Deleting unusefull elements from dimension

[ralph007]

Hi All,

I have some problem with some TI process.

I want to delete elements from customers dimension which not have any idea in cubes where this dimension 'Customer' is using. I prepared processes which looking in each single cube and copied preparing temp cube. After this operation I have two dimensions :

Live Customer Dim - 'Customer'
Temp Customer Dim - 'TempCustomer'

'TempCustomer' dim included string elements I want to delete from 'Customer' dimension.

I tried to write some code but is not working. Code below:

sDestDimName = 'Customer';
sSourceDimName = 'TempCustomer';
nX = 1;
nLimit = DIMSIZ(sSourceDimName);
WHILE(nX<=nLimit);


sElementName = DIMNM(sSourceDimName,nX);
IF(sElementName@<>'');
DIMENSIONELEMENTDELETE(sDestDimName,sElementName);

ELSE;

nX = nLimit + 1;

ENDIF;

END;

But this not want to work maybe someone has experience how to delete elements from one dimension comparing other dimension. In final I shouldn't have all elements existing in 'TempCustomer' dim in 'Customer' dim.

Many thanks for any suggestion.

[Gabor]

I would try something like that on Prolog tab, but haven't run below code:

sDestDimName = 'Customer';
sSourceDimName = 'TempCustomer';
nX = 1;
nLimit = DIMSIZ( sSourceDimName );
WHILE( nX <= nLimit );
sElementName = DIMNM( sSourceDimName, nX );
IF( DIMIX( sDestDimName, sElementName ) > 0 );
DIMENSIONELEMENTDELETE( sDestDimName, sElementName );
ENDIF;
nX = nX + 1;
END;

[ralph007]

Hi,

Thanks Gabor for respond, How long this process should run? I'm waiting about 15 minutes and server not responding. The source dim has about 600000 elements that's maybe the reason?

Any other suggestion??

Many thanks

[declanr]

The concept is correct but when deleting elements in a loop you will need to loop from the max DOWN... otherwise as you delete element 1, you next move onto element 2 but whats now element 2 was previously element 3 so you never actually checked element 2 and your max limit is no longer actually the max number of elements etc...


Long story short, reverse the loop.

[ralph007]

Hi declanr,

But in this example I'm not deleting elements from source dimension. I'm checking index in source dimension and checking if this element exist in destination dimension and if is deleting it.

I don't think if I reverse the loop that will fix this problem. How the code should look like from your perspective?

Many thanks for help

[declanr]

No sorry you are correct, I just scanned it quickly without noticing it being against 2 dims.

[rmackenzie]

Thanks Gabor for respond, How long this process should run?s

Gabor's code corrects a couple of issues in yours:
1. You increment nX by nLimit+1 but he increments nX by 1, of which the latter is correct
2. You conditionally do the increment, when you should do it unconditionally - you might get into an endless loop situation otherwise.

I'm waiting about 15 minutes and server not responding.

The way to test if the code gets the desired outcomes is to:
1. Create a dev server with no data and run the code. If you have a data issue causing the long wait, this should eradicate that.
2. Create a dev server with just Customer and TempCustomer and no cubes - like (1) but a purer test

HTH

[ralph007]

Hi,

Gabor's code working but for small test dimension. I created test dimensions and it's working correctly I only have a problem with big customer dimension (it has about 600000 elements there).

I'm doing this test on test server.

Any suggestion?

Many thanks

[rmackenzie]

I only have a problem with big customer dimension (it has about 600000 elements there).

This can happen when you have large and/ or dense cubes and you try and delete elements. For every deletion you do, TM1 does a reconfiguration of it's affected in-memory objects which can take time when the cubes are large.

An alternative is to detach the unwanted elements from main hierarchies and bundle them to a 'Unwanted' consolidation. Overnight, perhaps, you can then deal with the maintenance on the children of this consolidation when time taken to run the TI is less important?

[ralph007]

Hi Robin,

Thanks for info I'll try to run this overnight and will look if this will delete all not necessary elements.

Regards

Rafal