Mass Element Deletion

Post Reply
User avatar
Ajay
Posts: 136
Joined: Wed May 14, 2008 8:27 am
OLAP Product: TM1
Version: 10.2.0
Excel Version: 2016
Location: London

Mass Element Deletion

Post by Ajay » Thu May 22, 2008 1:49 pm

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
10.2.0, w2008R2 OS Enterprise Edition, 1TB HD, 384GB RAM, MS Excel 2016, Cognos 10 BI

User avatar
Michel Zijlema
Site Admin
Posts: 706
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

Post by Michel Zijlema » Thu May 22, 2008 2:02 pm

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

User avatar
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

Post by Eric » Thu May 22, 2008 2:23 pm

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

User avatar
Michel Zijlema
Site Admin
Posts: 706
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

Post by Michel Zijlema » Thu May 22, 2008 2:37 pm

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

User avatar
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

Post by Eric » Thu May 22, 2008 2:38 pm

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

George
Posts: 1
Joined: Wed Jun 04, 2008 9:23 am

Re: Mass Element Deletion

Post by George » Wed Jun 04, 2008 9:42 am

Hi All

My first post on this new forum, great idea. :D

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

User avatar
Michel Zijlema
Site Admin
Posts: 706
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

Post by Michel Zijlema » Wed Jun 11, 2008 7:42 pm

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

Post Reply