Page 1 of 1

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

Posted: Thu Jun 11, 2009 7:43 pm
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

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

Posted: Thu Jun 11, 2009 10:34 pm
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;

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

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

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

Posted: Fri Jun 12, 2009 6:28 am
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.)