One-cell cube views


After some time, here is again a TM1 article. I wrote some piece of custom code this afternoon to create one-cell views. This is useful (at least I find it useful) when you have to create many views to be used in the Data source of TI processes. The code below will create a view on a chosen cube, it does not matter how many dimensions we have in the cube. For each dimension, I create a subset with 1 element: index-wise, the first element. That's it, we don't have to make life more difficult than necessary. I take care of the fact that (worst-case scenario) a dimension could be empty. In that case, the subset is empty but no error is thrown at you.

Below you can find 2 simple pictures of what the outcome of the process should be:


First create a new process containing a parameter:

ParameterTypeDefault ValuePrompt Question
pCubeString/Cube name ?

Then we have the following code in the Prolog section of the process:

# Wim Gielis #
##### # TI code to create a cube view with 1 cell # 10/27/2014 #####
vCube = Trim( pCube ); If( CubeExists( vCube ) = 0); ProcessError; EndIf; c0 = 'tmp_'; d = 0; While( Long( Tabdim( vCube, d + 1 )) > 0 ); vDim = Tabdim( vCube, d + 1 ); If( d = 0 ); ViewDestroy( vCube, c0 ); ViewCreate( vCube, c0 ); ViewExtractSkipCalcsSet( vCube, c0, 0 ); ViewExtractSkipRuleValuesSet( vCube, c0, 0 ); ViewExtractSkipZeroesSet( vCube, c0, 0 ); EndIf; SubsetDestroy( vDim, c0 ); SubsetCreateByMDX( c0, '{[' | vDim | '].[' | DFRST( vDim ) | ']}', vDim ); ViewSubsetAssign( vCube, c0, vDim, c0 ); ViewTitleDimensionSet( vCube, c0, vDim ); d = d + 1; End;

The constant c0 contains the name of the view and subsets. Change it if you want to have it changed. I guess the code is reasonably self-explanatory. Last thing to note is that I also have a chore running that picks up any cubes, dimension, views and subsets, starting with prefix "tmp_". So these temporary objects will not survive the next run of the clean up chore :-) They will definitely not clutter up the TM1 Data directory.

That’s it for now! See you next time at my website.


Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links