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
Mass Element Deletion
- Michel Zijlema
- Site Admin
- Posts: 712
- Joined: Wed May 14, 2008 5:22 am
- OLAP Product: TM1, PALO
- Version: both 2.5 and higher
- Excel Version: 2003-2007-2010
- Location: Netherlands
- Contact:
Re: Mass Element Deletion
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
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
- MVP
- Posts: 373
- Joined: Wed May 14, 2008 1:21 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 2003
- Location: Chicago, IL USA
Re: Mass Element Deletion
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.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1
Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1
Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
- Michel Zijlema
- Site Admin
- Posts: 712
- Joined: Wed May 14, 2008 5:22 am
- OLAP Product: TM1, PALO
- Version: both 2.5 and higher
- Excel Version: 2003-2007-2010
- Location: Netherlands
- Contact:
Re: Mass Element Deletion
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.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.
Michel
- Eric
- MVP
- Posts: 373
- Joined: Wed May 14, 2008 1:21 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 2003
- Location: Chicago, IL USA
Re: Mass Element Deletion
Good point. Sorry
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1
Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1
Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
Re: Mass Element Deletion
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
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
- Site Admin
- Posts: 712
- Joined: Wed May 14, 2008 5:22 am
- OLAP Product: TM1, PALO
- Version: both 2.5 and higher
- Excel Version: 2003-2007-2010
- Location: Netherlands
- Contact:
Re: Mass Element Deletion
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 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 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
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
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');
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');
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;
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