Saturday, October 07, 2006

Referencing Subsets in TM1 formulas...

One handy feature of TM1 is the ability to reference not only elements but also subset names in TM1 formulas.

For example, if we have the subset called "Fast Cars" (on a cars dimension), which contains the elements "Porsche" and "Ferrari," we can pass our DBRW formula the actual name of the subset (instead of the individual element names).

That is:
=DBRW("tm1server:Cars", "2007","Fast Cars", "New Zealand","Car Numbers")

Instead of:
=DBRW("tm1server:Cars", "2007","Porche", "New Zealand","Car Numbers")

This will give us a rollup of the 2007 Car numbers for both Porsche and Ferrari in New Zealand.

I have found this feature to be incredibly powerful, especially when filtering is applied to the subset based on an attribute value.

- Be weary of referencing subsets containing both consolidated elements and their children, as values will be counted multiple times
- Subsets cannot be referenced as elements in MDX queries