Content

Content tab displays sheets with TeamOne content, so you can easily navigate through TeamOne explorations and formulas. You can recalculate and perform actions related to the active sheet (expand/collapse exploration rows, submit exploration data to IBM PA, drill through to detailed data, increase/decrease indentation) and Recalculate all sheets with TeamOne content.

Recalculate

Recalculates all TEAMONE() formulas and exploration on the current sheet. The time required for the data to refresh depends on the complexity of your IBM Planning Analytics model, the number of formulas on the sheet, and the exploration data volume. It may take a few seconds or longer to complete depending on these factors.

Expand / Collapse

Expand or collapse the selected exploration cell. This is an equivalent to expanding or collapsing dimension element using dimension subset editor in Explorer. TeamOne will toggle the element state (a collapsed consolidation element will be expanded and an expanded consolidation element will be collapsed), save and refresh the current sheet exploration.

Upload data

After you update the exploration or TEAMONE() formula data in Google Sheets you can upload it back to IBM PA:

Drill through

If you have defined drill-through links in IBM Planning Analytics, you can perform drill-through from TeamOne Explorations. This allows you to view your detailed data from the associated cube view, database, or file. To do this, simply select an exploration cell in Google Sheets and click on the Drill-through option from the menu.

Increase indent

Being a really great tool, sheets are missing 2 simple functions to increase and decrease cell indentation. Increase indent adds 1 space (on the left) to the selected cells. This function can help you when defining a TM1 dimension hierarchy using space indentation

Decrease indent

Opposite to the function above, Decrease indent trims 1 space (from the left) of the selected cells.

Recalculate all

Use Recalculate all to recalculate all or multiple selected shees.

Add code

Though PAW provides a web interface to do TM1 modeling, it is still very tedious to develop and update your models, document your changes and deploy them to other environments. TeamOne allows to create/update tm1 cubes, views, dimensions, hierarchies, subsets, elements, attributes, manage users, groups, permissions, object security... without a single line of code. It is like you use Sheets to write a documentation and the documentation is used to update your model automatically. Moreover:

• you can collaborate with all your team members when you have new TM1 requirements or change requests
• a model draft gathered during reqruiements discussions can replace up to 95% of the development
• you can have maximum involvement of analysts and business users, for instance, you can ask somebody to translate element captions to a different language and then you will be only one click to get the new localized captions syncronized in TM1.
• your documentation is created before the development has even started, moreover, it gets updated as soon as you change anything
• you can prototype and implement real models in seconds

TIP

This functionality requires to have TM1 ADMIN group. It will be automatically hidden for non-admin users.

Add code will open an interface where you can add a basic template to:

• Create/update cube
• Create/update view
• Create/update dimension hierarchy
• Create/update subset
• Manage cube security
• Manage dimension security
• Manage element security
• Manage application security
• Manage process security
• Add prolog code
• Add epilog code

You fill out the basic template form and click ADD TO ACTIVE SHEET button, where you will be able add more fields and customize the code block. Once done, you can format and run code.

::cube

Use ::cube block to create and update cubes. You specify the cube name in the second column

Option / parameter	Description
-recreate	Include this option if you want to recreate cube (you don't need to specidy any value in column B). This option is mainly used in the dev environment when you need to change cube dimensions. IMPORTANT: recreating the cube will also delete all cube data.
-caption	Allows to specify a cube caption. You can localize the cube name by adding multiple caption options in a format -caption:locale, for instance -captions:en, -caption:fr, etc. The locale should exist in }Cultures dimension.
Dimension 1-N	Use a separate row to specify the needed cube dimension names in the order you want them to be in the cube. Remember you can have up to 256 dimensions in TM1 cube.

In the next example let's create XR cube that consists if 3 dimensions. We will also add Exchange Rates caption. Later we can add other localized captions:

See ::cube example

TIP

For existing cubes you can automatically generate a cube definition from the model tab cube menu

::view

Use ::view block to create and update a cube view. You specify the view name in the second column

Option / parameter	Description
-cube	cube name
-subset	default subset in a format $SubsetName, will be used for all the ommited dimensions. $ALL subset keyword will be used if this option is omitted
-alias	default subset alias, if exists the alias will be applied to each created subset. If subset already exists, its current alias will not be updated
-recreate	recreate default subset, it is used with the -subset option above. For instance, you specify -subset option as '$LEAVES'. Then it will recreate the LEAVES subset for each dimension you omitted in the current view definition
Dimension 1-N	enter a dimension name in column A, subset\expression in column B and subset options in column C. Use the next syntax to set a view row or column dimension: R:dimensionName, C:dimensionName. See below how to define elements\subset\mdx for each dimension subset in column B

