Rule area definitions made clear

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Rule area definitions made clear

Post by Wim Gielis »

Hello all

From time to time, one encounters badly written TM1 rules. So bad that you have troubles to know to which dimension an element in the area definition belongs. You have trouble finding out to which elements the rule applies. For instance, ID numbers that could match in a number of dimensions, …

I have been working on a little tool in Excel VBA to do the following.

First connect to a TM1 server through Perspectives.
Type in the server name, pick a cube name from a dropdown, and paste an area definition of a rule residing in that cube into a textbox. E.g. paste or type:

['Actuals',{'2008','2009','2010'},'Company ID':'105']

Then, the tool will list the dimensions of the cube, together with the elements. E.g.:

Dimensions: Elements
--------------------------
Version: Actuals
Year: 2008, 2009, 2010
Company ID: 105
Months: Unspecified
Measures: Unspecified

If an alias is used in the area definition, the code will add a comment to the cell specifying name of the alias and principal element name.

The code can handle the syntax above, hence also curly brackets and 'dimension name':'element name' syntax.

The only assumption in this story is that the rule should be correct regarding syntax. Otherwise, the VBA code could expect any string and should first validate syntax correctness. I didn't want to write that code ;-)

Feel free to comment or make suggestions on the tool or the VBA code. Perhaps I could be looking at automatically creating a view starting from the area definition (we know all the elements so that should be feasible).
Attachments
Find the dimensions of a rule area definition.xls
(82.5 KiB) Downloaded 719 times
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply