wimgielis.com

Neat MDX subset tricks in TI

• Apr. 6, 2016

	Example files with this article:
	Neat MDX subset tricks in TI

Update April 2016

Please scroll down for an improved solution starting with TM1 version 10.2.2 FP1.

Introduction

It is been a while since I posted up an article on my website, apologies! Here is one showing off a number of interesting tricks when you use MDX subsets in Turbo Integrator. We all (should) know that when an MDX subset expression yields no dimension elements, the expression errors out and the TI process stops immediately. In a good number of cases, we might run into the possibility of returning zero elements with a given expression. For example, it the user can supply an element name with wildcards to a TI process, the TM1FilterByPattern function could find no elements and hence fail. What can we do about it? We should avoid loops, but more importantly, TI has no Like operator to match 2 strings with wildcards. It would not be quite far-fetched to roll our own function in TI to implement the Like operator functionality. Just to begin with… how to create a function in TI that can be reused just as User Defined Functions in Excel VBA?

So, let me describe the topic of the TI process in this article: we should look up a given element name in all dimensions of a TM1 model an list all instances: dimension name and element name. Wildcards * and ? are allowed in the choice of element name. We limit ourselves to element names for this exercise, aliases will not be dealt with.

3 MDX subset tricks

I wrote a TI process to do just that. I avoid While-End loops over all elements in a dimension (even though tM1 is very fast in doing this). This article is about MDX subsets. My 2 neat tricks:

1. In order to not let the MDX subset error when no element matches the pattern, I always add the first dimension element. The matches are then found from index 2 in the subset onwards;
2. Adding the first dimension element to the subset can be done using a comma (see code below);
3. In order to avoid performance degradation with dynamic MDX subsets, I convert them to static subsets with 1 command (instead of looping)

TI code

This is all done in the Prolog tab of a TI process:

# Wim Gielis
# https://www.wimgielis.com
#####
# Neat MDX subset tricks in Turbo Integrator
# 08/24/13
#####
DataSourceAsciiDelimiter = ';';
DataSourceAsciiQuoteCharacter = '';
cSubset = 'temp';

pFile = Trim( pFile );
If( Long( pFile ) = 0 );
   pFile = 'Search for element.csv';
EndIf;


vElements = 0;

# Title record in a text file
AsciiOutput( pFile, 'Dimension', 'Element Index', 'Element Type', 'Element Name' );


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

   vDim = Dimnm( '}Dimensions', d );
   If( Dimsiz( vDim ) > 0 );

      vMDX = '{[' | vDim | '].[' | Dfrst( vDim ) | '], TM1FilterByPattern( TM1SubsetAll( [' | vDim | '] ), "' | pElement | '" )}';

      SubsetDestroy( vDim, cSubset );
      SubsetCreateByMDX( cSubset, vMDX );

      # Make the subset static by removing the first element, getting rid of this element is useful anyway
      SubsetElementDelete( vDim, cSubset, 1 );

      # Loop through the matches, if any
      m = 1;
      While( m <= SubsetGetSize( vDim, cSubset ));
         vElement = SubsetGetElementName( vDim, cSubset, m );
         AsciiOutput( pFile, vDim, NumberToString( Dimix( vDim, vElement )), DType( vDim, vElement ), vElement );
         vElements = vElements+1;
         m = m + 1;
      End;
   EndIf;

   d = d+1;
End;

pElement effectively is a parameter to the process. It is a String parameter where the user can choose for wildcard characters. Here is the code in the Epilog tab:

If( vElements = 0 );
   AsciiOutput( pFile, 'The element ''' | pElement | ''' was not found in any of the dimensions.' );
EndIf;

The code above shows off the 3 neat tricks. Tricks 1 and 2 can be found in the variable vMDX, while trick 3 presents itself in the form of the function SubsetElementDelete (I delete at index 1). Feel free to steal these useful tricks and to use them in your own elaborate TI processes.

In a TI process, converting a dynamic subset to a static one is easy: add a temporary element and delete back immediately. For example:

SubsetCreateByMDX( 'MySubsetName','{TM1FilterByLevel( TM1SubsetAll( [BS_Account] ), 0 )}' );
SubsetElementInsert( 'BS_Account', MySubsetName, Dimnm( 'BS_Account', 1 ), 1 );
SubsetElementDelete( 'BS_Account', MySubsetName, 1 );

Or, using a less known function Dfrst:

SubsetCreateByMDX( 'MySubsetName','{TM1FilterByLevel( TM1SubsetAll( [BS_Account] ), 0 )}' );
SubsetElementInsert( 'BS_Account', MySubsetName, Dfrst( 'BS_Account' ), 1 );
SubsetElementDelete( 'BS_Account', MySubsetName, 1 );

Update April 2016

The TI function SubsetMDXSet can help us to make the code even more short and clean:

If( SubsetExists( 'BS_Account', 'MySubsetName' ) = 0 );
SubsetCreate( 'BS_Account', 'MySubsetName' );
EndIf;
SubsetMDXSet( 'BS_Account', 'MySubsetName', '{...}' );
SubsetMDXSet( 'BS_Account', 'MySubsetName', '' );

The last but one line above sets an MDX expression (assuming you would want to do that). The last line makes the subset static by removing the MDX expression. Existing elements of the subset will be kept. SubsetMDXSet is available as of TM1 10.2.2 FP1. See the official documentation update.

• « Previous TM1 article
• Next TM1 article »