String Value at Consolidation level

[manoj928]

Hello,

We have a dimension as:

A
-- B
-----C

C is at granular level of dimension. Now what i want for a measure with String value "ABC" at level C, at B and A level as well it must show "ABC".
Need your help. We are using TM1 9.5.2 version.

Thanks,

[Duncan P]

First a few questions:

1. What do you want it to show if more than one child has a string value? Is it the first (in ELCOMP order) to have a non-empty value? Or is it some conglomeration of the strings?

2. Will you need this functionality on multiple dimensions? If so does it need to be symmetric with respect to dimensions i.e. aggregate up dimA and aggregate up dimB independently and then show a joint aggregations for all possible combinations of the two?

3. Finally is it reasonable to order the elements in the dimensions so that all children of the same parent are in a block with consecutive DIMIXs?

[manoj928]

1. What do you want it to show if more than one child has a string value? Is it the first (in ELCOMP order) to have a non-empty value? Or is it some conglomeration of the strings?
-- Every child will have same value for that measure.
2. Will you need this functionality on multiple dimensions? If so does it need to be symmetric with respect to dimensions i.e. aggregate up dimA and aggregate up dimB independently and then show a joint aggregations for all possible combinations of the two?
-- Yes, I need this functionality for multiple dimensions.

3. Finally is it reasonable to order the elements in the dimensions so that all children of the same parent are in a block with consecutive DIMIXs?
-- yes..

Thanks,

[Duncan P]

If, for any parent, every child will have the same value then by extension all leaves across the entire dimension (and all others for which this string aggregation is required) must have the same string value. Are you sure that this is the case?

[manoj928]

Thats right.

for all dimensions and at all aggregation, leaves values will be same, hence we want for all consolidation as well, it must be same value.

Thanks,

[Duncan P]

If it is all the same for all leaves then is it being set with a rule? If so then why is the rule not applying to the consolidated items?

[lotsaram]

Are there multiple hierarchies or just one? Should there be multiple parents then which one should have precedence?

If (and only if) there is a single hierarchy that all other elements ultimately roll up to then the following rule should work to just set the value once for the top element and it will be inherited down the hierarchy to all descendants.

Code: Select all

['String Value'] = S: 
IF( ElPar('Dim', !Dim, 1) @= '',
  STET,
  DB('Cube', ElPar('Dim', !Dim, 1), 'String Value')
);

(Example in the rule is for 2D cube but obviously adapt as necessary)

[manoj928]

There would be multiple hierarchy but i am sure, for all , it will be common value at each leaf level. I tried to explain as below:

Actual Expected
Dim 1 Dim2 String Measure String Measure
Loan 1 A(Level 1) ABC
B(Level 2) ABC
C (Level 3) ABC ABC
D (Level 3) ABC ABC
E (Level 3) ABC ABC

Business wants the expected column in TM1.

Any help will be great for me.

Thanks,

[manoj928]

Sorry if previous one is not so clear:

Actual Expected
Dim1 Dim2 String Measure String Mesure
Loan1 A (Level 1) ABC
B(Level2) ABC
C (Level 3) ABC ABC
D (Level 3) ABC ABC
E (Level 3) ABC ABC

Thanks,

[lotsaram]

Seems pretty simple then. All you need is an appropriately dimensioned cube to hold the String input value and a rule to bring this value into the other cube against all members of the dimension. Note that the dimension for which all values should be the same would not be part of the input cube for the common string value.

[manoj928]

So you mean to say, we should have a another Cube w/o a dimension DIM2 as explained below and a rule in main cube to fetch same measure field so again as far i know, it will be at leaf level only.

How we will put same value at consolidation level, i mean to say at Level 2 and 1.

Thanks,

[lotsaram]

So you mean to say, we should have a another Cube w/o a dimension DIM2 as explained below and a rule in main cube to fetch same measure field so again as far i know, it will be at leaf level only.

How we will put same value at consolidation level, i mean to say at Level 2 and 1.

While you don't need a separate cube, from a design perspective it is a good option and the "cleanest" solution so yes to the first question.
2nd question: You are saying that all cells need to have the same value for this string measure regardless of leaf or level 1, 2 in this "Dim2". So you get the "consolidated" string values exactly the same way as populating the leaf cells, the rule will be the same since a reference to "Dim2" will be omitted from the LHS of the rule (meaning it will apply to all possible members of the dimension.)

Think about it.

[manoj928]

Thanks a lot to all as i successfully implemented the logic and it is working fine.