Tasks
Tasks allow you to create one-click automations for different IBM PA routines: running TI processes, import, export or compare IBM PA data etc. (more automations are coming in the future). You can create tasks using configuration menu or TEAMONE sheet
For each task you define a ::tasktype in column A and task name in column B.
TIP
Start creating tasks using the configuration menu, then click the MOVE TO TEAMONE SHEET button and finish customizing the task on TEAMONE sheet
You can use Sheets formulas to make tasks dynamic. A common example would be to use =YEAR(TODAY()) formula to get the current calendar year and use it as a parameter for TM1 process task. Or you can simply reference other cells to build dynamic strings
To execute a task you simply click it in TeamOne sidebar: 
::tm1env
You will need to configure at least one ::tm1env (IBM PA environment connection). Depending on who should be able to use the connection you can save it in:
- Configuration > My Settings if you want to make the connection private to you and shared across all Soogle Sheets documents where you use TeamOne add-on
- Configuration > Document Settings if you want to make the connection shared with all the users of a given Google Sheets document. You can also use TeamOne security to specify who can use the connection (without being able to see the connection details) or who should have admin rights to manage the connections
- Configuration > Domain Settings if you want to make the connection available for all corporate users across all Google Sheets documents. You must be a TeamOne Domain Administrator in order to see and manage the Domain settings.
- You can leverage Merged Settings to keep your credentials private and make other connection details visible to other users.
| Option / parameter | Description |
|---|---|
| -host | Specify your TM1 server host FQDN. This parameter is mandatory |
| -port | Should be equal to the HTTPPortNumber in tm1s.cfg file. This parameter is mandatory for IBM PA Local connections |
| -namespace | Should be "LDAP" for IBM PA Cloud connections. If using IBM PA Local with IntegratedSecurityMode 4 or 5 specify, the namespace configured in Cognos Analytics (usually "LDAP") |
| -username | Leave empty if using Single-Sign-On (SSO). Otherwise use your IBM PA Local account or a non-interactive account for IBM Planning Analytics Cloud |
| -password | Leave empty if using Single-Sign-On (SSO). Otherwise use your IBM PA Local account password or a non-interactive account password for IBM Planning Analytics Cloud |
| -secret | This is a base64 encoded username and password. Use Extensions > TeamOne > Tools > Secret maker menu to create your own one. This parameter is mandatory |
| -securitySheet | Specify a sheet name where you want to manage your users and their security groups. If presented, Sync security from sheet option will become available in TeamOne Sidebar menu. See more info on security sheet |
| -startRow | Indicates the first data row in securitySheet. If omitted the default value 1 will be used |
| -startColumn | Indicates the first data column in securitySheet. If omitted the default value 1 will be used |
| -securityRecreate | Indicates to delete all non-admin users before synchronizing the users and groups from Google Sheets. The default value is False |
| -createGroups | If TRUE, TeamOne will create new security groups if they do not exist |
Adding ::tm1env in TeamOne Configuration:
The same ::tm1env definition on TEAMONE sheet:
::tm1process
::tm1process task is used to run TI processes. See the table below how to specify the process name and parameters:
| Option / parameter | Description |
|---|---|
| -name | Used to provide a TI process name. If omitted the task name will be used |
| -tm1env | Indicates to run the process always in the specified environment, should match one of the defined ::tm1env. If omitted the process will run in the selected environment in TeamOne sidebar |
| -info | A task hint, will be displayed when hovering a mouse cursor over info icon next to the task name |
| parameter 1-N | On each line enter a parameter name in column A and a parameter value in column B. For any omitted parameter its default process value will be used |
To execute a task you simply click it in TeamOne sidebar. When the process is completed you will see a message in the sidebar log panel confirming the execution status.
TIP
You can generate ::tm1process task from the sidebar process menu, which will add the process name and all the parameters automatically
::tm1data
::tm1data task allows to query TM1 data using an existing view, MDX or specified dimension elements\subset. The query result will be printed on the specified sheet.
| Option / parameter | Description |
|---|---|
| -tm1env | Indicates to extract data in the specified environment. If omitted the currently selected environment will be used |
| -info | A task hint, will be displayed when hovering a mouse cursor over info icon next to the task name |
| -sheet | Sheet name to output the data to |
| -activate | If provided, it will switch to the specified sheet automatically once the data is printed |
| -cube | Cube name, should be used with -view or dimension definitions (see below) |
| -view | An existing cube view to fetch the data from. -cube option must be also provided |
| -mdx | MDX expression to query TM1 data. MDX will be used only if -view option is not provided |
| Dimension 1-N | Enter a dimension name in column A, elements\subset\mdx in column B (see below for syntax). -cube option must be also provided. Dimensions/elements will be used only if -view and -mdx options are not provided |
In column B for each dimension (entered in Column A) you can define a subset or MDX expressions as:
- a comma delimited element list, i.e.: Actual,Budget, Forecast
- an existing subset name in a format $SubsetName
- $ALL, $LEAVES and $ROOTS system subsets. TeamOne will create a system subset if it does not exist using the next MDX expressions:
- ALL: {HIERARCHIZE({[dimensionName].members})}
- LEAVES: {TM1FILTERBYLEVEL( {[dimensionName].members}, 0)}
- ROOTS: {FILTER( {[dimensionName].members}, [dimensionName].CurrentMember.Parent.Name = "")}
- a valid MDX expression, i.e.: {[Calendar]}.[2022].Children}
- a pattern using "*" for any characters and "?" for any one character, i.e.: /*substring*/ or /2022 Q?/ You can also combine elements, patterns and MDX expressions into a comma delimited list, i.e.: Current period,Previous period,{[Calendar]}.[2022].Children,/2022 Q?/ If you leave Column B value empty, the default dimension member will be used
The data is printed on the specified -sheet: 
::tm1import
::tm1import task is used to import data from a sheet into a TM1 cube.
| Option / parameter | Description |
|---|---|
| -tm1env | Indicates the environment to use. If omitted the currently selected environment will be used |
| -info | A task hint, will be displayed when hovering a mouse cursor over info icon next to the task name |
| -cube | TM1 cube name to import the data into |
| -sheet | Sheet name containing the data |
| -startRow | Indicates the first data row. If omitted the default value 1 will be used |
| -startColumn | Indicates the first data column. If omitted the default value 1 will be used |
| -skipBlank | Indicates to skip empty cells |
| -checkIfUpdatable | Indicates to skip rule calculated cells |
There are several data requirements, depending on how your data is structured:
Option 1: each column represents a cube dimension, the last column contains values
- The column order should match the cube dimensions: Column 1 = Dimension 1, Column 2 = Dimension 2, ... Column N = Measure dimension, Column N+1 = Value
- The last column header is Value
Option 2: a separate column is used for each measure dimension element
- The column order should match the cube dimensions: Column 1 = Dimension 1, Column 2 = Dimension 2, ... Column N = Measure dimension
- A separate column should be used for each measure dimension element
For both options you can use both principal element names and aliases and the entered data should match the data types of the measure dimension elements TeamOne will automatically detect which option to use, based on the cube dimension count and the "Value" column header
TIP
Option 1 can be used directly with the data fetched by a ::tm1data task, meaning you can have a 2 way data transfer using ::tm1data and ::tm1import tasks.
::tm1fileoutput
::tm1fileoutput task allows to export sheet data into a field delimited file on TM1 server. It is very handy, when you have a TI process with a complex validation or transformation logics and you just want to use the sheets data export as the TI process data source.
| Option / parameter | Description |
|---|---|
| -tm1env | Indicates the environment to use. If omitted the currently selected environment will be used |
| -info | A task hint, will be displayed when hovering a mouse cursor over info icon next to the task name |
| -sheet | Sheet name containing the data |
| -startRow | Indicates the first data row. If omitted the default value 1 will be used |
| -startColumn | Indicates the first data column. If omitted the default value 1 will be used |
| -rows | Number of rows to export. If ommited or 0, all data rows will be used |
| -columns | Number of columns to export. If ommited or 0, all data columns will be used |
| -file | File path on TM1 server. You can specify a relative path to the TM1 data directory, for instance ..\FileName.txt will save FileName.txt one level folder up |
| -delimiter | file field delimiter, if omitted a tab character will be used |
::tm1datacompare
::tm1datacompare task allows comparing of an existing tm1data task query results (a TM1 view, MDX or specified dimension elements\subset) in the same or different tm1 environment.
| Option / parameter | Description |
|---|---|
| -tm1env | Indicates the environment to extract data from. If omitted the currently selected environment will be used |
| -tm1data | tm1data task name (should be defined separately). You should run that task to get the initial data extract |
| -activate | If provided, it will automatically switch to the sheet specified in the tm1data task once the comparison is completed |
First you should run the corresponding ::tm1data task to get the initial data results. ::tm1datacompare task will execute ::tm1data task again but this time it will compare the current and previous results and will show the difference in the last column:
Compare TM1 data before and after deployments
Run a ::tm1data task before you start deploying any changes. Run ::tm1datacompare task after the deployment is completed. Compare the difference on the sheet specified in -sheet option of the ::tm1data task.
Compare TM1 data between different TM1 models
Use -tm1env task option to specify your target environment.