I forgot a dimension in my cube !
- Aug. 28, 2016
Introduction
You know how it goes, creating cubes at high speed for the customer, deadlines and even more cubes are putting pressure on you. You create a cube from scratch, let's say, it contains 10 dimensions that you carefully look up in the not-so-user-friendly dialog of "Create cube". Remark: after all those years it still strikes me that this dialog screen does not let us create a new cube based on the dimensions of a different (existing) cube. Many times cube dimensions are in a similar order than other cubes.
Anyway, so I was saying that it is quite plausible that you created a cube but you forgot that one dimension. I do know that we can use the TI function CubeCreate, but it's just me… I prefer a generic TI script in that case over (again) painfully selecting your dimensions from the dialog screen, or typing dimension names in a TI script.
For my regular blog readers, here is the TI code. It is "limited" to cubes of 20 dimensions, feel free to extend the code for even larger cubes.
Sample code
First create an empty process containing 3 parameters:
| Parameter | Type | Default Value | Prompt Question |
| pCube | String | Cube name ? | |
| pNewDim | String | New dimension name ? | |
| pIndexOfNewDim | Numeric | 0 | Index of new dim ( at least 1 ) ? |
Then we have the code in the Prolog section of the process:
If( CubeExists( pCube ) = 0 );
ProcessError;
EndIf;
If( DimensionExists( pNewDim ) = 0 );
ProcessError;
EndIf;
vCube = pCube;
# trace the cube dimensions
z = 'tmp_new_dim';
DimensionCreate( z );
d = 1;
While( d > 0 );
vDim = Tabdim( vCube, d );
If( vDim @= '' );
d = 0;
Else;
DimensionElementInsert( z, '', vDim, 'N' );
d = d + 1;
EndIf;
End;
# Make sure that the new to be added dimension does not already exist in the cube
# If it is, then stop the process
If( Dimix( z, pNewDim ) > 0 );
ProcessError;
EndIf;
vNrOfDims = Dimsiz( z );
If( pIndexOfNewDim <= 0 );
pIndexOfNewDim = 1;
EndIf;
If( pIndexOfNewDim <= vNrOfDims );
DimensionElementInsert( z, Dimnm( z, pIndexOfNewDim ), pNewDim, 'N' );
Else;
DimensionElementInsert( z, '', pNewDim, 'N' );
EndIf;
vNrOfDims = Dimsiz( z );
# 2. remove the rules - MAKE SURE YOU HAVE A BACKUP OF RULES
CubeRuleDestroy( vCube );
# 3. destroy the data - MAKE SURE YOU HAVE A BACKUP OF DATA
CubeClearData( vCube );
# 4. destroy the cube - MAKE SURE YOU HAVE A BACKUP OF VIEWS, SECURITY, PICKLIST CUBES, ...
CubeDestroy( vCube );
# 5. create the cube
If( vNrOfDims = 3 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ) );
ElseIf( vNrOfDims = 4 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ) );
ElseIf( vNrOfDims = 5 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ) );
ElseIf( vNrOfDims = 6 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ) );
ElseIf( vNrOfDims = 7 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ) );
ElseIf( vNrOfDims = 8 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ) );
ElseIf( vNrOfDims = 9 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ) );
ElseIf( vNrOfDims = 10 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ) );
ElseIf( vNrOfDims = 11 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ) );
ElseIf( vNrOfDims = 12 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ), Dimnm( z, 12 ) );
ElseIf( vNrOfDims = 13 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ), Dimnm( z, 12 ), Dimnm( z, 13 ) );
ElseIf( vNrOfDims = 14 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ), Dimnm( z, 12 ), Dimnm( z, 13 ), Dimnm( z, 14 ) );
ElseIf( vNrOfDims = 15 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ), Dimnm( z, 12 ), Dimnm( z, 13 ), Dimnm( z, 14 ), Dimnm( z, 15 ) );
ElseIf( vNrOfDims = 16 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ), Dimnm( z, 12 ), Dimnm( z, 13 ), Dimnm( z, 14 ), Dimnm( z, 15 ), Dimnm( z, 16 ) );
ElseIf( vNrOfDims = 17 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ), Dimnm( z, 12 ), Dimnm( z, 13 ), Dimnm( z, 14), Dimnm( z, 15 ), Dimnm( z, 16 ), Dimnm( z, 17 ) );
ElseIf( vNrOfDims = 18 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ), Dimnm( z, 12 ), Dimnm( z, 13 ), Dimnm( z, 14 ), Dimnm( z, 15 ), Dimnm( z, 16 ), Dimnm( z, 17 ), Dimnm( z, 18 ) );
ElseIf( vNrOfDims = 19 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ), Dimnm( z, 12 ), Dimnm( z, 13 ), Dimnm( z, 14 ), Dimnm( z, 15 ), Dimnm( z, 16 ), Dimnm( z, 17 ), Dimnm( z, 18 ), Dimnm( z, 19 ) );
ElseIf( vNrOfDims = 20 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ), Dimnm( z, 12 ), Dimnm( z, 13 ), Dimnm( z, 14 ), Dimnm( z, 15 ), Dimnm( z, 16 ), Dimnm( z, 17 ), Dimnm( z, 18 ), Dimnm( z, 19 ), Dimnm( z, 20 ) );
ElseIf( vNrOfDims = 21 );
CubeCreate( vCube, Dimnm( z, 1 ), Dimnm( z, 2 ), Dimnm( z, 3 ), Dimnm( z, 4 ), Dimnm( z, 5 ), Dimnm( z, 6 ), Dimnm( z, 7 ), Dimnm( z, 8 ), Dimnm( z, 9 ), Dimnm( z, 10 ), Dimnm( z, 11 ), Dimnm( z, 12 ), Dimnm( z, 13 ), Dimnm( z, 14 ), Dimnm( z, 15 ), Dimnm( z, 16 ), Dimnm( z, 17 ), Dimnm( z, 18 ), Dimnm( z, 19 ), Dimnm( z, 20 ), Dimnm( z, 21 ) );
Else;
# too many dimensions to handle - extend the process or do it the good old manual way
ProcessError;
EndIf;
DimensionDestroy( z );
TM1 10.2.2 FP6 introduces a new Turbo Integrator function: CubeDimensionCountGet. This can simplify a loop over cube dimensions using Tabdim.
See you all next time ! :-)
