Rule area definitions made clear

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
Posts: 2456
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium

Rule area definitions made clear

Post by Wim Gielis » Sat Mar 14, 2009 10:00 am

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).
Find the dimensions of a rule area definition.xls
(82.5 KiB) Downloaded 369 times
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014 ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

Post Reply