Time dimension, alternate drill-down path

Post Reply
masteringDWH
Posts: 1
Joined: Fri Jun 09, 2017 1:02 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Time dimension, alternate drill-down path

Post by masteringDWH » Mon Jun 12, 2017 7:51 am

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:
Image

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

So long...

Wim Gielis
MVP
Posts: 2200
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Time dimension, alternate drill-down path

Post by Wim Gielis » Mon Jun 12, 2017 8:12 am

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: Time dimension, alternate drill-down path

Post by st2000 » Mon Jun 12, 2017 8:24 am

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
TM1_DIM_DATE_ALT_NAV_PATH_Pic1.png (27.51 KiB) Viewed 2106 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
TM1_DIM_DATE_ALT_NAV_PATH_Pic2.png (56.63 KiB) Viewed 2106 times
Now you define the parent-child-relationships on sheet Consolidations:
TM1_DIM_DATE_ALT_NAV_PATH_Pic3.png
TM1_DIM_DATE_ALT_NAV_PATH_Pic3.png (53.94 KiB) Viewed 2106 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!
Last edited by st2000 on Mon Jun 12, 2017 8:26 am, edited 1 time in total.
-----------------------------------
Best regards,
Stefan

st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: Time dimension, alternate drill-down path

Post by st2000 » Mon Jun 12, 2017 8:25 am

Here the result in the Subseteditor:
TM1_DIM_DATE_ALT_NAV_PATH_Pic4.png
TM1_DIM_DATE_ALT_NAV_PATH_Pic4.png (14.95 KiB) Viewed 2105 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
TM1_DIM_DATE_ALT_NAV_PATH_Pic5.png (51.69 KiB) Viewed 2090 times
-----------------------------------
Best regards,
Stefan

tomok
MVP
Posts: 2617
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Time dimension, alternate drill-down path

Post by tomok » Mon Jun 12, 2017 11:56 am

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

BariAbdul
Regular Participant
Posts: 420
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: TM1,Cognos
Version: 9.5.2
Excel Version: 2007

Re: Time dimension, alternate drill-down path

Post by BariAbdul » Tue Jun 13, 2017 2:48 am

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
"You Never Fail Until You Stop Trying......"

User avatar
Michel Zijlema
Site Admin
Posts: 705
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Time dimension, alternate drill-down path

Post by Michel Zijlema » Fri Jul 07, 2017 1:03 pm

masteringDWH wrote: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

User avatar
Steve Rowe
Site Admin
Posts: 1956
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Time dimension, alternate drill-down path

Post by Steve Rowe » Fri Jul 07, 2017 3:08 pm

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.

Post Reply