Page 1 of 1
Mass Element Deletion
Posted: Thu May 22, 2008 1:49 pm
by 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
Re: Mass Element Deletion
Posted: Thu May 22, 2008 2:02 pm
by 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
Re: Mass Element Deletion
Posted: Thu May 22, 2008 2:23 pm
by 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.
Re: Mass Element Deletion
Posted: Thu May 22, 2008 2:37 pm
by Michel Zijlema
Eric wrote: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
Re: Mass Element Deletion
Posted: Thu May 22, 2008 2:38 pm
by Eric
Good point. Sorry
Re: Mass Element Deletion
Posted: Wed Jun 04, 2008 9:42 am
by 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
Re: Mass Element Deletion
Posted: Wed Jun 11, 2008 7:42 pm
by 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