Rule area definitions made clear
Posted: 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).
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).