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.

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.

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:

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.

See ::tm1process example

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

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:

See ::tm1data example

::tm1import ​

::tm1import task is used to import data from a sheet into a TM1 cube.

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

1. The column order should match the cube dimensions: Column 1 = Dimension 1, Column 2 = Dimension 2, ... Column N = Measure dimension, Column N+1 = Value
2. The last column header is Value

Option 2: a separate column is used for each measure dimension element

1. The column order should match the cube dimensions: Column 1 = Dimension 1, Column 2 = Dimension 2, ... Column N = Measure dimension
2. 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

See ::tm1import example

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

See ::tm1fileoutput example

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

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:

See ::tm1datacompare example