Troubleshooting
Problem
The zero suppression feature of TM1 Server does not apply to MDX calculated rows and columns. This behaviour impacts add-hoc calculation created using the cubeviewer widget in Planning Analytics Workspace, Planning Analytics for Excel reports, and Cognos Analytics reports.
Resolving The Problem
This is expected behaviour of the TM1 Server. The zero suppression capability of the TM1 Server is based on values in leaf cells values in a cube. The TM1 Server does not base zero suppression on the values of consolidated or calculated rows and columns that are presented in views. This method of suppression allows for very fast performance on large views.
The calculation feature in Planning Analytics Workspace and Planning Analytics for Excel generate a MDX statement that includes a calculation. The calculation in the MDX statement is processed by MDX engine in TM1 server without ever being represented by a cell in a cube.
The following is an example of an Exploration Report in Planning Analytics for Excel that uses a calculation. The row named '1 + 2' is a calculation, not a member in the Numbers hierarchy used on the row axis.
The following MDX is generated by this report.
WITH
MEMBER [Numbers].[Numbers].[1 + 2] AS [Numbers].[Numbers].[1] + [Numbers].[Numbers].[2],FORMAT_STRING='#,##0;(#,##0);0'
SELECT {
[Letters].[Letters].[A]
} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0,
HEAD(GENERATE({
[Numbers].[Numbers].DEFAULTMEMBER,
[Numbers].[Numbers].[2]
} , STRTOSET( CASE WHEN 0 < INSTR([Numbers].[Numbers].CURRENTMEMBER.UNIQUENAME , "[2]") THEN "{" + [Numbers].[Numbers].CURRENTMEMBER.UNIQUENAME + ",
[Numbers].[Numbers].[1 + 2]
}" ELSE "{ " + [Numbers].[Numbers].CURRENTMEMBER.UNIQUENAME + " }" END ) , ALL), 500) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 1
FROM [Numbers&Letters]
MEMBER [Numbers].[Numbers].[1 + 2] AS [Numbers].[Numbers].[1] + [Numbers].[Numbers].[2],FORMAT_STRING='#,##0;(#,##0);0'
SELECT {
[Letters].[Letters].[A]
} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0,
HEAD(GENERATE({
[Numbers].[Numbers].DEFAULTMEMBER,
[Numbers].[Numbers].[2]
} , STRTOSET( CASE WHEN 0 < INSTR([Numbers].[Numbers].CURRENTMEMBER.UNIQUENAME , "[2]") THEN "{" + [Numbers].[Numbers].CURRENTMEMBER.UNIQUENAME + ",
[Numbers].[Numbers].[1 + 2]
}" ELSE "{ " + [Numbers].[Numbers].CURRENTMEMBER.UNIQUENAME + " }" END ) , ALL), 500) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 1
FROM [Numbers&Letters]
The calculation that determines the value in cell C16 is highlighted in the above MDX statement.
Because TM1 zero suppression is based on values of cells in the cube, the calculated row (row 16 in the above example) will not be suppressed.
In cases where a calculation is used frequently, and/or suppression is critical, consider representing the calculated row or column as a member in the TM1 Database. In the above example a member that represents row 16 may be added to Number hierarchy. This member could be a consolidation on members 1 and 2 or a leaf member with a cube rule to added the values of members 1 and 2. Either option will support zero suppression.
Document Location
Worldwide
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSD29G","label":"IBM Planning Analytics"},"ARM Category":[{"code":"a8m0z000000blsYAAQ","label":"Troubleshooting"}],"ARM Case Number":"","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
16 April 2020
UID
ibm16193341