Oct 2, 2020

TM1 hidden features

IBM Planning Analytics powered by TM1 (TM1) is full of features, over the years the IBM server team continues improving it to meet various customer requirements. This article digs into some of its hidden features. All features described below are currently undocumented, use them with care!

DebugUtility, the Swiss Army Knife

The DebugUtility function can do many things from clearing the cube cache to getting the list of feeders, overfed and underfed cells:

  • DebugUtility( 114, 0, 0, ‘myCube’ , ‘targetFolder’ , ” ); #Create a .cub file with scrambled data
  • DebugUtility ( 125 , 0 , 0 , ‘myCube’ , ” , ” ); #Clear cube cache
  • DebugUtility ( 100 , 0 , 0 , ‘myCube’ , ‘/path/myCube_feeders.txt’ , ” ); #Create a file with the list of all feeders of Cube.
  • DebugUtility ( 105 , 0 , 1 , ‘myCube’ , ‘/path/myCube_overfed_cells.txt’ , ” ); #Create a file with the list of overfed Cells (Cells Fed but with 0 value)
  • DebugUtility ( 113 , 0 , 0 , ‘myCube’ , ‘/path/myCube_underfed_cells.txt’ , ” ); #Create a file with the list of underfed Cells (Cells with Rule but no Feeder)
  • DebugUtility( 107, 0, 0, ‘Cubename’, ‘Debug.txt’, ” ); #Troubleshooting stack overflow errors.

For example executing DebugUtility( 113, 0, 0, ‘General Ledger’ , ‘C:ArcGL-underfed-cells.txt’ , ” ); in a TM1 process will create a txt file with the list of all under-fed intersections for cube General Ledger:

⚠️When working with large cube it may take some time to perform these functions, so it is recommended that you test these functions first in a non-production environment.

AttrPutS Unique Flag

The AttrPutS function includes a 6th argument called Unique Flag:

  • AttrPutS(Value, DimName, ElName, AttrName, [LangLocaleCode], [Unique Flag])

Unique Flag is an optional argument, if set to 1 it will append element name as suffix if alias is not unique.

No need to write extra code to check uniqueness of Alias

Cannot adjust format of Alias

Write to locked objects

This function allows the TI process to write to cubes or elements which are locked.

  • CubeLockOverride ( flag ) with flag: 0 (false) or 1 (true)

Unintended consequences with updating locked intersections

Ensure process can update all intersections

Swapping alias with principal name

Swapping the element principal name with the specified Alias is possible with the function SwapAliasWithPrincipalName:

  • SwapAliasWithPrincipalName(‘DimName’, ‘AliasName’, Flag) Flag: must be 0 to take effect.

Does not work with dimensions with hierarchies

Convenience to fix incorrect element principal name


Entering the dimension name after the MDX_Expression allows a null set to be created in the target dimension without error.

  • SubsetCreatebyMDX(SubName, MDX_Expression, [DimName], [AsTemp])

Need extra code to check validity of subset created

No need to write extra code to ensure subset is created without error

Exiting a while loop

The While loop will repeat a series of statement while a given condition is true. Another way to stop a while loop is to add the Break statement:

Pause a process

The Sleep function is a simple functions that takes one argument, the number of milliseconds to pause a process execution:

  • Sleep(1000) to wait 1 seconds

The Sleep function can be useful in the following use cases:

  • When you are doing something with ExecuteCommand that is writing to the file system to provide a buffer of 1 – 2 seconds to make sure the file is finished writing to disk and isn’t locked before you try and do something else with it
  • For implementing a queue or dependency system with file based semaphores and the TI is monitoring for either the prescence or abscence of a file. Use Sleep to pause activity for a given interval between checks

Getting all ancestors with MDX

The TM1ROLLUP function returns all the ancestors of the specific dimension element and itself.

For example, in Account GL dimension, to return the ancestors of element “GL10000” the MDX query would be:

  • { TM1ROLLUP( {[Account GL].[GL10000] }, {[Account GL].[GL10000] }) }

Timestamp in milliseconds

The HierarchyTimeLastUpdated function is the only process function to measure time in milliseconds.

This function can be used to measure any operations in milliseconds. To do that you will need first to create two dummy hierarchies (One to get the start time and the other one to get the end time). The following code will measure how much time the SubsetGetSize function took:

#Create Hierarchy 1 to get Start Time in millisecondsDimensionHierarchyCreate(cHierDim , cDummyHier1 );nStart = HierarchyTimeLastUpdated(cHierDim , cDummyHier1 );#Execute Subset MDXnCount = SubsetGetSize(cDim, cSubset);#Create Hierarchy 1 to get End Time in millisecondsDimensionHierarchyCreate(cHierDim , cDummyHier2 );nEnd = HierarchyTimeLastUpdated(cHierDim , cDummyHier2 );#Calculate the time needed for MDX to complete with milliseconds precisionnDurationSec = (nEnd-nStart) * 86400 * 1000;#Clean up and delete temporary hierarchiesIF (HierarchyExists(cHierDim , cDummyHier1 )>0);HierarchyDestroy(cHierDim , cDummyHier1 );ENDIF;IF (HierarchyExists(cHierDim , cDummyHier2 )>0);HierarchyDestroy(cHierDim , cDummyHier2 );ENDIF;


Related content

Loading related content