Clear Data in a Single TI

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Clear the data from 40 cubes using single TI process?

Post by Wim Gielis »

Hello,

I wrote some code to generalize the process.

A loop over the cubes is done, skipping internal control cubes. Application cubes that use control dimensions are not skipped, though.

USE THIS CODE WITH CAUTION

Subsets and views are created on the fly, and destroyed afterwards. Cube cells are zero'ed out. No dimension elements will be removed. Rules will still apply if defined.

The code could be optimized to include only lowest-level elements (now all dimension elements are taken), but since you could have string elements in the last dimension and text on (some) consolidations, you would have to check for that first. I will post that code later.

Here is the code in the Prolog of a process with None as data source:

Code: Select all

###################
# Wim Gielis
# June 11, 2009
# ATTENTION: PLEASE KNOW WHAT YOU ARE DOING
# SINCE THIS CODE WILL CLEAR ALL DATA FROM ALL APPLICATION CUBES ON A CERTAIN SERVER
# No responsibility will be taken in case of unforeseen loss of data.
###################


vViewName='MyTempView';
vSubsetName='MyTempSubset';


# loop over the cubes

iCube=1;

While(iCube<=DIMSIZ('}Cubes'));

     # the cube in the loop
     vCube=DIMNM('}Cubes',iCube);

     # exclude control cubes
     If(Subst(vCube,1,1)@<>'}');

          ViewDestroy(vCube,vViewName);
          ViewCreate(vCube,vViewName);

          # track the number of dimensions for this cube
          vNrOfDimensions=0;

          While(Long(Tabdim(vCube,vNrOfDimensions+1))>0);
               vNrOfDimensions=vNrOfDimensions+1;
          End;

          # loop over the dimensions in this cube
          iDim=1;

          While(iDim<=vNrOfDimensions);

               vDim=Tabdim(vCube,iDim);

               SubsetDestroy(vDim,vSubsetName);

               SubsetCreateByMDX(vSubsetName,'{TM1SUBSETALL( [' | vDim | '] )}');

               ViewSubsetAssign(vCube,vViewName,vDim,vSubsetName);

               iDim=iDim+1;

          End;

          ViewZeroOut(vCube,vViewName);

          ViewDestroy(vCube,vViewName);

     EndIf;

     iCube=iCube+1;

End;
Here is the code in the Epilog of the process:

Code: Select all

###################
# Wim Gielis
# June 11, 2009
# ATTENTION: PLEASE KNOW WHAT YOU ARE DOING
# SINCE THIS CODE WILL CLEAR ALL DATA FROM ALL APPLICATION CUBES ON A CERTAIN SERVER
# No responsibility will be taken in case of unforeseen loss of data.
###################



# loop over the cubes

iCube=1;

While(iCube<=DIMSIZ('}Cubes'));

     # the cube in the loop
     vCube=DIMNM('}Cubes',iCube);

     # exclude control cubes
     If(Subst(vCube,1,1)@<>'}');

          # loop over the dimensions in this cube

          iDim=1;

          While(Long(Tabdim(vCube,iDim))>0);

               vDim=Tabdim(vCube,iDim);

               SubsetDestroy(vDim,vSubsetName);

               iDim=iDim+1;

          End;

     EndIf;

     iCube=iCube+1;

End;
Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Clear the data from 40 cubes using single TI process?

Post by lotsaram »

Reddy, beware that Wim's code will clear out the entirety of each cube except for control cubes! You probably don't want to do this in practice. Depending on the cube(s) you are clearing you can use TI to build quite specific subsets/views to clear out only what you want. You could also use an attribute flag or subset of }Cubes so that the code only applies to particular cubes.

Also if you do want to zero out the whole cube then the two while loops in Wim's original code are not necessary as by default when a processing view is defined, until a subset is assigned to a dimension in the view the default subset for the dimension is subset ALL, there is therefore no need to loop through the cubes to determine the number of dimensions and then loop through the dimensions to assign subset ALL to each dimension. When defining a view to be used as a process data source or zero out not assigning a subset is shorthand for assigning subset ALL. You only need to assign a subset where you do want to restrict the view on one or more dimensions.

The following edited version of Wim's code would have the same effect.

Code: Select all

vViewName='MyTempView';
vSubsetName='MyTempSubset';

# loop over the cubes
iCube=1;
While(iCube<=DIMSIZ('}Cubes'));
     # the cube in the loop
     vCube=DIMNM('}Cubes',iCube);
     # exclude control cubes
     If(Subst(vCube,1,1)@<>'}');
          ViewDestroy(vCube,vViewName);
          ViewCreate(vCube,vViewName);
          ViewZeroOut(vCube,vViewName);
          ViewDestroy(vCube,vViewName);
     EndIf;
     iCube=iCube+1;
End;
Reddy
Posts: 17
Joined: Wed Jun 03, 2009 6:45 am
OLAP Product: TM1
Version: 9.4 FP1
Excel Version: 2007
Location: Dubai

Re: Clear the data from 40 cubes using single TI process?

Post by Reddy »

Thank You Wim and Lotsaram :)

First I copied codes given by Wim in Prolog and epilog procedures and executed the process without any modification, then almost all the data is cleared except some data here and there but then I ran a new process with Lotsaram codes in prolog procedure which resulted in complete initialization of db.

Sure! This is really something everyone would be looking after to initialize the entire database with single TI process.

Thank you very much Wim and Lotsaram for your attention :idea:

-Reddy
Thanks :)
Reddy
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Clear the data from 40 cubes using single TI process?

Post by Alan Kirk »

Reddy wrote:Thank You Wim and Lotsaram :)

First I copied codes given by Wim in Prolog and epilog procedures and executed the process without any modification, then almost all the data is cleared except some data here and there but then I ran a new process with Lotsaram codes in prolog procedure which resulted in complete initialization of db.
I would suggest one modification to Lotsaram's otherwise most useful loop;

Code: Select all

         
          ViewCreate(vCube,vViewName);
          CubeSetLogChanges(vCube, 0);
          ViewZeroOut(vCube,vViewName);
          CubeSetLogChanges(vCube, 1);
          ViewDestroy(vCube,vViewName);
It's not going to matter if you have only a small set of sample data in there. If you have a FULL set of data, you could end up with log files running to hundreds of megabytes quite easily.

I know of what I speak from bitter experience.

(Also, writing to the logs will slow down the clearing process. Writing to disk is the slowest part of the lot.)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Post Reply