In column B for each dimension you can define a subset or MDX expressions as:

• a semicolon 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].[hierarchyName].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?/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: Current period;Previous period;{[Calendar].[2022].Children};/2022 Q?/ If you leave Column B value empty, the -subset option will be used

In column C you can enter subset options in the next format: '-name:Total Company -alias:Caption_Base', which will name the subset as Total Company and will set the Caption_Base alias. See more subset options below

See ::view example

TIP

For existing cubes you can automatically generate a view definition from the model tab cube or view menu

::rule

Use ::rule block to create and update a cube rule

Option / parameter	Description
-cube	cube name
Rule line 1-N	use a as many lines as you need to define the calculation rules

::dimension

Use ::dimension block to create and update dimensions. You specify the dimension name in the second column. Starting from column 3 you can specify element attributes in a format: AttributeName:AttributeType:AttributeLocale, where AttributeType is one of the following:

• S (default) - string
• A (default) - alias
• N (default) - numeric

AttributeLocale is an element in }Cultures dimension.

You can ommit AttributeType and/or AttributeLocale, TeamOne will assume a string non-localized attribute, for example on the next screenshow TeamOne will create create Caption alias, MonthNumber numeric and MonthName string attributes:

Option / parameter	Description
-hierarchy	If this option is specified, it will create an alternative hierarchy, if omitted the default hiearchy will be created
-recreate	Include this option if you want to recreate the dimension elements (you don't need to specidy any value in column B). This option is mainly used in DEV environment when you need to delete the elements first before re-building the dimension hierarchy. IMPORTANT: all the data for the deleted elements will be lost
-componentSort	Set the component sorting. Values: ByInput (default), ByName
-componentOrder	Set the component sorting order. Values: Ascending (default), Descending
-elementSort	Set the element sorting. Values: ByHierarchy (default), ByInput, ByName, ByLevel
-elementOrder	Set the element sorting order. Values: Ascending (default), Descending
-caption	Allows to specify a dimension caption. You can localize the dimension name by adding multiple caption options in a format -caption:locale, for instance -captions:en, -caption:fr, etc. The locale should exist in }Cultures dimension.
-subset.alias	Specify the alias in Column B to be used for the Default and system subsets (ALL, LEAVES, ROOTS). No alias will be used if omitted
-subset.expandabove	Will expand above the Default and system subsets (ALL, LEAVES, ROOTS)
-subset.dynamic	Will make dynamic the Default and system subsets (ALL, LEAVES, ROOTS)
-subset.leaves	The value in Column B will be used as the LEAVES subset expression. $LEAVES subset keyword will be used if omitted.
-subset.all	The value in Column B will be used as the ALL subset expression. $ALL subset keyword will be used if omitted.
-subset.roots	Will create ROOTS subset. The value in Column B will be used as a subset expression. $ROOTS subset keyword will be used if omitted.
Element 1-N	Use a separate row to specify the dimension element names in the order you want them to be in the dimension. There are 2 ways to organize elements into a parent-child hierarchy: you can eiher use a space indentation in column A or you can specify the parent element in column B. You can specify the element type and weight after a colon, for example: ElementName:-1 will add ElementName child element with -1 weight; Comment:S will create a string element. If attributes are defined above, you can specify the element attribute values in the corresponding columns

For instance let's create Period dimension containing 2022 and 2023 months. Let's also add en and fr localized captions. Also let's create the default and system subset:

See ::dimension example

We can quickly add a Fiscal Quarter alternative hierarchy

See ::hierarchy example

::subset

Use ::subset block to create and update subsets. You specify the subset name in the second column

Option / parameter	Description
-dimension	Dimension to create a subset for
-hierarchy	Use this option to specify which hiearchy to create a subset in. If omitted the default hiearchy will be used
-alias	Used to specify a subset alias
-expandabove	Use this option to set the subset expand above flag. Use False value to disable it if needed
-dynamic	Include this option if you want to recreate a dynamic subset
Element 1-N	Use a separate row to specify the elements to include in the subset. See below how to define subset elements\mdx for each

In Column A on each row you can specify:

