DMR - Time Hierarchy!!!!

[giovanni.lucarelli]

hello to everybody!

first of all sorry for my bad english...

I am working with Cognos 8.3 and I modelled a DMR; in my reporting activity I need often to recover informations in the same report such as billing this year, billing previuos year, stock YtD, etc... avoiding multy queries to improve performance

Well, Cognos8 specialist maked me sure that Dimensional Functions (such us nextMember, previuosMember, periodsToDate, etc...) work good as well in DMR that in Cubes, but I don't find still the way for them!!!!!!

Really, my time hierarchy is totally un-sorted in the tree-hierarchy!

I wanna give u more details, and i hope that samebody can help me...really! I am going to be mad.

my data sorce is oracle and my table TIME_TIME is this (same rows)

id_day     cd_language date_day     month_year   month_code quarter_year quarter_code year
(number)  (varchar)     (date)         (varchar)      (varchar)     (varchar)     (varchar)       (varchar)
1            EN              01-01-2006  2006-January 200601       2006-01       200601         2006
2            EN              02-01-2006  2006-January 200601       2006-01       200601         2006
..            ..                .............       ............................................................................
1            IT               01-01-2006  2006-Gennaio 200601       2006-01       200601         2006
2            IT               02-01-2006  2006-Gennaio 200601       2006-01       200601        2006
..            ..                .............       ............................................................................

then:

1) I forced sorting in Oracle (in fact my table is correctly sorted for cd_language and id_day 
2) I imported this table in FM
3) I defined a filter for multilanguage (and it works good)
4) I defined determinants in this way:

key                attributes   
year                                group by
quarter_year   quarter_code   group by
month_year   month_code   group by
date_day   (all)   unique

5) Notice that when I run query example in FM this is still order!!!!!!! (I don't know if it's important)

6) I defined my regular dimension:

YEAR      year                             business_key, member_caption   
QUARTER quarter_year                business_key, member_caption   quarter_code attribute
MONTH    month_year                business_key, member_caption   month_code  attribute
DAY        to_char(date_day….)   business_key, member_caption

and i flag unique level for all (as is correct)


....well!!!... the level DAY is still sorted but THE OTHER LEVEL ARE NOT!!!!!! and this problem persists and maintains in report studio:

this is the result for example in the hierarchy:

2006
      200601
               200601
                        2006-01-01
                        2006-01-02
                        2006-01-03
                        ...............
               200603
                        2006-03-01
                        2006-03-02
                        2006-03-03
                        ...............
               200602
                        2006-02-01
                        2006-02-02
                        2006-02-03
                        ...............

so when i run dimensional function (for example PreviousMember(200602) it gives me 200603!!!!!)

can samebody help me???????

I hope my explaination is quite good  :-[

if u need more details to have a cleaner situation don't hesitate to contact me!!!!

please!
     

[blom0344]

Perhaps this will shine some light on your issue:

(known issues for 8.2 from the Knowledge Base):

Cannot Specify the Order of Members When Defining a Level 

In the current release, you cannot specify the order of the members when defining a level for a regular dimension on a relational data source. This limitation results in the following functions returning members who are dependent on the sort order in which the data was retrieved:

    *

      firstFromSet
    *

      remainderSet
    *

      head
    *

      item
    *

      lag
    *

      lead
    *

      nextMember
    *

      prevMember
    *

      lastPeriods
    *

      closingPeriod
    *

      openingPeriod
    *

      parallelPeriod
    *

      periodsToDate
    *

      subset
    *

      cousin
    *

      firstChild
    *

      firstSibling
    *

      lastChild
    *

      lastSibling

We recommend that you do not use these functions when using dimensionally modeled relational data sources. To ensure that report authors do not use these functions, use Quality of Service to exclude them from the functions list.

491332

You mention that data is correctly sorted in the Oracle table. That is a known misconception. Databases do not care much about sorted storage. The query that extracts the data is responsible for the correct order by..

[giovanni.lucarelli]

THANKS VERY MUCH... :)

but I called Cognos Support in London and they said me that this functions work in Cognos 8.3!!!!!

what do u think?


thanks again...

[blom0344]

We're currently looking into DMR for our production 8.1 clients and sure hope that 8.3 offers better functionality..

Did you try and enforce proper sorting through the relational source (like adding a query subject that involves the correct order by , instead of just reading the table in a random way?

[giovanni.lucarelli]

if I import my table from the data-source by this query: select * from cognos_connection.table_name order by column_name asc nothing's happened....in query explenation the "order by" disappear!

really i don't know... I tried with a view too....nothing

[blom0344]

Change the SQL settings under options to native and add the order by. You will need to explicitly name the correct database/schema for this to work as it bypasses Cognos. The order by is used as FM uses a named table expression : 

Code Select Expand


with
New_Table as
    (tt....{  Select   * from db.dbo.table order by 1})



[giovanni.lucarelli]

thanks blom0344,

I never tried this....but how can I change SQL native add the order by?

Excuse me ....my knowladge about Cognos is not so big

[blom0344]

If you edit the query subject, then there is a tab test. In the right lower corner is an options link where you can tweak some settings (among others the SQL generated)
Obviously, this has only some use with SQL objects, not with model query subjects..

[giovanni.lucarelli]

blom0344,
I did: nothing:

select id_day, id_month, id_quarter, id_year
from table_time
order by 1

is sorted but for example:

select id_month from (
select id_day, id_month, id_quarter, id_year
from table_time
order by 1
)
group by id_month

is not....


what can I try still?!??!?!!?? ???

[rockytopmark]

Gio -

8.4 has added capability to set an item to use for the order for the members of the Levels of a Hierarchy.  In 8.3 and back, you will have to do the ordering at the report level.