Subsets In Rules

Post Reply
Posts: 64
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Subsets In Rules

Post by bgregs » Thu Sep 24, 2020 7:01 pm

I recently was playing around with some of the new (and by "new" I mean "new to me") TM1 rule functions, and stumbled across something that may be interesting to others. Thank you PavoGa for your help in testing and validating this thing!

For a while, we've known about the ability to use subsets in place of elements in a CellGetS/CellGetN functions, which makes sense since you can actually copy the subset name as text and paste it as a consolidation in the Architect dimension editor - nothing new here. While this works great for some cases in TIs, the DB calls in rules never quite had this functionality. For example:

Code: Select all

CellGetN('cubeName', vCycle, 'SubsetNameAsElement', vMeasure)   <-- This will work
DB('cubeName', !Cycle, 'SubsetNameAsElement', !Measure)         <-- This will fail
With the addition of CellValueS and CellValueN however, we now have the ability to grab data out of cubes (right hand side of rule) through subset addressing. For example:

Code: Select all

CellValueN('cubeName', 'Cycle':!Cycle, 'Dimension':'SubsetNameAsElement', 'Measure':!Measure) <-- This will work!!!
Please note, the Cycle and Measure references could actually be chopped off for a cleaner statement. It's entirely possible this is nothing new to anyone, but I found it interesting and thought it was worth documenting on the forums.

NOTE: This WILL work for dynamic subsets, but the performance could take a huge hit. Use at your own risk - this may be a perfect solution for some easy dynamic or static subsets, but I would guess that this would have a huge negative impact on performance for some more complicated dynamic subsets.

User avatar
Community Contributor
Posts: 443
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.09
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Subsets In Rules

Post by PavoGa » Thu Sep 24, 2020 7:34 pm

And we have some more coming don't we? :D
Cleveland, TN

Posts: 3433
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Subsets In Rules

Post by lotsaram » Fri Sep 25, 2020 7:36 am

I'm really surprised that this works given IBM has implemented something completely different in PAW with MDX aggregates replacing subsets as the mechanism for user defined consolidations.

Might be worth a query to IBM whether this will stay as a feature or is accidental and might disapear.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Post Reply