Dimension with multiple levels hierarchy
-
- Posts: 54
- Joined: Thu Oct 18, 2012 5:08 pm
- OLAP Product: Cognos TM1
- Version: 10.1.1
- Excel Version: 2010
- Location: Italy
Dimension with multiple levels hierarchy
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
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
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Dimension with multiple levels hierarchy
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
Alan Kirk has done some videos though I don't know if there is one that covers your area.
Cheers
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 54
- Joined: Thu Oct 18, 2012 5:08 pm
- OLAP Product: Cognos TM1
- Version: 10.1.1
- Excel Version: 2010
- Location: Italy
Re: Dimension with multiple levels hierarchy
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
In the 2nd image I set the name of my dimension
In the 3rd image I setup the hierarchy indicating the children elements
This same image shows the results of the dimension
As you can see, data are not in the desired hierarchy
What's wrong?
Thanks in advance for your help
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
In the 2nd image I set the name of my dimension
In the 3rd image I setup the hierarchy indicating the children elements
This same image shows the results of the dimension
As you can see, data are not in the desired hierarchy
What's wrong?
Thanks in advance for your help
-
- MVP
- Posts: 2832
- 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: Dimension with multiple levels hierarchy
What about the Consolidamento tab? Whats's the code on that one?
-
- Posts: 54
- Joined: Thu Oct 18, 2012 5:08 pm
- OLAP Product: Cognos TM1
- Version: 10.1.1
- Excel Version: 2010
- Location: Italy
-
- MVP
- Posts: 2832
- 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: Dimension with multiple levels hierarchy
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.
-
- Posts: 54
- Joined: Thu Oct 18, 2012 5:08 pm
- OLAP Product: Cognos TM1
- Version: 10.1.1
- Excel Version: 2010
- Location: Italy
Re: Dimension with multiple levels hierarchy
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
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
-
- Posts: 42
- Joined: Mon May 19, 2008 10:25 am
- OLAP Product: TM1, EV
- Version: 9.0 9.1 9.4 9.5 10.1 10.2
- Excel Version: 2003 2007 2010 2015
- Location: London, UK
Re: Dimension with multiple levels hierarchy
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
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
Anthony
That's no moon - that's a space station
That's no moon - that's a space station
-
- Posts: 54
- Joined: Thu Oct 18, 2012 5:08 pm
- OLAP Product: Cognos TM1
- Version: 10.1.1
- Excel Version: 2010
- Location: Italy
Re: Dimension with multiple levels hierarchy
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
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
-
- Posts: 54
- Joined: Thu Oct 18, 2012 5:08 pm
- OLAP Product: Cognos TM1
- Version: 10.1.1
- Excel Version: 2010
- Location: Italy
Re: Dimension with multiple levels hierarchy
now it works (see the attached picture), but why dates are also outside the consolidation?
-
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: Dimension with multiple levels hierarchy
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.
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.
-
- Posts: 54
- Joined: Thu Oct 18, 2012 5:08 pm
- OLAP Product: Cognos TM1
- Version: 10.1.1
- Excel Version: 2010
- Location: Italy
Re: Dimension with multiple levels hierarchy
Right!
Thank you
Thank you
-
- Posts: 1
- Joined: Fri Apr 25, 2014 3:39 am
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2010
Time dimension
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
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
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Dimension with multiple levels hierarchy
A TM1 dimension isn't like a relational database; each element needs to be unique:cbho123 wrote: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
e.g.
Code: Select all
2014
Q1 - 2014
Jan - 2014
01-01-2014
Declan Rodger