Dimension with multiple levels hierarchy

[Rosanero4Ever]

Hi all,
I'm a newbie about TM1 and I'm reading many tutorial (above all IBM documents and the book "TM1 Cookbook") but I have some problems to develope a dimension having a hierarchy with multiple levels.
In my environment, I have a table in Sql Server as reguard as time with the following columns:
- year
- quarter
- month
- day

Dates in this table are between 01-01-2000 and 31-12-2020

I use Turbo Integrator process to create in TM1 a time dimension with the above hierarchy but the result isn't that I hoped :
process groups quarters so, e.g. in the 2010 1st quarter, I see all the dates are in all 1st quarters (all years)
Does anybody can advice me some videos or tutorials that can guide me simply?
I'm reading many documents that build the hierarchy in excel but they don't help me.
Thanks in advance for your time

[Steve Rowe]

Your best bet is to post the code in the TI that you written if you want explicit help.

Alan Kirk has done some videos though I don't know if there is one that covers your area.
Cheers

[Rosanero4Ever]

Hi,

I attached 3 jpegs showing my process configuration.
In the 1st image I set Data as Element and others Year, Month, Quarter (corresponding to "anno", "mesestring" and "trimestre") as consolidation

1.jpg (44.47 KiB) Viewed 11762 times

In the 2nd image I set the name of my dimension

2.jpg (30.48 KiB) Viewed 11762 times

In the 3rd image I setup the hierarchy indicating the children elements
This same image shows the results of the dimension

5.jpg (92.73 KiB) Viewed 11762 times

As you can see, data are not in the desired hierarchy
What's wrong?
Thanks in advance for your help

[tomok]

What about the Consolidamento tab? Whats's the code on that one?

[Rosanero4Ever]

sorry....

6.jpg (31.86 KiB) Viewed 11757 times

[tomok]

Your dimension is being built exactly as you have specified in the Consolidations tab. I can't help you correcting it because I can't read or write (Italian????) so I don't know what the correct designation of the variables should be.

[Rosanero4Ever]

Hi tomok,
thanks for your time.
Yes, my TM1 is setup in italian language.
Unfornutately I haven't english language in my TM1 installation.
But, because I need your help, what information do you need to debug my process and help me to solve this issue?
Regards

[AnthonyT]

Hi Roasanero

Not sure if this is the problem, as my grasp of Italian doesn't much go past the names of football teams!

It looks like the consolidations are not unique so get rolled up into multiple places!

For example, October is not unique to 2012 - there is one in 2011, 2010 etc. The same for weeks as well - week 01 is not unique to January 2012 as there is one in January 2011..... you get the point!

By creating new variables you could join together the year and month to get 2012_10 or 2012_October, and the same for the weeks - 2012_October_Week01. Weeks then role up into Months, which role up into Years.

Hope this helps

[Rosanero4Ever]

Hi Anthony,

I was suspecting just that you wrote.
But this isn't a pretty solution (even if I think is THE solution), aren't you agree?
I used Transformer and the dimensional functionalities of Framework Manager and there all is more simple and hierachies are created in a few minutes.
So, I'll adopt this workaround...
Thank you so much

[Rosanero4Ever]

now it works (see the attached picture), but why dates are also outside the consolidation?

7.jpg (101.29 KiB) Viewed 11741 times

[Duncan P]

This could be just a display order issue. Press the "Hierarchy Sort" button in the subset editor to see if the problem goes away.

If it does and you want to fix it permanently you need to press the "Ordine componenti" button on the "Dimensioni" tab and select the Italian equivalent of "Automatic" and "Hierarchy" in the dialog.

Element order dialog

element_order.png (4.6 KiB) Viewed 11740 times

[Rosanero4Ever]

Right!
Thank you

[cbho123]

Hi,

I have data like this
Year-Quarter-Month-date
2013 -1-Jan-2014-01-01
2013-1-Jan-2014-01-02
.
.
2013-1-Feb-2014-02-01
.
.
.
2013-2-April-2014-04-01
....
2014 -1-Jan-2014-01-01
2014-1-Jan-2014-01-02
.
.
2014-1-Feb-2014-02-01
.
.
2014-2-April-2014-04-01

When I created Time Dimention in TM1, using hierarchy - Year->Quarter->Month->Date
I am facing problem. Under January month all years January dates are coming. This is happening for all the months.

I know that I need to use aliases. but not getting correct idea. Please advise

[declanr]

Hi,

I have data like this
Year-Quarter-Month-date
2013 -1-Jan-2014-01-01
2013-1-Jan-2014-01-02
.
.
2013-1-Feb-2014-02-01
.
.
.
2013-2-April-2014-04-01
....
2014 -1-Jan-2014-01-01
2014-1-Jan-2014-01-02
.
.
2014-1-Feb-2014-02-01
.
.
2014-2-April-2014-04-01

When I created Time Dimention in TM1, using hierarchy - Year->Quarter->Month->Date
I am facing problem. Under January month all years January dates are coming. This is happening for all the months.

I know that I need to use aliases. but not getting correct idea. Please advise

A TM1 dimension isn't like a relational database; each element needs to be unique:

e.g.

Code: Select all

2014
     Q1 - 2014
          Jan - 2014
                01-01-2014

Just putting an element called "Jan" under "2014" and "2015" doesn't create 2 different intersections called "Jan"; it just shows the same single intersection in 2 different places.