This is the first post on how you can create/update TM1 models with no code using
TeamOne Google Sheets add-on.
Today we will review how you can define dimensions hierarchies and attributes (including localizations) using a spreadsheet. Let's get started!
In a new Google Sheets document I will open the
TeamOne sidebar from the extensions menu:
We will skip creating TM1 connection as it was covered in my
previous post, I will just remind you the connections defined in personal settings will be shared in all Google Sheets where you use TeamOne, so you don't need to define the same connection in each document.
To manage dimensions we use
::dimension definition block. The dimension name is specified in
column B and attributes are listed starting from
column C:
By default TeamOne will create a string attribute, you can specify the attribute type and locale using the next syntax:
AttributeName:AttributeType:Locale, for instance
Caption:A:enWe can define element hierarchies by specifying the parent elements in
Column B:. Let's also localize the dimension and elements (using "Caption" alias):
Alternatively space indentation can be used to define hierarchies. For instance, let's create
Fiscal Quarter hierarchy in
Period dimension:
You can set the element order using
-componentSort and
-elementSort options:
Now to create\update the dimension in TM1 based from what we defined above we click the
Run code button. We can check the results on the
Model tab:
TeamOne will also create the default and common subsets (ALL, LEAVES) automatically. You can easily control the subset definitions using
-subset.* options:
Finally let's create one additional subset containing the current month:
CONCLUSION: we used Google Sheets to
document define our
Period dimension, including element hierarchies, attributes, localizations and subsets. Using
TeamOne we can create and update the dimension in TM1 with
zero code.
P.S.: Using
TeamOne you can also create/update cubes and views, as well as manage users, groups and object security without a single line of code. As a "byproduct" you get a nice model documentation :)
Win-win!