Wildcard Search in a Dimension

[Wim Gielis]

Attached you can find a TI process that finds an element in any of the dimensions of a TM1 model.
Wildcards * and ? are allowed in the element selection. The output is a file.

Wim

WG_search_for_element_name_with_wildcards.pro

(5.05 KiB) Downloaded 230 times

Code: Select all

If( Long( pElement ) = 0 );
   LogOutput( 'ERROR', 'The element is empty.' );
   ProcessError;
EndIf;

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


DataSourceAsciiDelimiter = ';';
DataSourceAsciiQuoteCharacter = '';

cSubset_1 = 'temp_1';
cSubset_2 = 'temp_2';
cSubset_3 = 'temp_3';

vElements = 0;

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


# loop over all 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_1 );
      SubsetCreateByMDX( cSubset_1, vMDX );

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

      # Loop through the matches, if any
      m = 1;
      While( m <= SubsetGetSize( vDim, cSubset_1 ));
         vElement = SubsetGetElementName( vDim, cSubset_1, m );
         AsciiOutput( pFile, vDim, NumberToString( Dimix( vDim, vElement )), Dtype( vDim, vElement ), vElement, 'Element' );
         vElements = vElements + 1;
         m = m + 1;
      End;

      # test for attributes (aliases)
      If( CubeExists( '}ElementAttributes_' | vDim ) > 0 );
      If( DimensionExists( '}ElementAttributes_' | vDim ) > 0 );

         vMDX_Alias = '{TM1SubsetAll( [' | vDim | '] )}';

         If( SubsetExists( vDim, cSubset_2 ) = 0 );
            SubsetCreateByMDX( cSubset_2, vMDX_Alias );
         EndIf;

         # there should be attributes, so loop through them
         a = 1;
         While( a <= Dimsiz( '}ElementAttributes_' | vDim ));

            vAttributeName = Dimnm( '}ElementAttributes_' | vDim, a );
            vAttributeType = Subst( Dtype( '}ElementAttributes_' | vDim, vAttributeName ), 2, 1 );

            If( vAttributeType @= 'A' );

               # apply an alias
               SubsetAliasSet( vDim, cSubset_2, vAttributeName );

               vMDX_Alias = '{ [' | vDim | '].[' | SubsetGetElementName( vDim, cSubset_2, 1 ) | '], TM1FilterByPattern( TM1SubsetToSet([' | vDim | '], "' | cSubset_2 | '"), "' | pElement | '" )} }';

               If( SubsetExists( vDim, cSubset_3 ) = 0 );
                  SubsetCreateByMDX( cSubset_3, vMDX_Alias );
               EndIf;

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

               # Loop through the matches, if any
               m = 1;
               While( m <= SubsetGetSize( vDim, cSubset_3 ));
                  vElement = SubsetGetElementName( vDim, cSubset_3, m );
                  vAttribute = Attrs( vDim, vElement, vAttributeName );
                  If( vAttribute @<> vElement & Long( Trim( vAttribute )) > 0 );
                     AsciiOutput( pFile, vDim, NumberToString( Dimix( vDim, vElement )), Dtype( vDim, vElement ), vAttribute, 'Alias: ' | vAttributeName );
                  EndIf;
                  vElements = vElements + 1;
                  m = m + 1;
               End;
               SubsetDestroy( vDim, cSubset_3 );

            EndIf;

            a = a + 1;

         End;

         SubsetDestroy( vDim, cSubset_2 );

      EndIf;
      EndIf;

      SubsetDestroy( vDim, cSubset_1 );

   EndIf;

   d = d + 1;
End;