TM1 Forum

I might just be missing something really simple, but a rule in an element attribute cube on a numeric element
compiles but is showing nothing, cells are not grey and trace calculation doesn't work !?
And all the string attribute rules are fine:-

eg: string attribute rule - okay
['dept01', 's_attr01'] = S:'hello there';

eg: numeric attribute rule - no good
['dept01', 'n_attr01'] = N:99;



does anyone have a quick answer?

Thanks!

Hi
My quick answer is not to write N: rules on Attribute cubes and create/use a look up cube that holds the information you need for your rule (on the look up cube). Maybe somebody else has another one.
Not that I have check all the different versions of TM1 on whether this ever worked...it doesn't in 9.5.2.

The longer answer would be... you'll notice that no matter what type of Attribute - Numeric, Text, Alias - you create, the elements in the }elementattributes dimension are all of type N (numeric) on the surface and hence no S: rule should possibly apply, which is the opposite of what you observe. Let alone that if the same restrictions would apply on attribute cubes you shouldn't be able to store text. You'll also notice that no consolidation in an attribute cube consolidates any numbers - numeric attribute or not. I guess this has something to do with how attribute cubes are treated internally and... well, if you need numeric calculations you are better off with a lookup cube even if a ['dept01', 'n_attr01'] = S:'99'; would do it for you.

I agree with Gregor, as far as I can recall numeric rules have never worked in attribute cubes ever.

Just for future info, I remembered reading about this somewhere and managed to find it in the TM1 Reference guide:

Numerical Attributes
Numerical attributes are considered special elements and rules for these elements are not allowed. To use a rule to populate the cells, create a text attribute. The string values can then be converted to numeric data as needed using the NUMBR function.

This documentation is not entirely accurate.

An attribute cube, no matter what the type of the attributes, behaves as if all elements are of type text. This is what allows numeric attributes to be entered for consolidated items. It is perfectly possible to write a rule for a numeric attribute but you have to treat it as a string, and the result of the rule statement needs to be either the empty string, or the text representation of a valid number.

To illustrate I added three numeric attributes (ChildCount, ParentCount and Difference) and the following rule to the attribute cube for a simple dimension s. The dimension structure is and the resulting attribute values are .

Duncan P wrote:This documentation is not entirely accurate.

An attribute cube, no matter what the type of the attributes, behaves as if all elements are of type text. This is what allows numeric attributes to be entered for consolidated items. It is perfectly possible to write a rule for a numeric attribute but you have to treat it as a string, and the result of the rule statement needs to be either the empty string, or the text representation of a valid number.

To illustrate I added three numeric attributes (ChildCount, ParentCount and Difference) and the following rule to the attribute cube for a simple dimension s.

Code: Select all

['ChildCount'] = S: TRIM(STR(ELCOMPN('s',!s),16,0));
['ParentCount'] = S: TRIM(STR(ELPARN('s',!s),16,0));
['Difference'] = S: TRIM(STR(NUMBR(DB('}ElementAttributes_s', !s, 'ChildCount'))-NUMBR(DB('}ElementAttributes_s', !s, 'ParentCount')),16,0));

Duncan, that's a fantastic piece of TM1 rules gymnastics! Where there is a will there is usually a way.

However the workaround of
i) type conversion from string to numeric
ii) perform calculation
iii) convert result back to string
... is certainly ungainly, especially considering that string values need to be DB() referenced.

IMO I think this illustrates that for most if not all of us, especially those more at the starting out end of the TM1 spectrum, that the best approach is as Gregor said to enter numeric values and perform numeric calculations elsewhere in a similarly dimensioned 2D cube and then do a straight look-up transfer in the element attributes cube.

It's a fair cop guv. You got me bang to rights!