• an element name
• a semicolon delimited element list, i.e.: Actual;Budget;Forecast
• a valid MDX expression, i.e.: {[Calendar]}.[2022].Children}
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /2022 Q?/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: Current period;Previous period;{[Calendar].[2022].Children};/2022 Q?/

For instance let's create a few subsets for the Period dimension: (https://docs.google.com/spreadsheets/d/1wOI8onSk2GoOoVzSDh_EPbZj5pN5iQPPK62cDV3sr7k/edit#gid=416332645)

See ::subset example

TIP

For existing dimensions you can automatically generate a dimension or hiearchy definition from the model tab dimension or hierarchy menu

Subset keywords

TeamOne simplifies using ALL, LEAVES and ROOTS subsets by providing subset keywords. In certain places, like ::view or ::dimension definitions, you can use a subset keyword instead of subset name or MDX expression. TeamOne will automatically check if subset exists and create one if needed:

Subset keyword	Description
$ALL	Will refer to the ALL subset. If the subset does not exist, it will be created using the next MDX expression: {HIERARCHIZE({[dimensionName].[hierarchyName].members})}
$LEAVES	Will refer to the LEAVES subset. If the subset does not exist, it will be created using the next MDX expression: {TM1FILTERBYLEVEL( {[dimensionName].[hierarchyName].members}, 0)}
$ROOTS	Will refer to the ROOTS subset. If the subset does not exist, it will be created using the next MDX expression: {FILTER( {[dimensionName].[hierarchyName].members}, [dimensionName].[hierarchyName].CurrentMember.Parent.Name = "")}

MDX mods

You can manipulate with subsets expressions using MDX mods instead of the actual MDX. It saves time, makes it easy to use by non-technical users and simple to understand the subset content. For instance, compare this MDX expression:

{FILTER( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Period].[Period].[FY2025]}, ALL )}, 0 )}, [Period].[Period].[quarter] = "Q3" )}

to the equivalent expression using MDX mods:

FY2025>>expand>>level=0>>quarter=Q3

wildcard expressions are supported as well:

\FY20*\>>descendants>>level=0>>status=OP?N>>first=5

MDX mods can be added to subset expression after double closing angle bracket >>. Please see the table below for all currently supported MDX mods:

MDX mod	Description
ancestors	Will select the first parent for each member of the current set. On the backend it will wrap your current mdx into {HIERARCHIZE({GENERATE( ${mdx}, UNION({[DimName].[HierName].CurrentMember.Ancestors},{[DimName].[HierName].CurrentMember}) ) })}
ancestorsall	Will select all the parents for each member of the current set. On the backend it will wrap your current mdx into {HIERARCHIZE({GENERATE( ${mdx}, UNION({[DimName].[HierName].CurrentMember.AncestorsAll},{[DimName].[HierName].CurrentMember}) ) })}
expand	Performs a drill down for each member of the current set. On the backend it will wrap your current mdx into {TM1DRILLDOWNMEMBER( ${mdx}, ALL )}
children	Include direct children elements for each member of the current set. On the backend it will wrap your current mdx into {GENERATE( ${mdx}, [${dim}].[${hier}].CurrentMember.Children )}
descendants	Include all descendant elements for each member of the current set. On the backend it will wrap your current mdx into {DESCENDANTS(${mdx})}
distinct	Removes duplicates from the current set. On the backend it will wrap your current mdx into {DISTINCT(${mdx})}
first=%number%	Get the first number elements. On the backend it will wrap your current mdx into {HEAD( ${mdx}, ${number} )}
last=%number%	Get the last number elements. On the backend it will wrap your current mdx into {TAIL( ${mdx}, ${number} )}
level=%number%	Will filter the current set by the specified number. On the backend it will wrap your current mdx into {TM1FILTERBYLEVEL( ${mdx}, ${number} )}
sortasc	Will sort the current set in ascending order. On the backend it will wrap your current mdx into {TM1SORT( ${mdx}, ASC )}
sortasc=%AttributeName%	Will sort the current set in ascending order by the specificed attribute. On the backend it will wrap your current mdx into {ORDER( ${mdx}, [DimName].[AttributeName],ASC )}
sortdesc	Will sort the current set in descending order. On the backend it will wrap your current mdx into {TM1SORT( ${mdx}, ASC )}
sortdesc=%AttributeName%	Will sort the current set in descending order by the specificed attribute. On the backend it will wrap your current mdx into {ORDER( ${mdx}, [DimName].[AttributeName],DESC )}
%attribute%=%value%	Will filter the current set by the specified attribute & value. You can use a wildcard expression for the attribute value. On the backend it will wrap your current mdx into {FILTER( ${mdx}, [${dim}].[${hier}].CurrentMember.Properties("${attribute}") = "${value}" )} or {TM1FILTERBYPATTERN( ${mdx}, "${wildcard value}", "${attribute}")} if a wildcard value is provided
toggle=%ElmName%	Will toggle (expand or collapse) the element state. On the backend it will wrap your current mdx into {ToggleDrillState( ${mdx}, {[DimName].[HierName].[ElmName]} )}. You can also provide multiple elements: toggle=%ElmName1,ElmName2,...%

