TM1 Forum

Hi,
I have a TM1 system which now imports the data from ERP.
Some of the key dimensions get updated as it gets updated in ERP.
Now one of my key dimensions is splilt into 3 parts in the ERP.
For E.g:
Old ERP Dim1=123456789

New ERP Dim1=123
Dim2=456
DIm3=789.
What should be best design approach in my TM1 model. All this dimensions will have hierarchy.

Thanks in advance,
Abhi

Hi Abhi,

Not really sure what your question is, it's probably right to put the three dimensions into the cube in place of the one you had before but it's not possible for me to give you an exact design.

Irrespective of the where the underlying data comes from and how it is arranged your cube design should be driven by what the users want to do with the data, rather than data driving the design. The data sets the boundries of what the design can be, the users requirements should define the detail of the design.

Cheers,
Steve

Thanks Steve
But one thing is if I go with three independent dimensions and all three dimensions are inter dependent which means security will be a problem.
Cell level Security is not the option as it will have huge impact on Performance.
Business needs flexibility for entering the data and they should have right combinations available.

I think I understand what you are asking and I run across this all lthe time. In your example you have a nine character code where the first three characters represent one thing, let's say company, the next three characters mean another thing, we'll say cost center, and the last three mean account. You have two choices, combine the three fields from ERP and create a single dimension with multiple hierarchies to represent how the three sets of codes roll up or create a separate dimension for each where the first dimension must have all the unique combinations of the first three characters, the second all the unqiue combinations of the second three characters and so on.

The first option is probably preferable but it may not be as user friendly because of the multiple hierarchies and you also won't be able to look at a cross section of the three in an active form report. The second has challenges because when you look at all the intersections of the three independent dimensions you will likely end up with combinatons that aren't actually a valid nine character code. One option here is to create a "Validity" cube where you take the three dimensions and put a value of 1 in the intersection when it represents a valid nine character code. You can use this cube in input templates to filter the dimensional lists or to create context-sensitive lists. For example, once user selects a valid company, have the cost center list be filtered to only those valid for that company and after company chosen only show those accounts valid for that company and cost center. This can be a bit of work but it can be extremely user friendly when you can use aliases for the three and users don't have to memorize the chart of nine character codes since your system guides them through the selection.

Thanks Tom....
But can you guide little on how do we create a filter based out of Validity Cube.
Are you intending to say using rule.
Or via Security.
If we are doing it wid Rule will not there be performance issue...
Pls guide....

tomok wrote:One option here is to create a "Validity" cube where you take the three dimensions and put a value of 1 in the intersection when it represents a valid nine character code. You can use this cube in input templates to filter the dimensional lists or to create context-sensitive lists. For example, once user selects a valid company, have the cost center list be filtered to only those valid for that company and after company chosen only show those accounts valid for that company and cost center. This can be a bit of work but it can be extremely user friendly when you can use aliases for the three and users don't have to memorize the chart of nine character codes since your system guides them through the selection.

Hi Tomok,

"Context Sensitive Lists". This is always I wished TM1 to support, as we had it in Cognos Planning. But I thought it is not an available feature in TM1. Could you please explain how you think you can achieve this in TM1? Obviously cell security does not provide this feature. Can this be achieved by putting MDX expressions in dimension subsets that uses context elements?

aagrawal12 wrote:Cell level Security is not the option as it will have huge impact on Performance.

Hi Aagrawal,

Do not think of cell security always kills the performance. We have seen it performing well in very big cubes (>50GB cubes), when it is designed properly. If you need to use cell security, make it rule driven out of the 3 dimensional lookup cube (similar to the validity cube that Tomok has suggested).

Regards,

mce wrote:"Context Sensitive Lists". This is always I wished TM1 to support, as we had it in Cognos Planning. But I thought it is not an available feature in TM1. Could you please explain how you think you can achieve this in TM1? Obviously cell security does not provide this feature. Can this be achieved by putting MDX expressions in dimension subsets that uses context elements?

Like most things in TM1, it isn't a feature that is supported out of the box, you have to build it. What most people don't understand about TM1 is it is not an application, like Cognos Planning, it is a multi-dimensional development tool. IBM is making strides moving it towards the "application" realm but there is still a ways to go. I just hope they leave the flexibility alone in cases where you don't want to eat their dog food. Time will tell.

Context sensitive lists require two cubes 1) a "validity" type cube, where you can store a flag that indicates which combinations of elements are considered "valid" and 2) a client choices cube, using the }Clients dimension, so you can have the validity checked by user. You then create two dynamic MDX subsets in each of the dimensions you want to be "context sensitive"’ one that includes the client choice (from the Client_Choices cube) and another that filters values from the Validity cube.

The “Client_Choice” MDX subset would look like this (example is for Dim_1 but you will need one for each dimension that is to be context-sensitive):

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Dim_1] )}, [Client_Choices].(STRTOMEMBER("[}Clients].["+USERNAME+"]"),[Choice].[Dim_1]))}

The context sensitive leaf list would look like this (this is for valid Dim_1 elements, you would follow the same logic for each of the other dimensions):

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Dim_1] )}, 0)}, [Validity].(TM1MEMBER([Dim_2].[Client_Choice].Item(0),0),TM1MEMBER([Dim_3].[Client_Choice].Item(0),0)) > 0 )}

Using this logic the list of elements from Dim_1, Dim_2 and Dim_3 can be only those items that are considered “valid” based on the validity lookup cube and each client’s choice in the other dimensions.

Let me add that the filtering doesn't necessarily have to be done via a "Validity" cube. It could be based on whether or not there are budget or actual or whatever balances in a particular cube, as long as the dimesions being filtered exist in the balances cube. I know the "No Dynamic Subsets" crowd will be all over this but I find it a very valuable tool and have never had any peformance issues using it. YMMV.