Planning Analytics

Despite now we have an application for almost anything, spreadsheets are being used by every company for different purposes: to organize everything, to keep track of anything, to analyze data, to store and share data, to easily collaborate on live documents, to automate, etc. It is just so familiar, it is just so natural and comfortable to work with data using spreadsheets.

Even though we migrate corporate budgeting to IBM Planning Analytics / TM1, we never get rid of spreadsheets. Users still use spreadsheets to prepare and upload their data to TM1, they export data from TM1 and use spreadsheets to re-arrange, massage and format it. Every TM1 developer uses spreadsheets to design, document or manage TM1 models. Many TM1 developers became true VBA ninjas.

In this article I would like to tell you about TeamOne, a Google Sheets add-on for IBM Planning Analytics which was designed to:

• import/export data from/to TM1
• manage users and permissions
• manage TM1 model objects
• search TM1 models and logs
• document TM1 models
• automate tasks

TM1 usage and license compliance, performance testing and automated data validation are coming in the future releases.

It takes a few seconds to add TeamOne to Google Sheets directly from Google Workspace Marketplace. Since it is a cloud add-on you never need to worry about maintaining or updating it.
You don't need to buy a Google Workspace™ subscription. If you have a paid plan, you will be able to install TeamOne for all your domain users, otherwise you can TeamOne perfectly with a free Google account.

Once you install the add-on, you can open TeamOne sidebar from the Extensions menu:

* secret - is your encoded credentials, use Extensions > TeamOne > Tools > Secret maker to generate one.

You can click the Save button or Move to TEAMONE sheet. The second option will add the entered connection to TEAMONE sheet and you can actually use that sheet to configure other connections and automations, which will appear in the sidebar:

By the way you can save the secret in Configuration> My settings, that way it will not be visible to other document users.

Now you can test drive the TeamOne add-on and let's start with =TEAMONE() function.

The function expects 4 arguments:

• Cube name
• Row elements (range)
• Column elements (range)
• Title elements (range)

We tried to make it as simple as possible:

• in most cases you will need only one formula as it can work for multiple rows/columns/titles
• you don't need to specify the dimension names, the add-on will detect them from the entered elements
• you don't need to specify all the dimensions / elements
• the dimension and element order do not matter
• you can use element principal names or aliases
• remember that TM1 is space and case insensitive so both "Actual" and "AC tuaL" will work :)
• you can use Google Sheets formulas to calculate element names
• you don't need to repeat the element names for multiple rows (notice "Phones" and "Tablets" on the screenshot above)
• you can use alternative hierarchies
• you can omit rows, columns or titles range argument (in fact only one element is required to get the total cube value)

Wait a second, you don't even have to type the formula from scratch. Using a cube or view context menu (on the model tab), TeamOne will do all the heavy lifting and generate an example which you can edit further:

To refresh\recalculate the function results, just click the TEAMONE recalculate from the sidebar functions tab

Let's re-arrange some elements, add a little bit of formatting and we got a pretty Revenue by product color report based on 24Retail TM1 sample database:

Anytime we can click the TEAMONE recalculate to refresh the data from TM1. By default TEAMONE function will fetch data from the currently selected server. We can add another TM1 connection and use the same report to see data from another environment

Or using EnvironmentName:CubeName syntax for the first function argument, you can even get and compare the data from multiple TM1 environments on the same sheet:

That's it for today. In the next articles we will explore more TeamOne features, like how to:

• use TeamOne tasks to import Google Sheets data to TM1 or create automations that can be executed by business users in a click, for instance, you can run a process and calculate pYear parameter using sheets formulas =YEAR(TODAY())
• synchronize TM1 users and groups from a sheet, see active sessions and even kill users threads
• define and create cubes, views, dimensions, hierarchies, subsets, elements, attributes using a spreadsheet
• manage objects security using a spreadsheet
• search TM1 models, see TM1 server message logs
• compare TM1 models, for instance you can see which processes are different between TM1 Server1 and TM1 Server2. Moreover TeamOne can find and show you each different peace of code. Finally you can deploy objects from one environment to another one

#PlanningAnalyticswithWatson