All dimension atttributes in a TM1 model

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.

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

TI code

cFile = 'All attributes.csv';

DataSourceAsciiQuoteCharacter = '';
DataSourceAsciiDelimiter = ';';



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

# 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
               ASCIIOUTPUT(cFile, vDim, vAttributeName, vAttributeType_Name);

               a = a + 1;
            END;

         ENDIF;

      ENDIF;

   ENDIF;

   d = d + 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).

There you go, seems like giveaway Friday already! :-)




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links