}ElementAttributes_Dept N:numeric attribute not working

Post Reply
User avatar
yyi
Community Contributor
Posts: 121
Joined: Thu Aug 28, 2008 4:42 am
Location: Sydney, Australia

}ElementAttributes_Dept N:numeric attribute not working

Post by yyi »

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!
PA 2.0.62.6, win10, excel 365 x86
Gregor Koch
MVP
Posts: 263
Joined: Fri Jun 27, 2008 12:15 am
OLAP Product: Cognos TM1, CX
Version: 9.0 and up
Excel Version: 2007 and up

Re: }ElementAttributes_Dept N:numeric attribute not working

Post by Gregor Koch »

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.
lotsaram
MVP
Posts: 3573
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: }ElementAttributes_Dept N:numeric attribute not working

Post by lotsaram »

I agree with Gregor, as far as I can recall numeric rules have never worked in attribute cubes ever.
MSidat
Community Contributor
Posts: 110
Joined: Thu Aug 26, 2010 7:41 am
OLAP Product: TM1, PA
Version: PAL 2.0.8
Excel Version: 2016
Location: North West England

Re: }ElementAttributes_Dept N:numeric attribute not working

Post by MSidat »

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.
Always Open to Opportunities
Duncan P
MVP
Posts: 600
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: }ElementAttributes_Dept N:numeric attribute not working

Post by Duncan P »

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));
The dimension structure is
Dimension structure
Dimension structure
attrule_dimstruct.png (5.08 KiB) Viewed 6319 times
and the resulting attribute values are
Calculated attribute values
Calculated attribute values
attrule_attrs.png (9.38 KiB) Viewed 6319 times
.
lotsaram
MVP
Posts: 3573
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: }ElementAttributes_Dept N:numeric attribute not working

Post by lotsaram »

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.
Duncan P
MVP
Posts: 600
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: }ElementAttributes_Dept N:numeric attribute not working

Post by Duncan P »

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