TurboIntegrator Audit Cube (zCubeDimsTI)
Rules Audit Cube (zCubeDims)
Sharing dimensions between cubes is very common in TM1 applications. As a result, it is often necessary to identify and document which dimensions are used in which cubes. This article describes two methods for building a dimension audit cube for tracking dimension usage in TM1.
You can build and populate dimension audit cubes with either a TurboIntegrator process or TM1 rules. Either method will provide you with a single cube from which you can view dimension usage in individual cubes and across your entire TM1 model. Each method, however, has distinct advantages (noted in green text) and disadvantages (red text).
Can be run on a user-determined basis
You must explicitly run the TurboIntegrator process to create/populate the audit cube, must be aware of possible server locking issues
Requires knowledge of the maximum number of dimensions in all cubes before running the process
Always live and up-to-date
Easy to modify
Depending on the number of dimensions in your cubes, can be slow to re-calculate
Requires knowledge of the maximum number of dimensions in all cubes to create/maintain the rule
Regardless of which method you use to create a dimension audit cube, the following TM1 objects and functions are critical to the creation and population of the audit cube:
}Cubes and }Dimensions are control dimensions that hold all the cubes and dimensions in the TM1 model
TABDIM(<cube>,<index>) is a TM1 rules function that returns the dimension name for a given cube at the specified dimension index position
The following screen shots show dimension audit cubes created with TurboIntegrator and TM1 rules.
|
TurboIntegrator Audit Cube (zCubeDimsTI) |
Rules Audit Cube (zCubeDims) |
|
|
|
Both views have been zero suppressed and filtered to show the dimension position within the cube in ascending order.
The Exists element displays an X where a dimension exists in a cube – this is helpful when auditing all dimensions in your TM1 model (see below). Position shows the dimension order within the cube – better for a ‘by-cube’ view as above.
The TurboIntegrator process that creates the audit cube completes the following actions:
Setup up variables for cube names, view names, and maximum number of dimensions in cubes.
Create and clear the audit cube (if necessary)
Setup looping variables
Loop through the cube and dimension combinations using TabDim (looping through the cubes first then the dimensions in the cube) to return the current dimension’s position in a cube, if any, and write the result to the audit cube.
Add an X into the Exists element in the audit cube
To build the TurboIntegrator process that will create your dimension audit cube:
Create the dimension zCubeDimMeasures on your TM1 server. The dimension should contain the following elements:
Exists - A string element
Position - A numeric element
Create a TurboIntegrator process with a Datasource Type of None.
Copy and paste the following code into the Prolog procedure of the process:
|
# Set Maximum number of Dimensions in ANY cube vMaxDims=30; #Set name of Audit cube vAuditCube='zCubeDimsTI'; #Set cube clear view vClearView = 'ClearView';
# Create vAuditCube if required (does not exist) IF (CubeExists(vAuditCube)<>1); CubeCreate(vAuditCube,'}Dimensions','}Cubes','zCubeDimMeasures'); ENDIF; # Create Clear Cube view if necessary IF (ViewExists (vAuditCube,vClearView) <> 1); ViewCreate(vAuditCube,vClearView); ViewRowDimensionSet(vAuditCube, vClearView, '}Cubes', 1); ViewRowDimensionSet(vAuditCube, vClearView, '}Dimensions', 2); ViewRowDimensionSet(vAuditCube, vClearView, 'zCubeDimMeasures', 3); ViewSuppressZeroesSet(vAuditCube, vClearView, 1); ENDIF; #Clear cube ViewZeroOut (vAuditCube, vClearView); # Set looping variables vNumCubes = DIMSIZ('}Cubes'); vCubeLoop=0; #Loop through cubes WHILE (vCubeLoop < vNumCubes); vCubeLoop = vCubeLoop + 1;
vCubeName=DIMNM ('}Cubes',vCubeLoop);
vEnumLoop = 0; # Loop through Dimensions WHILE (vEnumLoop < vMaxDims); vEnumLoop = vEnumLoop+1; vDimName = TABDIM(vCubeName,vEnumLoop); IF (vDimName @<> ''); CellPutN(vEnumLoop,vAuditCube,vDimName,vCubeName,'Position'); CellPutS('X',vAuditCube,vDimName,vCubeName,'Exists'); ENDIF; END; END; |
Edit the vMaxDims variable – set this to the maximum number of dimensions found in any cube on your TM1 server.
The maximum number of dimensions can easily be found by clicking the Cubes group in the Server Explorer, then sorting the #Dimensions column in the Properties pane in descending order. The largest number in the #Dimensions column is the maximum number of dimensions in any cube on the server.
To simplify process creation and maintenance, you can set the vMaxDims variable to a value much greater than the maximum number of dimensions found in any cube on your server. This will allow you to run the process without modification if you later add new cubes with more dimensions to your server. However, the resulting process will be less efficient.
IMPORTANT: The value of vMaxDims must never be less than the maximum number of dimensions found in any cube on your server; this will result in inaccurate data in the audit cube.
Save and run the process.
You now have a new dimension audit cube named zCubeDimsTI that you can use to monitor dimension usage in all cubes on your server.
The rules method for creating an audit cube uses a cube named zCubeDims to store audit data. The cube contains (in order) the }Cubes, }Dimensions, zCubeDim, zCubeDimMeasures dimensions. The zCubeDim dimension contains elements corresponding to the numbers 1 to n (where n is the maximum number of dimensions in any single cube), along with a top level consolidation. This dimension is used to pass index values to the TabDim() function.
Each value in zCubeDim is used to check whether, for each cube, a given dimension exists at the current zCubeDim position. If it does then the current zCubeDim element is entered as the Position value.
The top-level consolidation in zCubeDim allows you to view all the element positions at the same time, as there are no duplicates for any cube/dimension combination.
The Exists element is populated by checking for the existence of Position values at all levels.
To create the audit cube and associate rule:
Create the zCubeDimMeasures dimension as described above.
Create the }zCubeDim dimension.
This dimension should contain numeric elements corresponding to the number of dimensions found in the largest-dimensioned cube on your server. It should also contain a consolidation of all numeric elements named All CubeDims.
For example, if the cube with the most dimensions on your server contains five dimensions, the }zCubeDim dimension should be structured as follows:
A simple method for determining the largest-dimensioned cube on your server is described above.
Create the zCubeDims cube. The cube should contain the following dimensions, in order:
}Dimensions
}Cubes
zCubeDim
zCubeDimMeasures
Add and save the following rule to the zCubeDims cube:
|
['Position']=n: IF( TABDIM(!}Cubes,NUMBR(!zCubeDim)) @= !}Dimensions, NUMBR(!zCubeDim), 0 ); ['Exists']=s: IF(['Position']>0, 'X', '' ); |
You now have a new rules-driven audit cube named zCubeDims that you can use to monitor dimension usage in all cubes on your server.
You can help Applix improve the Recommended Practices web site by rating this article and providing comments or suggestions for improvement. Click Submit after choosing a rating and/or entering comments.