TeamOne Explorer is your Planning Analytics for Google Sheets. You can easily create explorations (cube views) by selecting dimensions and elements in the sidebar and the data will be refreshed directly in the Google Sheets grid.
When recalculating a new Exploration for the first time, it will be printed starting from the cell A1. You can add extra columns and rows with custom content above or below the exploration rows. TeamOne will automatically detect the new exploration coordonates and update it accordingly.
You can open TeamOne Explorer from the sidebar using the Explorer button on first tab. When you create a new exploration it becomes linked to the current open sheet and gets the same name as the sheet. When you open the Explorer screen, it will load the current sheet exploration if it exists, otherwise you will be able to create a new exploration or select an existing one to open.
You can select system cubes by selecting Show system objects from the menu
When you click the Explorer button, it will load the current sheet exploration if it exists, otherwise you will be able to create a new exploration for the current sheet. To open a different exploration, you can either switch to the sheet you want to open the exploration for and click the Explorer button or you can do it using the Other explorations menu option. TeamOne will automatically switch to the corresponding sheet for you.
Reload active sheet exploration
If you navigate to a different sheet, simply click the exploration icon to reload its exploration
You select the cube name when creating a new exploration or you can change the existing exploration cube in the exploration settings, which you can open from the exploration menu or simply by clicking the cube name.
In both cases TeamOne will build a sample exploration for you by selecting the last 2 dimensions for your rows and columns and using $ALL MDX mod to dynamically select all elements for each dimension.
Include empty rows
By default TeamOne will suppress empty rows. Select Include empty rows to include elements with no amounts or text data.
Include empty columns
By default TeamOne will suppress empty columns. Select Include empty columns to include elements with no amounts or text data.
TeamOne can retrieve data with formatting defined in IBM PA or you can retrieve the unformatted values (recommended if you intend to update and save the data back to IBM PA).
By default TeamOne will group rows elements (print distinct row element names). Uncheck the Group rows option if you prefer to repeat all the row elements.
By default TeamOne will indent rows elements by the hiearchy level. Uncheck the Hierarchy indentation option if you prefer to print the rows elements without indentation.
By default TeamOne will autoresize columns to fit the content. Uncheck the Autofit columns option if you prefer to keep the current columns width.
Rows, columns and filters
Each exploration has 3 dimension areas: rows, columns and filters. You can manually select elements or use and existing subset or use a subset expression for row and column dimensions. You can select a single element for each filter dimension
You can move dimensions between areas (rows, columns and titles) or up and down within an area (to change the order of nesting) using the dimension context menu. Using the same menu you can quickly select ALL, ROOTS or LEAVES subsets:
Row and column dimensions
You should have at least 1 row and 1 column dimension.
Click a filter dimension to select an element. The subset screen will open and the element dropdown field will be activated automatically and you can start typing the element name or simply select it from the list. You can also select a different hierarchy, an existing subset and attribute or use an MDX to control the element list.
You can unselect the element by clicking the clear icon on the right
While typing the element name, hit ENTER to select the first highlighted element and close the subset screen.
If you do not select a filter element, TeamOne will use a default element for that dimension, which is usually the first top level consolidated element user has access to. Default element can also be set by TM1 administrator in }HierarchyProperties control cube. Keep in mind, if there is element security defined for a given dimension, users may see a different default element depending on their permissions.
Row and column elements
For row and column dimensions you can choose to display elements from an existing subset, use an MDX expression or pick the elements manually
Simply select a subset from the Subset list and TeamOne will fetch the elements automatically.
If subset has an alias, it will be selected automatically as well
If subset is dynamic, the MDX button color will change to "indigo" and you will be able to see the expression by hovering the mouse of the MDX button
Use the MDX button to edit the existing MDX expression or create a new one. In the MDX editor you type the expression manually or use the MDX mods buttons at the top to build an expression with no code.
For instance, the next expression will filter all the leaf level Organization dimension elements by the currency attribute value equal to CDN and will select the first 2 elements. You can also use the Convert MDX button to get the real MDX:
More over you can simply type or copy-paste the elements. Or even use wildcard patterns:
MDX expressions are dynamic, meaning the element list will be evaluated upon refreshing exploration data
Finally you can select elements manually:
Save selected elements as subset
Use Save as subset menu option to save subsets back to IBM Planning Analytics, so they can be available in Planning Analytics Workspace, Planning Analytics for Excel, TM1Web, TM1 Architect, etc.
Click the Refresh data button to refresh the exploration data on its sheet. This will also save all your exploration changes. Depending on the exploration data volume it may take time to fetch and print all the data. For large explorations TeamOne will split data requests into multiple packages, up to 16384 cells each. Each result will be printed on the sheet as soon as it is received.
Save exploration as cube view
Use Save as cube view exploration menu option to save cube views back to IBM Planning Analytics, so they can be available in Planning Analytics Workspace, Planning Analytics for Excel, TM1Web, TM1 Architect, etc.
You can add custom calculated columns after the last exploration column using ARRAYFORMULA(), as it can calculate multiple values based on the provided range of cells. More complex formulas can be constructed depending on the requirements. Keeping the ARRAYFORMULA in the very first exploration data row (the very first row under the frozen headers) will prevent it from being wiped when recalculating the exploration.