For IBM Planning Analytics users, the ability to interact with data intuitively and efficiently is critical for making informed decisions. Enter TeamOne, the Google Sheets add-on that seamlessly bridges the gap between IBM Planning Analytics and one of the world’s most popular spreadsheet tools. By leveraging the flexibility and collaboration features of Google Sheets, TeamOne allows users to access, manage, and analyze their IBM Planning Analytics data with ease. Whether it's creating dynamic reports, automating workflows, data uploads or performing real-time collaboration, TeamOne unlocks a new level of productivity and accessibility. In this post, we’ll dive into the key functionalities and features that make TeamOne an indispensable tool for modern finance and business teams.

Installation

Installing TeamOne couldn’t be easier. There’s no need to install anything on your computer or make changes to your IBM Planning Analytics server. Simply enable the TeamOne add-on for your Google account, and you’re ready to go. For teams, Google admins can pre-install the add-on to multiple users simultaneously or control who can use the add-on. Once enabled, TeamOne will automatically appear in the Extensions menu of Google Sheets, accessible across all your devices, whether you’re using Windows, macOS, or Linux.

TeamOne Sidebar

The TeamOne Sidebar is the primary interface for working with IBM Planning Analytics data directly in Google Sheets. It allows users to switch between multiple connections, recalculate and upload data, expand and collapse hierarchies, and perform drill-through, browse the IBM Planning Analytics (TM1) models, run automated tasks. Admin users have access to advanced functionality, including model comparisons, user and security management, server logs, and the ability to run and manage processes and chores.

IBM Planning Analytics data

With TeamOne, accessing IBM Planning Analytics data is seamless and highly adaptable to suit various user needs.

Explorations

You can use Explorations to quickly build dynamic reports, either from scratch or by leveraging existing cube views. Through the intuitive TeamOne Sidebar, you can select dimension elements or write MDX queries to pinpoint the exact data you need in Google Sheets. You can save subsets and explorations back to your IBM Planning Analytics (TM1) server as public to private cube views.

Each Exploration is automatically linked to a corresponding tab in your Google Sheets document and the data can be refreshed at any time.

Using the TeamOne sidebar, you can easily expand and collapse elements, perform drill throughs and upload the exploration data back to IBM Planning Analytics.

For users who prefer formulas, TeamOne introduces powerful custom functions such as TEAMONE(), MDX(), ATTR(), SUBSET(), USERNAME() and UPDATED().

TEAMONE() function

Whether creating reports, building dashboards, or performing ad-hoc analysis, the TEAMONE() function provides an easy formula-driven way to integrate live IBM Planning Analytics data into your spreadsheets. 

The function has the following arguments:

• Cube name - the name of the cube from which data is to be retrieved. You
• Row elements - the cell range representing your row elements  
• Column elements - the cell range representing your column elements  
• Filter elements - the cell range representing your filter elements  
• Options - allows for customization of the function’s behavior through various flags:
  • "A": Autofit columns.
  • "F": Fetch formatted values (e.g., currency or percentages).
  • "U": Enable data updates to the server.
  • "S": Use static values (allows for adding custom calculations and improves performances).
  • "R": Reverse sign.
  • "Q": Quiet mode to disable notes for omitted dimensions. 
  • "Z": Display zero values instead of blanks. 

• Solve order - Specifies the formula evaluation order, useful when you have multiple formulas that need to be recalculated in a specific order.

Key Features:

• Flexibility with dimensions: There's no need to follow the cube's dimension order, and you can omit dimensions that are not relevant to your query.
• Alias and element names: You can use element names or aliases, and the function is case- and space-insensitive, making it forgiving and user-friendly.
• Single formula for entire reports: A single TEAMONE() formula can be used to populate an entire report or section, reducing complexity and improving maintainability.
• Data READ and WRITE: Supports both reading data from IBM Planning Analytics and writing updates back to the server.
• Dynamic combination: You can combine TEAMONE() with other Google Sheets functions to create complex, dynamic reports and calculations.
• Data formatting and customization: you can fetch the formatted values or apply the formatting in Google Sheets.
• You can add spacers or calculated rows and columns in between the data:

MDX() function

The MDX() function allows you to build dynamic reports (with dynamically generated rows and columns) using an existing cube view, MDX expression or element expressions for each dimension.

In the following example, we see the data from the Revenue by channel view in the Revenue cube:

We can also provide the MDX expression directly:

But for non-technical users, it is much easier to use the TeamOne MDX mods - simplified expressions. Users can easily select subsets, use wildcard expressions, select ancestor and descendant elements, filter and sort elements using almost plain English words (please see below MDX mods examples). The function syntax is similar to the TEAMONE() function:

