Spot double counting issues

  • Apr. 05, 2013

Introduction

When dimension elements have several parents, the potential for double counting exists. In this article, I show some relatively simple loops to search for elements that have more than 1 parent in a dimension. When such an element is found, it does not necessarily mean that there is double counting: TM1 dimensions allow parallel hierarchies within the same dimension. Yet, often we see that (difficult) dimensions leave the potential for double countings.

A TI approach

The code below investigates all dimensions in the TM1 model at hand. For each dimension, every element is looped over, looking at its number of parents. If strictly bigger than 1, a CSV file is populated with information. The CSV file is stored in the data directory but you can easily change that in the code. The code goes into the Prolog or Epilog part of a standard Turbo Integrator process. 

# Wim Gielis
# https://www.wimgielis.com


#####
# TI code to help us spot double counting issues
# 04/05/13
#####


DataSourceAsciiDelimiter = ';';
DataSourceAsciiQuoteCharacter = '';

pFile = Trim( pFile );
If( Long( pFile ) = 0 );
   pFile = 'Multiple parents.csv';
EndIf;

pFile = GetProcessErrorFileDirectory | '\' | pFile;

vDoubles = 0;

# Title record
AsciiOutput( pFile, 'Dimension', 'Element Index', 'Element Name', 'Element Type', 'Number of parents', 'Immediate parents...' );


d = 1;
While( d <= Dimsiz('}Dimensions') );

   vDim = Dimnm('}Dimensions', d);

   If( Scan('\' | vDim | '\', pExcludeDims) = 0);

      e = 1;
      While( e <= Dimsiz(vDim) );

         vElem = Dimnm(vDim, e);

         If( ElparN(vDim, vElem) > 1);

            # get the parent names
            vParents = '';
            p = 1;
            While( p <= ElparN(vDim, vElem));
               vParent = Elpar(vDim, vElem, p);
               vParents = vParents | DataSourceAsciiDelimiter | vParent;
               p = p + 1;
            End;

            AsciiOutput(pFile, vDim, NumberToString(e), vElem, Dtype(vDim, vElem), NumberToString( ElparN(vDim, vElem)), Delet(vParents, 1, Long(DataSourceAsciiDelimiter)));
            vDoubles = vDoubles + 1;

         EndIf;

         e = e + 1;

      End;

   EndIf;

   d = d + 1;
End;

I guess the code is quite understandable. Note that you can skip certain dimensions from investigation by mentioning them in 'pExcludeDims'. This is a String parameter to the process, and multiple dimensions to be excluded should be separated with the character \. The other String parameter is pFile, the filename that you want for the output. The file will be output in the Logging directory of TM1, so change it if you want a different location. Please make sure a previous version of the file is not opened at the time of running this process. In case no doubles are found at all, the fill will still be generated but it contains a message that no doubles were found. In the Epilog of the process: 

If( vDoubles = 0 );
   AsciiOutput(pFile, 'No elements with multiple parents were found.');
EndIf;

You can definitely go more fancy with these kind of processes but this little process will serve very well, I use it many times with customers.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links