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
SubsetCreateByMDX
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;