Subsets that add up
- Oct. 23, 2015
Introduction
This will be a rather short article. I wanted to pass you the curiosity of using a subset name in a DBRW() formula. Say, you have a subset containing 10 elements. The DBRW() formula will then give you the result as if the subset name was a consolidated member, containing those 10 elements as children with weight equal to 1.
Please have a look at the screenshot below. You will see a simple slice in Excel with at the bottom the DBRW formula pointing to a subset name. On the right, a TM1 cubeview to present and reconcile the data.
It does not matter if the subset is private or dynamic. Nor does it matter if the subset is static or dynamic. The subset can contain leaf-level elements in the dimension or consolidations. If you use the same element more than once in the subset, it will only be added up once. The weight of the respective children in their parent(s) is irrelevant; as said, the subset uses weight 1 to add up values of subset members.
Hmmm, wait… Did I say that subsets add up? That’s not always correct. If we have a rule at C-level… then the DBRW() formula will happily apply that rule to the ‘subset element/consolidation’!
Mind you, I do NOT recommend using this in a production model! What I describe above is undocumented and unsupported by IBM, AFAIK.
What I describe above is in fact linked to the functionality in any TM1 dimension, to insert a subset in another subset. For example, check out the below screenshots of the Subset Editor in which I create 2 static subsets and then I insert them in another subset.
Note that the dimension elements and consolidations do not change. TM1 acts as if the subset name that we insert is a new consolidated element. Hence, a TM1 rule that calculates C-level, or element security at the C-level, will both be enabled. You can also insert a subset that is dynamic and MDX-based.
FYI, a CellGetN() formula also seems to work in a TI process. The subset has to be a public subset, unless it's a private subset of the user executing the TI process. Another FYI, in rules this functionality does not seem to work (a DB function).
