Page 1 of 1
Structure details for a cube
Posted: Wed Dec 14, 2016 1:42 pm
by repoman
Hi, I am new to this forum, and not a TM1 developer. I am using version 10.2.2 of TM1 and Excel 2013. I am working on a metadata management project and need to gather the specific structure information from TM1 cubes. I am trying to gather for each cube in a given instance the name of the cube, the name of each dimension on the cube, and the name of each measure on the cube(if any). I am not interested in any of the actual data loaded in the cube, just the structure of the cube.
My technical TM1 teammates are not aware of a way to do this. They can get the cube and its dimensions, but not the measures for some reason. I would appreciate any thoughts or suggestions on how to accomplish this.
Thanks!
Re: Structure details for a cube
Posted: Wed Dec 14, 2016 1:51 pm
by tomok
repoman wrote:They can get the cube and its dimensions, but not the measures for some reason.
This is probably because a "Measures" dimension is not required in a TM1 cube. It is perfectly acceptable not to have one even if that goes against best practice. For instance, you could have a GL cube with the dimensions Version, Time, Center, and Account. There are no measures. Best practice would have you add such a dimension and have a single element called "Amount" but you don't have to.
So, unfortunately, you'll have to analyze each cube manually and figure out if it has a measures dimension and it it does, what those individual measures are in that cube. I don't know of an automated tool that is intelligent enough to handle that.
Re: Structure details for a cube
Posted: Wed Dec 14, 2016 4:28 pm
by Wim Gielis
tomok wrote:repoman wrote:They can get the cube and its dimensions, but not the measures for some reason.
This is probably because a "Measures" dimension is not required in a TM1 cube. It is perfectly acceptable not to have one even if that goes against best practice. For instance, you could have a GL cube with the dimensions Version, Time, Center, and Account. There are no measures. Best practice would have you add such a dimension and have a single element called "Amount" but you don't have to.
So, unfortunately, you'll have to analyze each cube manually and figure out if it has a measures dimension and it it does, what those individual measures are in that cube. I don't know of an automated tool that is intelligent enough to handle that.
Hi,
As this information is / could be stored in the cube }CubeProperties, a few loops
(over cubes, then over dimensions to see if the stored }MEASURES_DIMENSION entry belongs to the cube, then over the elements in that dimension) will do.
Use Tabdim to read out the dimension name in a cube at a certain position, use AsciiOutput to export the measures dimension elements to a text file, and that's it.
Of course, and that's what Tom is probably referring to, if the MEASURES_DIMENSION is not filled in, it's different.
You could assume the last dimension in the cube. In either case this could be some manual work of first filling in the MEASURES_DIMENSION element and then executing your routine.
Re: Structure details for a cube
Posted: Wed Dec 14, 2016 4:43 pm
by Wim Gielis
Here's coding taken from one of my generic TI processes:
Code: Select all
# Loop over cubes. Verify cube logging and set the measures dimension
c = 1;
While( c <= Dimsiz( '}Cubes' ));
vCube = Dimnm( '}Cubes', c );
########################
# CUBE LOGGING
########################
If( CellGetS( '}CubeProperties', vCube, 'LOGGING' ) @<> '' &
CellGetS( '}CubeProperties', vCube, 'LOGGING' ) @<> 'YES' );
AsciiOutput( GetProcessErrorFileDirectory | 'UNLOGGED CUBES.txt', vCube );
EndIf;
########################
# MEASURES DIMENSION
########################
nDimCount = 2;
While( TabDim( vCube, nDimCount + 1 ) @<> '' );
nDimCount = nDimCount + 1;
End;
vMeasuresDim = Tabdim( vCube, nDimCount );
If( CellGetS( '}CubeProperties', vCube, 'MEASURES_DIMENSION' ) @<> vMeasuresDim );
CellPutS( vMeasuresDim, '}CubeProperties', vCube, 'MEASURES_DIMENSION' );
EndIf;
c = c + 1;
End;
CubeSaveData( '}CubeProperties' );
Re: Structure details for a cube
Posted: Wed Dec 14, 2016 6:03 pm
by tomok
Wim Gielis wrote:As this information is / could be stored in the cube }CubeProperties, a few loops
(over cubes, then over dimensions to see if the stored }MEASURES_DIMENSION entry belongs to the cube, then over the elements in that dimension) will do.
Use Tabdim to read out the dimension name in a cube at a certain position, use AsciiOutput to export the measures dimension elements to a text file, and that's it.
Of course, and that's what Tom is probably referring to, if the MEASURES_DIMENSION is not filled in, it's different.
You could assume the last dimension in the cube. In either case this could be some manual work of first filling in the MEASURES_DIMENSION element and then executing your routine.
The problem I was referring to is the fact that having a measures dimension is completely optional and even if you do, populating this attribute in the }CubeProperties cube is also optional. If either of these conditions are false then there is no automated way to get at what the OP wants. I have seen a lot of TM1 systems in my day and only a very few where both these conditions were true. YMMV.
Re: Structure details for a cube
Posted: Wed Dec 14, 2016 6:11 pm
by Wim Gielis
Indeed, then I wrote down correctly what you meant Tom
Re: Structure details for a cube
Posted: Wed Dec 14, 2016 6:32 pm
by gtonkin
In a similar manner to what Wim has suggested to detail the various object, I use Excel to read various system cubes through Active Forms, SubNMs, TabDims etc. to build a reasonable overview of a model.
I have attached a portion of this for you to aid you in listing the cubes, and dimensions and then the overlay of which cubes use which dimensions.
Start by updating the server name on the R-Reference sheet, recalculate the workbook and see if this gets you closer to your goal.
You can then add sheets to read from the other control cubes as required.
As a general rule, I always make the last dimension in my cube the Measures dimension as often times you require string measures and these can only be accommodated by having them in the last dimension - said differently, you may want to make an assumption that the last dimension in a cube is the measures BUT confirm this based on the elements and content.
Re: Structure details for a cube
Posted: Fri Dec 16, 2016 1:49 pm
by repoman
Thanks all for the valuable input on my question. Much appreciated, - Happy Holidays!