TM1 Forum

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!

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.

You want this in a single dimension?

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.

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:

This assumes that you'll never need to create a view for a single DeptFunction or SubDept code across all departments

please explain this.

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.

This assumes that you'll never need to create a view for a single DeptFunction or SubDept code across all departments

What Alan means is that if you need to create multiple hierarchies in the same dimension or you can't ask a question like
"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