I am trying to build a dimension based on the sample data in the XLS file which is attached here.
The dimension hierarchy should roll-up like this: Dept -> SubDept -> DeptFunc
and the Description should be the Alias
The problem is that in order to define the hierarchy in the database in the source data there some of the fields blanks in it - which is causing problem to build hierarchies in TM1. I have tried to build this dimension correctly by using so many ways but I am not getting the desired results. Can someone please help me to rectify this issue?
A sample TI Process will be highly appreciated.
Thanks in advance!
Problem building Dimension (Hierarchies)
-
- Posts: 103
- Joined: Mon Sep 21, 2009 7:09 pm
- OLAP Product: Cognos Tm1
- Version: 10.2.2
- Excel Version: 2016
Problem building Dimension (Hierarchies)
- Attachments
-
- TempDim.xls
- Sample data file
- (17 KiB) Downloaded 227 times
- Alan Kirk
- Site Admin
- Posts: 6610
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Problem building Dimension (Hierarchies)
You want this in a single dimension?ExApplix wrote:I am trying to build a dimension based on the sample data in the XLS file which is attached here.
The dimension hierarchy should roll-up like this: Dept -> SubDept -> DeptFunc
and the Description should be the Alias
The problem is that in order to define the hierarchy in the database in the source data there some of the fields blanks in it - which is causing problem to build hierarchies in TM1. I have tried to build this dimension correctly by using so many ways but I am not getting the desired results. Can someone please help me to rectify this issue?
A sample TI Process will be highly appreciated.
As things stand, you've got a problem.
The blanks aren't an issue. You'll need to define an element like "Not Specified" (or perhaps "000") and have a simple If block write to that element if it encounters a blank. (If (Variable@=''); s_ElementName = '000'; Else; s_ElementName = Variable; EndIf; ... something along those lines.)
Your bigger problem is this; I assume that SubDept is to be a level 1 consolidation, and Dept is to be a level 2 consolidation, but you have N level elements (like 995) which are also used as consolidation names. You can't have two elements in the one dimension with the same name, even if one is a C and one is an N.
One alternative that you could perhaps consider is having the N level elements as the combination of the three fields (eg 160-995-996) and have the consolidations made up of sub-departments and departments, but from the look of your file you could well have overlapping Dept and SubDept element names as well.
Again that could be worked around by combining Dept and SubDept into a single name for the consolidations.
This assumes that you'll never need to create a view for a single DeptFunction or SubDept code across all departments. If you do, then you might want to consider renaming the Dept and SubDept elements as you write them to the dimension; for example using D160 instead of just 160 for the department, and SD995 instead of just 995 for the SubDept.
There are plenty of workarounds, but you're going to have to pick one to get around the "same name, different level" issue.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 103
- Joined: Mon Sep 21, 2009 7:09 pm
- OLAP Product: Cognos Tm1
- Version: 10.2.2
- Excel Version: 2016
Re: Problem building Dimension (Hierarchies)
I have recreated the dimension by concatenating the Column names e.g. for SubDept it is now Dept.SubDept and for DeptFunc it is now Dept.SubDept.DeptFunc
There are some DeptFunc codes which are found under multiple SubDept, and because of that the hierarchies/Alias are not built-up correctly. See the following table:
dept subdept deptfunc description
500 501 502 Telephone
600 601 502 Gas
Can you think of way to build this correctly?
What do you mean by:
There are some DeptFunc codes which are found under multiple SubDept, and because of that the hierarchies/Alias are not built-up correctly. See the following table:
dept subdept deptfunc description
500 501 502 Telephone
600 601 502 Gas
Can you think of way to build this correctly?
What do you mean by:
please explain this.This assumes that you'll never need to create a view for a single DeptFunction or SubDept code across all departments
- Steve Rowe
- Site Admin
- Posts: 2424
- 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: Problem building Dimension (Hierarchies)
ExApplix,
I think the problem you are having building this structure is that you are thinking of it as one dimension.
If you treat DeptFunc as another dimension then your problems will go away.
The other approach is to have two consolidations in the dept dimension. One by function and one by structure. I suspect though that a second dimension would give you a more user friendly cube.
With the information you have given there's no way to have a single hierarchy and get both the total of 502 and total of 601 correct at the same time.
"Give me the total for sub func 502 across All Depts"
This is becuase the Total 502 and Total All Depts is in the same dimension. So getting the right answer out relies on the Total 502 structure being mapped correctly. If you have a DeptFunction dimension then you can easily get the answer since you have you dept dimension on Total All Depts and your Dept Function on 502. The extra dimension actually makes the cube less complex since the complexity of the dimension strutures is lower.
HTH
I think the problem you are having building this structure is that you are thinking of it as one dimension.
If you treat DeptFunc as another dimension then your problems will go away.
The other approach is to have two consolidations in the dept dimension. One by function and one by structure. I suspect though that a second dimension would give you a more user friendly cube.
With the information you have given there's no way to have a single hierarchy and get both the total of 502 and total of 601 correct at the same time.
What Alan means is that if you need to create multiple hierarchies in the same dimension or you can't ask a question likeThis assumes that you'll never need to create a view for a single DeptFunction or SubDept code across all departments
"Give me the total for sub func 502 across All Depts"
This is becuase the Total 502 and Total All Depts is in the same dimension. So getting the right answer out relies on the Total 502 structure being mapped correctly. If you have a DeptFunction dimension then you can easily get the answer since you have you dept dimension on Total All Depts and your Dept Function on 502. The extra dimension actually makes the cube less complex since the complexity of the dimension strutures is lower.
HTH
Technical Director
www.infocat.co.uk
www.infocat.co.uk