Flag for rule based elements

[RCO]

Hi all,

I'm unsure on whether the is TI function or any other way to identify/flag which elements are rule based.
I know I could create an attribute an flag those elements, however in a relatively large measure dimension, Is there a way to automate this/populate the attribute via a TI?

Cheers

[lotsaram]

You have 2 problems here
1/ TM1 calculation rules are true multi-dimensional expressions. Although an area statement and rule can be defined in terms of only one dimension it doesn't have to be and very often isn't. Therefore whether a rule applies to a cell or not can be the result of the cell coordinates on multiple dimensions so an attribute or element property to define some notion of data entry vs. calculation isn't workable.
2/ TM1 dimensions (including measures) can be reused in multiple cubes, so a measure that is data entry in one cube could very well be rule calculated in another (or in another slice of the same cube from point 1 above.)

Assuming that you are only interested in leaf cells which would make sense since by definition all consolidated intersections are calculations anyway AND providing that no cube or element locks are in place then the TI function CellIsUpdateable should do what you need. If will return true for a simple numeric cell that accepts data entry and false for a rule derived cell.

(Locks aren't commonly used in TM1 models, I think mainly because most people aren't aware they exist but a lock can restrict access also for users who otherwise have full admin rights, including TI processes in which case the CellIsUpdateable function could return false on a numeric leaf cell. But there is a LockOverride function which can be used so that a TI process ignores locks if there are any.)

Oh, and I might also add, what's your requirement or use case anyway for wanting to flag or display to users what is rule calculated?

[rmackenzie]

I know I could create an attribute an flag those elements

Per what Lotsa said, the bottom line is that the rule applies to cells not elements - a key difference to get your head around.

I'm unsure on whether the is TI function or any other way to identify/flag which elements are rule based.

It would be great to have CellIsRuleCalculated(sCubeName, V1, V2...) instead of CellIsUpdateable(sCubeName, V1, V2...) but it isn't there. If you're OK with just concentrating on whether the leaf cells have rules then you can use code like this:

Code: Select all

# check if cell is all leaf level
nContinue = 1;
nCellIsLeaf = 1;
nDimensionCounter = 1;
WHILE ( nContinue =1 );
  sDimension = TABDIM ( DataSourceNameForServer, nDimensionCounter );
  IF ( sDimension @<> '' );
    sElement = Expand ( '%V' | NumberToString ( nDimensionCounter ) | '%' );
    sType = DTYPE ( sDimension, sElement );
    IF ( sType @<> 'N' );
      nCellIsLeaf = 0;
      nContinue = 0;
    ENDIF;
  ELSE;
    nContinue = 0;
  ENDIF;
  nDimensionCounter = nDimensionCounter + 1;
END;

# if cell is all leaf and not updateable then probably rule applies - note issues with LOCK etc
IF ( nCellIsLeaf = 1 & CellIsUpdateable ( DataSourceNameForServer, V1, V2, V3, V4, V5, V6, V7 ) = 0 );
  nLeafHasRule = 1;
  # debug
  AsciiOutput ( 'out.txt', V1, V2, V3, V4, V5, V6, V7, vValue );
ELSE;
  nLeafHasRule = 0;
ENDIF;

Note this relies on all variables being named V1, V2, V3 etc

I'd echo Lotsa's request to know why you want to do this - out of curiousity?

[RCO]

Thank you lotsaram/rmackenzie for your kind replies.

I am fully aware that rules apply to cells and not to elements not to mention what a rule that applies in one cube is not necessary applicable in another cube despite using the same dimension.

I have to admit it is a rather unusual requirement, however there is a large measure dimension with over 300 elements providing different sorts of statistics and KPIs. Some of these elements are calculated by rules and some are actual data. Users want to know what the model calculates and what is actually loaded in the cube for clarity purposes. They are also considering having this flag shown in a couple of reports.

I actually didn't know about CellIsUpdateable, so thank you very much for that I believe I can work something out with this, once again thank you both

[rmackenzie]

Another approach some people take is to create consolidations in the measure dimension to semantically categorise the measures as either input measures, or calculations, or whatever you choose. You might want to use zero weighting if you do this otherwise somebody might mistake an meaningless aggregation for a real result.

[lotsaram]

Another approach some people take is to create consolidations in the measure dimension to semantically categorise the measures as either input measures, or calculations, or whatever you choose. You might want to use zero weighting if you do this otherwise somebody might mistake an meaningless aggregation for a real result.

I ofter use "technical consolidations" in measure (and other) dimensions for the purposes of feeding or to identify whether a rule should act on children of a consolidation or not. I also sometimes use technical consolidations for the purpose "cataloguing" measures eg. "All unit metrics", "All value metrics", "All ratio metrics", "All percentage metrics", "All YoY growth metrics", .... I have never tried to automate these rollups though just set by hand. I usually have a C: 0 rule at the top of the rule file for such groupings to limit any potential confusion in case such a grouping should get displayed on a report.

[RCO]

Interesting to know that I wasn't the only one creating C: 0 rule at the top of a rule file for similar groupings lotsaram has mentioned.

Thank you both for your ideas & comments.