TI performance during hierarchy unwind

[Ajay]

Hi All,

I'm experiencing a slow running TI process when removing a hierarchy from a dimension, during it's update.

The way I am doing this, is I am creating an MDX based subset as a source for the TI, and then simply deleting each item the subset returns. I am trying to make this a little more recyclable so I have the following MDX:

{TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | sDimName | '] )}, 1,2,3,4,5,6,7,8,9,10 ) }

I've added 1..10 levels to represent the various levels across the dimensions in my model.

For smaller dimensions the TI runs very quickly, but for others which have about 300-400,000 elements it seems to take an age, and I am unsure whether the performance hit is down to:

a) the MDX statement above,
b) the DimensionDeleteElement( MyDimension, TheElementsFromTheSourceSubset),
c) the compiling actioned when the metadata has deleted the elements before moving on to completion (no Data or Epilog code).

Before moving on to test each and see whether I need to perhaps add attribute levels to identify parents from children, I wondered what your experiences of unwinding hierarchies were with respect to dimensions above a few hundred thousand elements, and whether there is a faster way of accomplishing what I need ?

Finally, is there a smarter way I can build the MDX to work out the levels I need rather than "hard-code" the 10 levels above ?

Thankyou
Ajay

[Edward Stuart]

Assuming you are looking for a generic process that will work across multiple scenarios I would suggest looking at the Bedrock Dimension Unwind Consolidation process

Personally I've used if DTYPE = C or ELLEV <> 0 but it depends if you've got multiple hierarchies or not and what the update process is

[Edward Stuart]

On a static subset that is

[qml]

Hi Ajay,

This is not a very efficient way of doing it. Every time you remove an element, the whole MDX expression is recalculated, which can take a long time for a big dimension.

Are you trying to remove all consolidations or just a specific list? In the first case I would do something like that (please note the reverse order in which the dimension is cycled through):

Code: Select all

i = DIMSIZ( sDim );
WHILE( i > 0 );

	sElement = DIMNM( sDim, i );

	IF( DTYPE( sDim, sElement ) @= 'C' );

		DimensionElementDelete( sDim, sElement );
	ENDIF;

	i = i - 1;
END;

If you are removing specific elements only and MDX is needed (or at least very handy) then I would do something like this (note the reverse order and how the dynamic subset is turned into a static one before being used):

Code: Select all

sMDX = '';
SubsetCreateByMDX( sSub, sMDX, sDim );

# the below two lines implicitly turn the dynamic subset into a static one

SubsetElementInsert( sDim, sSub, DIMNM( sDim, 1 ), 1 );
SubsetElementDelete( sDim, sSub, 1 );

i = SubsetGetSize( sDim, sSub );
WHILE( i > 0 );

	sElement = SubsetGetElementName( sDim, sSub, i );

	DimensionElementDelete( sDim, sElement );

	i = i - 1;
END;

[paulsimon]

Hi Ajay

I personally think that deleting all consolidations is dangerous. Instead you should be aiming to just delete the links between elements. One issue with deleting consolidations, is that if your dimension update fails part way through then any static subsets that have those elements will lose those elements. If you have text data such as comments stored against those consolidations, then that data will also be lost.

As others have pointed out, Bedrock has an Unwind routine that will do that. I use my own set of routines.

Regards

Paul Simon