::element.rename

Use ::element.rename block to rename dimension/hierarchy elements without loosing the data.

Option / parameter	Description
-dimension	Dimension to
-hierarchy	Hierarchy (you can omit if using the default dimension hierarchy)
Element 1-N	Use a separate row to specify the existing element names in Column A and new element names in Column B. Keep in mind, TeamOne will skip non-existing element names, which could be the case when you already performed the renaming or if you just made a typo

See ::element.rename example

TIP

You can combine ::element.rename and ::dimension blocks on the same sheet, so you can easily manage the dimesion from one place.

::element.security

Use ::element.security block to define element security

You specify groups definitions starting from column 3. In each column you can use:

• a subset name in a format: $SubsetName
• a single group name
• a semicolon delimited group list, i.e.: Group 1; Group 2; Group 3
• a valid MDX expression, i.e.: {[}Groups].members}
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Sec*Level?/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: Managers;Deputy;/Sec*Level?/

Option / parameter	Description
-recreate	Wipe all security first
-createGroups	Include this option if you want to create non-existing security groups
-dimension	Dimension name (mandatory)
-hiearchy	Hierarchy name (optional), if omitted, the default hierarchy will be used
Element 1-N	Use a separate row to specify dimension elements, see below for syntax. Starting from column 3 (under the corresponding group definition), you can specify the security privileges, which could be on of the following: NONE, READ, WRITE, RESERVE, ADMIN, LOCK. If ommited, the default NONE privilege will be set

Element 1-N syntax:

• an element name
• a semicolon delimited element list, i.e.: FY2022 Jan;FY2022 Feb;FY2022 Mar
• a valid MDX expression, i.e.: {[Period].[Fiscal Quarter].[FY2022 Q1].children}
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /FY202? */
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: FY2022 Jan;FY2022 Feb;FY2022 Mae;/FY2021*/

See ::element.security example

TIP

You can automatically generate ::element.security definition from the model tab dimension or hiearchy menu

::dimension.security

Use ::dimension.security block to define dimension and hiearchy security (hiearchy security was introduced in IBM Planning Analytics 2.0.9.5)

You specify groups definitions starting from column 3. In each column you can use:

• a subset name in a format: $SubsetName
• a single group name
• a semicolon delimited group list, i.e.: Group 1; Group 2; Group 3
• a valid MDX expression, i.e.: {[}Groups].members}
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Sec*Level?/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: Managers;Deputy;/Sec*Level?/

Patterns make it super easy to setup security for alternate hierarchies, for instance: /Product:/ will set the security for all the hierarchies of the Product dimension

Option / parameter	Description
-recreate	Wipe all security first
-createGroups	Include this option if you want to create non-existing security groups
Dimension/hiearchy 1-N	Use a separate row to specify dimensions and hierarchies (in a format dimension:hiearchy), see below for syntax. Starting from column 3 (under the corresponding group definition), you can specify the security privileges, which could be on of the following: NONE, READ. If ommited, the default NONE privilege will be set

Dimension 1-N syntax:

• a dimension name
• a semicolon delimited dimension list, i.e.: Calendar:Fiscal Year;Employee;Job Type Assumption
• a valid MDX expression, i.e.: {[}Dimensions].members}
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Revenu?:*/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: Revenue;Employee;/*assumption*/

See ::dimension.security example

TIP

You can automatically generate ::dimension.security definition from the model tab dimensions menu

::cube.security

Use ::cube.security block to define cube security

You specify groups definitions starting from column 3. In each column you can use:

