Planning Analytics

 View Only

Create TM1 dimensions directly in Google Sheets with no code

By Vlad Didenko posted Tue April 26, 2022 03:13 PM

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:en

We 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 :)




Mon June 27, 2022 01:07 AM

Thanks @Errol Brandt, using TeamOne, besides captions, you can define and localize other attributes as well, using a separate column for each one. The cool thing about this approach is that you basically document your model and your co-workers can collaborate and help with translations. Everything is documented and stored online, you get Google Sheets file version history and the changes can be synced to any TM1 server in 1 click.

What were the challenges with Japanese localization?

Fri June 24, 2022 08:53 PM

That looks really interesting, and it's great to see the use of captions.

I looked at using captions to help make PA more accessible to our Japanese parent company but found it really hard to implement.