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:
We can quickly add a Fiscal Quarter alternative hierarchy
::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" )}
{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
FY2025>>expand>>level=0>>quarter=Q3
wildcard expressions are supported as well:
\FY20*\>>descendants>>level=0>>status=OP?N>>first=5
\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 |
::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 |
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*/
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:
::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:
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.