• a subset name in a format: $SubsetName
• a single group name
• a semicolon delimited group list, i.e.: Group 1; Group 2; Group 3
• a valid MDX expression, i.e.: {[}Groups].members}
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Sec*Level?/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: Managers;Deputy;/Sec*Level?/

Option / parameter	Description
-recreate	Wipe all security first
-createGroups	Include this option if you want to create non-existing security groups
Cube 1-N	Use a separate row to specify cubes, see below for syntax. Starting from column 3 (under the corresponding group definition), you can specify the security privileges, which could be on of the following: NONE, READ, WRITE, RESERVE, ADMIN, LOCK. If ommited, the default NONE privilege will be set

Cube 1-N syntax:

• a cube name
• a semicolon delimited cube list, i.e.: Calendar;Employee;Job Type Assumption
• a valid MDX expression, i.e.: {[}Cubes].members}
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Revenu?/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: Revenue;Employee;/*assumption*/

See ::cube.security example

Cube dimension security

TeamOne will also set READ privilege for each cube dimension if no access was defined previously. For instance, you want to give READ access for Group B to Revenue cube. Previously you gave WRITE access for Group B to Product dimension. When setting READ access to Revenue cube, TeamOne will set READ access to all the dimensions in Revenue cube, but will keep Product dimension as WRITE.

TIP

You can automatically generate ::cube.security definition from the model tab cubes menu

::application.security

Use ::application.security block to define element security

You specify groups definitions starting from column 3. In each column you can use:

• a subset name in a format: $SubsetName
• a single group name
• a semicolon delimited group list, i.e.: Group 1; Group 2; Group 3
• a valid MDX expression, i.e.: {[}Groups].members}
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Sec*Level?/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: Managers;Deputy;/Sec*Level?/

Option / parameter	Description
-recreate	Wipe all security first
-createGroups	Include this option if you want to create non-existing security groups
Application 1-N	Use a separate row to specify dimension elements, see below for syntax. Starting from column 3 (under the corresponding group definition), you can specify the security privileges, which could be on of the following: NONE, READ or ADMIN. If ommited, the NONE privilege will be set

Application 1-N syntax:

• an application name
• a semicolon delimited application list, i.e.: Analysis\Employee.view;Analysis\Expenses.view
• a valid MDX expression, i.e.: "{TM1DRILLDOWNMEMBER( {[}ApplicationEntries].[Analysis]}, ALL, RECURSIVE )}"
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /Reports*/ or /Reports.????/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: Analysis\Employee.view;Reports*/

See ::application.security example

Default application security

Use -recreate option to default all application security to READ. Then specify which applications should be restricted using NONE, READ or ADMIN privilege.

::process.security

Use ::process.security block to define process security

You specify groups definitions starting from column 3. In each column you can use:

• a subset name in a format: $SubsetName
• a single group name
• a semicolon delimited group list, i.e.: Group 1; Group 2; Group 3
• a valid MDX expression, i.e.: {[}Groups].members}
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Sec*Level?/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: Managers;Deputy;/Sec*Level?/

Option / parameter	Description
-recreate	Wipe all security first
-createGroups	Include this option if you want to create non-existing security groups
Process 1-N	Use a separate row to specify processes, see below for syntax. Starting from column 3 (under the corresponding group definition), you can specify the security privileges, which could be on of the following: NONE, READ. If ommited, the default NONE privilege will be set

Process 1-N syntax:

• a process name
• a semicolon delimited process list, i.e.: copy_version;create_version;cache_views
• a valid MDX expression, i.e.: {[}Processes].members}
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or */vers?ion*/
• a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a semicolon delimited list, i.e.: copy_version;create_version;/*create*/

See ::process.security example

::code.prolog

Use ::prolog.code (you can also use just ::code) block to write a prolog code It can be very handy when combining with sheets formulas, for instance, you can create and autopopulate TM1 functions based on the sheets values:

See ::code.prolog example

::code.epilog

Use ::epilog.code block to write an epilog code It can be very handy when combining with sheets formulas, for instance, you can create and autopopulate TM1 functions based on the sheets values:

See ::code.prolog example

comments

You can use a single line and multiline comments in ::code blocks:

• # character will comment out a single line
• #[ and #] will comment out multiple lines

See comments example

Format code

This function will format code and make it look like a documentation. Code formatting includes:

• code block header: white text and indigo background
• -options: italic text style
• comments: grey text color
• alternating colors background for all rows
• rows grouping for code blocks

Run code

This function will execute code blocks on the current sheet.