Clear Data in a Single TI

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

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

Post by Wim Gielis » Thu Jun 11, 2009 7:43 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

lotsaram
MVP
Posts: 3008
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

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

Post by lotsaram » Thu Jun 11, 2009 10:34 pm

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 » Fri Jun 12, 2009 5:36 am

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: 5729
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

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

Post by Alan Kirk » Fri Jun 12, 2009 6:28 am

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