Time dimension, alternate drill-down path

[masteringDWH]

Hello,

actually we're building a time dimension in TM1 and ran into a question how to have an alternate drill-down path in the dimension.
The dimension looks like:

Year
- 2014
- 2015
- .....
- .....

• Quarter
  - 1. quarter
  - 2. quarter
  - ....

• • Month
    - .....

Now we want to include an alternate drill path for rolling months which looks like:
Rolling months

• 18 months

• • Month

• 24 months

• • Month

At the end we would have:
Year
- 2014
- 2015
- .....
- .....

• Quarter
  - 1. quarter
  - 2. quarter
  - ....

• • Month
    - .....

Rolling months

• 18 months

• • Month

• 24 months

• • Month

But at the moment we can just define one path in the HierarchyPropertys which causes that TM1 puts the rolling month levels under year, quarter, month. I know you can do such thing with the cognos transformer which could look like:


Anyone an idea how to achieve this without adding a second dimension for the rolling months?

So long...

[Wim Gielis]

Hello,

What exactly are you having issues with ?
Can you post your actual TI code ?
With the correct usage of DimensionElementInsert and DimensionElementComponentAdd you should get quite far...
Also, use While... End loops to populate the rolling windows.
A generic process or loop at the beginning of the Prolog tab could destroy all relationships in the dimension,
after that you recreate the dimension structures again in Prolog or Metadata tab (depending on the source of the process), but probably in the Prolog tab.

[st2000]

You need to prepare a data source for this having a PK for each granular item and for each level an identifier also as a label value (in my case it's an import via ODBC/SQL):

TM1_DIM_DATE_ALT_NAV_PATH_Pic1.png (27.51 KiB) Viewed 5471 times

Then you define this PK as an element and all other identifiers as Consolidation (my pic stems from a german installation, but it's named similar I think; Anderes means other):

TM1_DIM_DATE_ALT_NAV_PATH_Pic2.png (56.63 KiB) Viewed 5471 times

Now you define the parent-child-relationships on sheet Consolidations:

TM1_DIM_DATE_ALT_NAV_PATH_Pic3.png (53.94 KiB) Viewed 5471 times

There is no drag-n-drop-area per hierarchy like Transformer offers one. The relationships you define make up the hierarchies you get.
Then you also define Attributes and can generate the dimension. You receive not one ALL element, but as much as you created hierarchies. This means you need to be aware of this when aggregating data: This can be done only along of one of your hierarchies!

[st2000]

Here the result in the Subseteditor:

TM1_DIM_DATE_ALT_NAV_PATH_Pic4.png (14.95 KiB) Viewed 5470 times

It seems like I could add only max. 3 pictures per post...

Note, that I prefixed the year level members (CY for calendar year, CW for calendar week, so furtherly the derived variable is used). When I left the years as they were (e.g. 2016) I got odd results. It seemed that TM1 was confused which year belongs to which hierarchy due to the occurance below of both. Prefixing them avoided this and everything worked. I can't preempt that I made a mistake somewhere else, but if you see an odd distribution of members which are used to build more than only one hierarchy, this could be a solution.
The ALL members I created artifically (string formula), as these were not included in the ODBC source:

TM1_DIM_DATE_ALT_NAV_PATH_Pic5.png (51.69 KiB) Viewed 5455 times

[tomok]

Seeing as how a time dimension is usually something you only update once a year I would do this with an XDI worksheet. This will give you the ultimate flexibility to build as many hierarchies as you want and have them appear in any order you want. It's stupid easy.

[BariAbdul]

Also,Please go through Wim article on relative time dimension,It might give you some ideas:
http://www.wimgielis.com/tm1_relativeti ... ons_EN.htm Thanks

[Michel Zijlema]

actually we're building a time dimension in TM1 and ran into a question how to have an alternate drill-down path in the dimension.

[...]

But at the moment we can just define one path in the HierarchyPropertys which causes that TM1 puts the rolling month levels under year, quarter, month.

Anyone an idea how to achieve this without adding a second dimension for the rolling months?

This is one example of the limited integration between TM1 and Cognos BI/BA. In the }HierarchyProperties cube you can only define one set of level names for the levels in the dimension. So in dimensions with multiple hierarchies (that do not have the same level contents/depth) you're out of luck.
The }Hierarchies dimension in the cube does not seem to have any function at all.

Michel

[Steve Rowe]

Just to side track this post, the new hierarchies functionality works very well with the time dimension.

I'm not sure how well this interacts with CA / BI but I would hope very well since hierarchies represents a move to MDX standards by TM1, something that CA / BI already respects pretty well.