TM1 Forum

hi Guys

i am new to TM1 and am struggling to do the following task:

i have an existing cube with various dimentions, including clients and departments. when we run cognos on this cube to do reporting, it firsts runs through all the data, and then suppresses the zeros. this causes the cognos server to crash.

what i would like to do is create a new cube from this cube that excludes the zero values, and one of the dimensions. i have set perametres for a specific month and year, and this will be run each month. i am trying to make clients a child of departments but for only the clients with values linked to that department.
i used if(measures <>0), DIMENSIONELEMENTCOMPONENTADD('dep-client',Departments,Client,1.000000);

this still allocates all clients under each department. could someone advise what the correct way to do it would be?

dumbomichael wrote:what i would like to do is create a new cube from this cube that excludes the zero values

That isn't the way TM1 works. For most cubes, where you are reporting data, you are always going to have a lot of zero values. Some cubes which have only a few dimensions - two, or perhaps three - are sometimes completely full of data. However, this happens rarely for reporting and happens mainly for configuration cubes etc. Having a lot of zeros isn't a problem in TM1, in fact, it is one of its strengths that it copes well with this.

dumbomichael wrote:i am trying to make clients a child of departments but for only the clients with values linked to that department.
i used if(measures <>0), DIMENSIONELEMENTCOMPONENTADD('dep-client',Departments,Client,1.000000);

this still allocates all clients under each department. could someone advise what the correct way to do it would be?

If the client X buys product Y from department Z in January, but not in February... then the client will get rolled up to the department but you will still see a zero when you report February - that's what I mean about there always being zero values.

dumbomichael wrote:i am new to TM1 and am struggling

Your attempting to re-design the cube because of a crash but I would suggest that you haven't proved that the cube sparsity or clients dimension is really the underlying issue (at least, not in your post). Sounds like you need to get somebody in to help you assess if the problem is the Cognos reporting tool, or the fundamentals of the cube design.

That isn't the way TM1 works. For most cubes, where you are reporting data, you are always going to have a lot of zero values. Some cubes which have only a few dimensions - two, or perhaps three - are sometimes completely full of data. However, this happens rarely for reporting and happens mainly for configuration cubes etc. Having a lot of zeros isn't a problem in TM1, in fact, it is one of its strengths that it copes well with this.

its my understanding that the problem we are having is that when we try to run cognos bi through tm1, cognos bi goes through all the possible combinations first, then only zero's out values. by doing this, there is too much data and the cognos server crashes.

we run this specific report once a month, thats why i thought a process to recreate a cube for cognos would be perfect without the zero values.

To deal with the zero suppress issue, you need to use Cognos 10.1 with the Dynamic Query option enabled.
But I do also agree that you may well have issues with your TM1 design.