• Cube name - the name of the cube from which data is to be retrieved. You
• Row dimension expressions - the cell range representing your row dimension expressions 
• Column elements - the cell range representing your column dimension expressions
• Filter elements - the cell range representing your filter elements  
• Options - allows for customization of the function’s behavior through various flags:
  • "A": Autofit columns.
  • "F": Fetch formatted values (e.g., currency or percentages).
  • "S": Use static values (allows for adding custom calculations and improves performances).
  • "Z": Display zero values instead of blanks. 

• Solve order - Specifies the formula evaluation order, useful when you have multiple formulas that need to be recalculated in a specific order.

Let's have a closer look at some of the MDX mods used above:

Channel^$leaves  expression tells TeamOne to select all leaf-level elements from the Channel dimension

Product^\*1000\  expression tells TeamOne to select all Productd dimension elements that end with "1000"

Year^$roots>>first=3>>sortasc  expression tells TeamOne to select all root level elements, pick the first 3 and sort them in ascending order

Version^Actual;Budget  expression tells TeamOne to select the Actual and Budget elements from the Version dimension.

~Caption_Base  expression tells TeamOne to use the "Caption_Base" alias.

There are so many other MDX mods you can use: ancestors, children, descendants, distinct, level, %attribute%=%value% ...

You can see all of them here: https://succeedium.com/teamone/doc/functions.html#mdx-mods

ATTR() function

The ATTR() function retrieves the specified attribute values for one or more dimension elements. It expects the following arguments:

• Dimension name - the dimension name, you can also use dimName:hierName syntax to specify an alternative hierarchy
• Element range - the cells representing your elements
• Attribute name - attribute name, i.e. 'Caption'
• Options - flag, "S" - static values.
• Solve order - specifies the formula evaluation order. If omitted, the default value of 0 will be used.

As demonstrated in the example above, a single ATTR() function can retrieve attribute values for multiple dimension elements and skip empty rows. This functionality allows for the inclusion of spaces in additional calculations within your reports.

SUBSET() function

The SUBSET() function retrieves dimension elements using the provided set expression. It expects the following arguments:

• dimName - the dimension name, you can also use dimName:hierName syntax to specify an alternative hierarchy
• setExpression - a valid MDX expression, $SubsetName or MDX mods
• attrName - attribute name, i.e. 'Caption'
• options - flag, "S" - static values.
• solveOrder - specifies the formula evaluation order. If omitted, the default value of 0 will be used.

Now let's look at a few SUBSET() examples:

1. To select elements from an existing subset, you can use the "$" MDX mod, simply specify the subset name after the dollar sign, for instance, the following formula will display the elements from the Default subset in the Month dimension:

2. Now, let's select all leaf-level elements, excluding duplicates, and sort them in ascending order:

3. You can also list the elements and even use wildcard expressions:

4. Finally, you can combine element list, wildcard expressions, real TM1 MDX and MDX mods:

Explorations with calculated columns

You can use the TEAMONE()  and ATTR() functions to calculate Exploration columns. Since both functions can work with a range of cells, you can add them to the first Exploration data row and use the exploration rows as arguments:

TIP: when selecting the exploration rows, select one extra empty row - this will allow the TEAMONE()  and ATTR() functions to automatically adjust when recalculating the exploration or expanding and collapsing the exploration rows.

Updating data

Both TEAMONE() function and explorations support data writeback using the "U" function flag and the "Allow data updates" exploration setting:

Action buttons and running TurboIntegrator processes

TeamOne replaces traditional action buttons with a Task tab, enabling users to create automation tasks such as:

• Run TurboIntegrator processes
• Import and export IBM Planning Analytics data
• Recalculate sheets
• Compare data
• and more

You can define tasks using the TeamOne Configuration:

or directly in Google Sheets:

You can use Google Sheets functions to make TeamOne tasks dynamic, for example, you can calculate the TI process parameter names and values. You can also add tooltips for your tasks using the "-info" option:

TM1 model explorer

Use the 3rd TeamOne Sidebar tab to view TM1 cubes, views, dimensions, hierarchies and subsets and automatically build formulas and explorations:

Google Slides and Google Docs integration

Users can easily incorporate data into their presentations or documents by copying and pasting tables directly from Google Sheets, without the need for additional tools or complex steps. By leveraging the native linking feature of Google Workspace, this integration ensures that Google Slides and Docs automatically detect updates made in the source Google Sheets. A simple refresh updates the embedded data to reflect the latest changes. Additionally, users can refresh multiple linked tables and charts simultaneously through the Tools > Linked objects menu, making it quick and efficient to keep all embedded content up to date:

Stay tuned for an upcoming IBM webinar showcasing TeamOne and discover how it can transform your workflow.

Have questions or want to learn more? Connect with us anytime at https://succeedium.com/teamone/contact.