Wim‘s Excel, TM1 & soccer site

All dimension attributes in a TM1 model

Oct. 3, 2021

Introduction

It is best practice to structure your TM1 applications (and thereby, coding, business rules, …) in the best possible way. For example, an alias that is used to complement ID numbers for elements in a dimension, is always called 'Description'. 'Desc' might be somewhat confusing with the descending sort order, but that is not a big problem. It would be less interesting if you mix up 'Description' and 'Desc' and 'descr' and 'second name': be consistent and as a result, your code maintenance will benefit from these little gestures.

Hence, why not use a generic routine to output all the attributes to a text file? It is feasible and far from difficult. You get my code for free, as always. Put all the code in the Advanced > Prolog tab of a blank TI process. The generated file will be created in the Data directory for the TM1 model at hand.

TI code

cFile = 'All attributes.csv';

DataSourceAsciiQuoteCharacter = '';
DataSourceAsciiDelimiter = ';';

# output headers to a file
TextOutput( cFile, 'Dimension', 'AttributeName', 'AttributeType' );

## Regular attributes

# loop over the dimensions that contain attributes
d = 1;
While( d <= Dimsiz( '}Dimensions' ));

   vDim = Dimnm( '}Dimensions', d );
   vDimAttr = '}ElementAttributes_' | vDim;

   # Skip certain dimensions
   If( Scan( '}tp_', vDim ) <> 1 );

      If( DimensionExists( vDimAttr ) > 0 );

         If( Dimsiz( vDimAttr ) > 0 );

            # there appear to be attributes, so let’s loop over them
            a = 1;
            While( a <= Dimsiz( vDimAttr ));

               vAttributeName = Dimnm( vDimAttr, a );
               vAttributeType = Dtype( vDimAttr, vAttributeName );

               # determine the attribute type
               If( vAttributeType @= 'AA' );
                  vAttributeType_Name = 'Alias';
               ElseIf( vAttributeType @= 'AN' );
                  vAttributeType_Name = 'Numeric attribute';
               ElseIf( vAttributeName @= 'Format' );
                  vAttributeType_Name = 'Formatting attribute';
               Else;
                  vAttributeType_Name = 'Text attribute';
               EndIf;

               # output to a file
               TextOutput( cFile, vDim, vAttributeName, vAttributeType_Name );

               a = a + 1;
            End;

         EndIf;

      EndIf;

   EndIf;

   d = d + 1;

End;

## Attributes for builtin objects

# loop over the objects that can have such attributes
o = 1;
While( o <= 4 );

   If( o = 1 );
      vObject = 'Cube';
   ElseIf( o = 2 );
      vObject = 'Dimension';
   ElseIf( o = 3 );
      vObject = 'Process';
   Else;
      vObject = 'Chore';
   EndIf;

   vDimAttr = '}' | vObject | 'Attributes';

   If( CubeExists( vDimAttr ) > 0 );
   If( DimensionExists( vDimAttr ) > 0 );
   If( Dimsiz( vDimAttr ) > 0 );
   
       # there appear to be attributes, so let's loop over them
       a = 1;
       While( a <= Dimsiz( vDimAttr ));
   
          vAttributeName = Dimnm( vDimAttr, a );
          vAttributeType = DType( vDimAttr, vAttributeName );
   
          # determine the attribute type
          If( vAttributeType @= 'AA' );
             vAttributeType_Name = 'Alias';
          ElseIf( vAttributeType @= 'AN' );
             vAttributeType_Name = 'Numeric attribute';
          ElseIf( vAttributeName @= 'Format' );
             vAttributeType_Name = 'Formatting attribute';
          Else;
             vAttributeType_Name = 'Text attribute';
          EndIf;
   
          # output to a file
          TextOutput( vFile, vObject, vAttributeName, vAttributeType_Name, 'Attributes for objects' );
   
          a = a + 1;
       End;
   
   EndIf;
   EndIf;
   EndIf;

   o = o + 1;

End;

Mind the use of the DTYPE function to determine the type of attribute: alias, text attribute, numeric attribute. Also, the formatting attribute is identified. The result of this nifty process is a CSV text file that you can easily open up in MS Excel. Change the setting for the delimiter to , if you are not using the ; to that end. In Excel, you can easily filter and sort or turn the output in a table (press Ctrl-T to do this).

Next to these attributes, we also have the possibility to create attributes on a select key dimensions of the TM1 model:

Cubes
Dimensions
Processes
Chores

This means that these key dimensions have 2 ways to receive attributes: in the normal way, using functions like: AttrInsert( '}Cubes', '', 'Name of the attribute', 'A' ); and AttrPutS( 'second name', '}Cubes', 'Name of the cube', 'Name of the attribute' );. The relatively newer way of achieving (different) attributes for cubes, is: CubeAttrInsert( '', 'Name of the attribute', 'A' ); and CubeAttrPutS( 'second name', 'Name of the cube', 'Name of the attribute' );. Similar functions exist for dimensions, processes and chores.

Lastly, there is even the possibility to localize names of objects. This could be very useful for multi-language TM1 models. In that case we use functions like: AttrInsert( '}Cubes', '', 'Name of the attribute', 'A' ); and AttrPutS( 'second name', '}Cubes', 'Name of the cube', 'Name of the attribute' );. The relatively newer way of achieving (different) attributes for cubes, is: CubeAttrInsert( '', 'Name of the attribute', 'A' ); and CubeAttrPutS( 'second name', 'Name of the cube', 'Name of the attribute' );. Similar functions exist for dimensions, processes and chores.

wimgielis.com

Excel, TM1 and soccer

All dimension attributes in a TM1 model

Introduction

TI code

Homepage

Section contents

About Wim

Other links