Mass Element Deletion

[Ajay]

Hi All,

I have a cube which has a few dimensions with more than 5000 elements. As you can imagine it does run slowly sometimes so I am looking to remove elements which have never had data against them.

I could do this manually but it would take ages so does anyone have a quick way of identifying such elements in a cube ?

Thanks
Ajay

[Michel Zijlema]

Hi Ajay,

You could define an attribute 'active' on the regarding dimension and setup processes that run through an all view on the cubes using the dimension, where the processes mark the 'active' attribute when there is value loaded.
After this you can run a process that deletes all elements where the 'active' attribute is not marked.


Michel

[Eric]

You can also Run a list of all Elements and compare them to a list of all elements with Supress Zeors on and the ones that do not have a match are the ones you need to delete.

[Michel Zijlema]

You can also Run a list of all Elements and compare them to a list of all elements with Supress Zeors on and the ones that do not have a match are the ones you need to delete.

Please note that your method cannot be used when the dimension is used in more than one cube - an element can have data loaded against it in one cube and not in another.

Michel

[Eric]

Good point. Sorry

[George]

Hi All

My first post on this new forum, great idea.

Michael I am interested in your method, as I am a novice could you provide more detail on the best way do do this.

Cheers George

[Michel Zijlema]

Hi George,

If you have a dimension that is used in x cubes you can create x processes that use an 'all' view as a source on these cubes. An all view will be a view with all elements selected on all dimensions and with the 'Skip Consolidated' and 'Skip Calculated' flags disabled and the 'Skip Zeroes' enabled.
You can setup an 'Active' text attribute using the Attributes editor on the regarding dimension or using the statement

Code: Select all

AttrInsert('dimname', '', 'Active', 'S');

on the Prolog tab of a TI process (where dimname needs to be replaced with the correct dimension name).
The processes that run through the 'all' views will run against all populated elements on the regarding dimension. By adding the statement

Code: Select all

AttrPutS('Y', 'dimname', dimname, 'Active');

on the Data tab of these processes the 'Active' attribute gets loaded.
After populating the attibute you can run a process with the all subset on the regarding dimension as a datasource with the statements

Code: Select all

  IF(AttrS('dimname', dimname, 'Active') @= '');
     DimensionElementDelete('dimname', dimname);
  ENDIF;

on the MetaData tab to delete the unused elements (where the first 'dimname' is the name of the dimension and the second dimname is the current selected element in the dimension).

please note that the 'all' source views will be constructed in memory before the processes start - on big cubes this could lead to a significant increase in memory usage. If you're already short on memory this could lead to your server running out of memory. I would advise to test this using a test environment.

Michel