Deleting a dimension element


Deleting elements in TM1… I see you thinking, that is dangerous! Because you can lose data, including those historical data for which you spent several evenings late in the office to get the data just right. Then again, from time to time it is needed to delete one or more elements. Just think about a given non-sensical customer ID that made its way into a dimension. Or that bloody dummy element called '#' that the TM1 Package Connector very easily slips into any dimension. Meaning, inserting it in a lot of dimensions before the blink of an eye!

TI approach

So, why not having a simple routine that takes out an element in 1 - or in all - dimensions in the TM1 model? This is only TI coding 101: a simple While()End() loop over dimensions suffices, coupled with a Dimix(), DimensionElementPrincipalName() and DimensionElementDelete() function. No other coding involved. I use DimensionElementPrincipalName() since I also want to allow to search for aliases. The cost of programming this is very small, hence I decided to allow this functionality too.

Please do note that running this code inadvertently can have deleterious effects on your TM1 model and its data, as well as on your own mental and physical sanity. Always have a good backup.

TI code

At run-time, the code asks for an element name. Next to that, either a valid dimension name, either opt to delete the chosen element in all model dimensions.

ParameterTypeDefault ValuePrompt Question
pElementStringName of the element to be deleted (aliases are allowed, wildcards are not)
pDimensionStringDimension name ?
pDeleteInAllDimensionsNumeric0Delete the element in all dimensions it can find ? 1 = Yes, All else = No

# Wim Gielis #
##### # Deleting an element into 1 or more dimensions - please careful ! # 08/30/13 #####
If( pDeleteInAllDimensions <> 1); # test for the dimension name If(DimensionExists( pDimension ) >0 ); vContinue = 1; Else; vContinue = 0; EndIf; Else; vContinue = 1; EndIf; If( vContinue = 1); d=1; While( d <= Dimsiz ( '}Dimensions' ) ); vDimension = Dimnm( '}Dimensions', d ); If( ( pDeleteInAllDimensions = 1 ) % ( ( pDeleteInAllDimensions <> 1 ) & ( vDimension @= pDimension ) ) ); If( Dimix( vDimension, pElement ) > 0 ); DimensionElementDelete( vDimension, DimensionElementPrincipalName( vDimension, pElement ) ); EndIf; EndIf; d = d + 1; End; EndIf;

Not much to add to this, I think, this is the kind of process a TM1 developer writes during a break or when waiting for the bus. Oh, well, I should add that I am responsible in no single way when you mess up your bosses' approved figures because the red sentence above is not red enough. (Color-blindness does not apply as an excuse)


Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links