Rule area definitions made clear

Example files with this article:
  • Dimensions of a rule area definition
  • Introduction

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

    My own tool

    I created a 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.:

    Year2008, 2009, 2010
    Company ID105

    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 did not want to write such code. For sure, regular expression syntax should be used for this.


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links