Performance of DimensionElementComponentDelete in Ti

[MSidat]

Hi All,

Just read Steve Rowe's post regarding speed of TI in Dimension Creation, and did not want to hijack it, although it may be related.

I have recently noticed a major performance issue using the DimensionElementComponentDelete function in a TI in 10.1 (CE)

I have a dimension consisting of around 2.5m elements. It only has one root (Level 1) with the remainder its children i.e.:
Top Consol
--Element 1
--Element ....
--Element 2500000

Dependant on an Attribute I had to re order the dimension and place each element under one of three new consolidations so it would now look like the following:
Top Consol
--Bucket 1
----Element 1
----Element ....
--Bucket 2
----Element 6
----Element ....
--Bucket 3
----Element 8
----Element ....

So My original TI had the attribute cube view as the Source, and nothing in any of the tabs except the following in the metadata:

Code: Select all

IF(vValue @='M');
   strParent = 'Mixed Orders';
ELSEIF(vValue @='I');
   strParent = 'Internal Orders';
ELSEIF(vValue @='E');
   strParent = 'External Orders';
ELSE;
   strParent = 'Unallocated Orders';
ENDIF;


strDimtoCheck = 'Sales_Document_Nos_Dim';
strTopConsol = 'Total Sales Documents Nos';

IF (ELLEV(strDimtoCheck, vSalesNum) = 0);
   IF (DIMIX(strDimtoCheck, strParent) = 0);
      DimensionElementComponentAdd(strDimtoCheck, strTopConsol,strParent, 1);
  ENDIF;

   IF(ELPAR(strDimtoCheck, vSalesNum,1) @<> strParent);
     DimensionElementComponentDelete(strDimtoCheck, ELPAR(strDimtoCheck, vSalesNum,1) ,vSalesNum);
     DimensionElementComponentAdd(strDimtoCheck, strParent ,vSalesNum, 1);
   ENDIF;
ENDIF;

This was processing at about 5 records a second (So in total would have been in excess of 100 hours)!!!

As soon as I removed the DimensionElementComponentDelete Line it sped through the records in 90 seconds. Thats 2.5M Elements added underneath a new consolidation.

I then created another TI with the same datasource with just 3 lines in the metadata to ensure all elements just have the new Parent:

Code: Select all

strDimtoCheck = 'Sales_Document_Nos_Dim';
strTopConsol = 'Total Sales Documents Nos';
DimensionElementComponentDelete(strDimtoCheck, strTopConsol ,vSalesNum);

Again this was processing at about 5 records a second which is a bit of a shambles really.

I have also tried the new function with the DIRECT postfix, and its the same issue.

Would like to hear any ideas how I could change/fix this so that I can efficiently move Elements without worrying if they roll up twice anywhere, or is this a potential Bug?

[paulsimon]

Hi

I haven't tried this in 10.1 but what I normally do is to call a process to break all consol links on the Prolog. Perhaps keeping this confined to one process so that it is all complete, before you start inserting any new links, rather than deleting links and inserting new links in the same process might help?

Your process should also probably only concentrate on the base level since, in future, you are going to need to de-link the elements from any of your new consolidations, not just the top level element.

I use something like the following, where the StdElemFlag is an attribute on the dimension that lets me retain top level hierarchies that typically don't change.

IF( dimix( vElemAttrDim, vStdElemFlag ) <> 0 ) ;

IF( attrs( vDim, vElem, 'StdElemFlag' ) @= 'Y' ) ;
ItemSkip ;
ENDIF ;

ENDIF ;

# For each parent, delete the link between
# this element and the parent

vParent = elpar(vDim,vElem,1) ;
WHILE( vParent @<> '' );
dimensionelementcomponentdelete(vDim,vParent,vElem) ;
# After deletion and parents shuffle down so what was
# parent number 2 is now parent number 1.
vParent = elpar(vDim,vElem,1) ;
END ;

Perhaps you could try this and let me know if it works. This has been used on some very large dimensions, in 9.5.1, so if you still get a problem then it looks as though some sort of issue got introduced in version 10.1.

Regards

Paul Simon

[MSidat]

Thanks for the reply Paul,

I have also used the technique you mentioned in the past. And as you point out is is quiet efficient.

Saying that I have also in the past done similar processing in 9.0.2 and 9.5.2 to what I am trying to do at the present and never had any issues although the biggest dimension I worked with in the past was just under 400k.
The main reason for having it in the Metadata tab is to ensure we only reallocate the ones needed rather than every one of the 2.5m elements.

I still cant justify the processing speed of around 5 records a second. I think I will raise it with our IBM Support Partner and see if they can do their own test and if required raise it as a bug.

Cheers.

[declanr]

You could run it in 2 TIs:

In the first:

Data tab - put a 1 against an attribute for all the elements that need to change.

Epilog tab - run TI 2


In the second:

Prolog tab - while loop that deletes the elements as components if the attribute is a 1

metadata tab - associated elements with their new parents



The above might help in the meantime but it seems more like you want to find out why it isn't working rather than find another way that will work.