Slow MDX Subsets? Best Practice Guide to Maintain Sets in TM1

Having MDX based dynamic subsets in TM1 is a fantastic way to ensure that reports, forms and processes that use those subsets contain all required elements. But there is an overhead. Dynamic sets require MDX and the MDX must be executed each time it is used. That might not seem like a significant overhead, but when you have large dimensions with multiple hierarchies, or views or reports over large cubes with many dimensions, using MDX can impact performance. The question is how to over come that. The answer is to store the MDX in a cube and then insert the MDX on demand, update the dimension and remove the MDX. This post shows you exactly how to do it, including the TI code to get it done.

As a bonus, we will have a location to store relevant information about the subset in the cube alongside the MDX, so you have a place to keep all the metadata on subsets for easy reference later. There are times when a user/developer opens a Dimension Subset and asks the below questions:

• What was the basis of the subset?
• Does the subset show all elements as per the latest Dimension update?
• Whether a process uses Dynamic subset and is taking longer to run?

For example, the screenshot below is a N Level subset in the Account dimension, where you cannot view the details on the subset and whether the subset includes the latest elements.

How to Record and View Details About a Subset

We have a Dimension called ‘Account’ and an Alias ‘Code and Name’ and need to create an N Level Subset, which shows all the N level elements and display the Alias. We would normally use MDX in this subset to retrieve all the N level elements. But if the dimension only updates once a day, why keep it dynamic?

Step 1: Create a Subset Definition Cube

To record & view the subset details, first step is to create a ‘Subset Definition’ cube with three dimensions as below:

• }Dimensions
• Index
• Subset Definition Measure

The 4 measures to be part of Subset Definition Measure dimension will be,

• Subset Name (String Element) – To record the name of the subset
• MDX Query (String Element) – To record the MDX query definition
• Convert To Static (String Element) – A logical operator to determine if to convert the subset to static
• Show Alias (String Element) – To use an Alias name in the subset

Step 2: Add MDX Details to Cube

The second step is to update the subset details or definition within the cube. We select the relevant dimension name in the cube and updating the details about the subset.

As you can see we have added the MDX required to update the set called “NLevel”, we have indicated to convert this to a static subset and show the alias Code and Name.

Step 3: Create a Process to Update all Sets using MDX and Convert to Static

The third step is to create a process with Data Source as None, pDimension as the parameter to provide the Dimension name, and copy paste the below code in Prolog section of the process.

#-- Variables
cSubsetDefinitionCube = 'Subset Definition';
sDimension = Trim(pDimension);
cIndexDim = 'Index';
nIndexDimSize = DIMSIZ(cIndexDim);
nCounter = 1;

#-- Create Subset
While (nCounter <= nIndexDimSize);
   sIndexName = DIMNM(cIndexDim, nCounter);
   sSubsetName = CellGetS(cSubsetDefinitionCube, sDimension, sIndexName, 'Subset Name');
   
   If(sSubsetName @<> '');
      sMDX = CellGetS(cSubsetDefinitionCube, sDimension, sIndexName, 'MDX Query');
      sConvertToStatic = CellGetS(cSubsetDefinitionCube, sDimension, sIndexName, 'Convert To Static');
      sAlias = CellGetS(cSubsetDefinitionCube, sDimension, sIndexName, 'Show Alias');

      If(SubsetExists(sDimension, sSubsetName) = 0);
         SubsetCreate(sDimension, sSubsetName);

      Else;
         SubsetDeleteAllElements(sDimension, sSubsetName);

      EndIf;

      SubsetMDXSet(sDimension, sSubsetName, sMDX);

      If(sConvertToStatic @= 'Yes');
         SubsetMDXSet(sDimension, sSubsetName, '');
      EndIf;

      If(sAlias @<> '');
         SubsetAliasSet( sDimension, sSubsetName, sAlias );
      EndIf;


   EndIf;

   nCounter = nCounter + 1;
End;

Step 4: Create Chore to update MDX based Subsets

The fourth & final step is to execute the process or schedule the process using an overnight chore. The subset gets created and the users can refer to the Subset Definition cube for viewing the subset details.

Do You Have a Performance Problems with TM1?

If you would like help with how to improve the performance of TM1 for your users, or how implement this awesome idea into your environment, please give us a yell.