Clear the data from 40 cubes using single TI process?

[Reddy]

Hi,

My TM1 system is loaded with data for around 40 cubes, is there a way to clear or initialize the data in all these cubes by writing and executing a 'single' TI process. It is little laborious to do manually one by one in each cube using data spreading method "clear" .

Thanks in advance
Reddy.

[Steve Vincent]

From the help files;

ViewZeroOut
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function sets all data points in a view to zero.

Syntax

ViewZeroOut(Cube, ViewName);

Arguments

Cube The parent cube of the view you want to zero out.
ViewName The view you want to zero out.

Example

ViewZeroOut('99sales', '1st Quarter Actuals');
This example sets all data points in the 1st Quarter Actuals view to zero.

[Reddy]

Thanks Steve

This has solved my problem, now I can initialize the entire db 'as and when required' by simply running a single TI process (though only one view can be initialized in one command like ('cube1', 'view1'), but can include one more line for another view as this is far better job than manually clearing out)

Thanks
Reddy

[Steve Vincent]

You're welcome I don't clear lots of cubes at once but i do use that code a lot, so it was an easy suggestion.

[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

[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]

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

-Reddy

[Alan Kirk]

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.)

[Wim Gielis]

Interesting Lotsaram, thanks for the information.

Perhaps the end result could be moved to a topic in the "Useful code, tips and tricks" subforum?

Wim

[Steve Vincent]

Interesting Lotsaram, thanks for the information.

Perhaps the end result could be moved to a topic in the "Useful code, tips and tricks" subforum?

Wim

Done - i've taken the relevant posts and copied them in to a new topic

[lotsaram]

My intention wasn't to improve on Wim's code, only to illustrate that it wasn't necessary to assign subset ALL to zero out. I wholeheartedly agree with Alan's suggestion to ensure that cube logging is off before doing the zero outs. Not only will this prevent potentially gigabytes of unnecessary log files being written, but this will also significantly improve performance by several fold. Not looping through each cube's dimensions and assigning a subset might shave a millisecond off each cube but removing the logging could save minutes off the zero out for each cube (depending on cubesize/data volume of course.)

[Reddy]

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.

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;

Hi Wim & Lotsaram,
Does this code also vanishes rule editor of the cubes as to my experience after running TI process (Cool, its testing database) with above codes have vanished all rule applied cells (i.e. cells are no more rule calculated but data entry). Can we just zero out the cubes without vanishing the existing rules?

[Wim Gielis]

Reddy

The rules themselves do not vanish.

According to me, what happens is that the code clears cells, that are input cells to other cells calculated with rules. Hence, the rules could return 0, or the cells are not fed.

Wim

[Reddy]

Reddy

The rules themselves do not vanish.

According to me, what happens is that the code clears cells, that are input cells to other cells calculated with rules. Hence, the rules could return 0, or the cells are not fed.

Wim

Hi Wim

Before executing the TI process, I see the element values are rule calculated but after running the TI process, same rule written element has become a data entry field. However, I will confirm with you after checking the content in rules editor. Now, I have some problem in opening rules editor (as posted today).

I will let you know as soon as I validate it.

[Steve Vincent]

It can't calculate what it doesn't have

A + B = C. If A and B are both zero, TM1 will ignore the rule because it knows it has nothing to do. If you put data back in to A or B then it should look and behave like normal again

[Reddy]

It can't calculate what it doesn't have

A + B = C. If A and B are both zero, TM1 will ignore the rule because it knows it has nothing to do. If you put data back in to A or B then it should look and behave like normal again

Ohhh !
Steve there you are its workin now. Thanx (Steve & Wim)

[par3]

Great stuff Guys!

I just want to know if there is another way of clearing a cube than ViewZeroOut. I'm using it at the moment but it is very slow, I can't wait that long! I don't know if it's only me but maybe you guys could try it. Using the Standard demo clear out the "Profit-and_loss" cube. Create a view for only 2008, budget and Local currency and all other dimensions on n level elements with zero's suppressed.

Thanx!

[Martin Ryan]

There are other ways, but they'd all be much slower than ViewZeroOut, this is optimised to do this. Couple of ideas to speed it up: turn off the logging; remove the rule before zeroing out then reinstate the rule; get a gruntier server.

Martin

[par3]

I think I figured out a quiker way. I still used the same view but instead of the Viewzeroout command you can use the Spreading function in TI and just spread a 0. Works much faster with same result!

[Wim Gielis]

I think I figured out a quiker way. I still used the same view but instead of the Viewzeroout command you can use the Spreading function in TI and just spread a 0. Works much faster with same result!

Hi

That sounds interesting, never used that in a TM1 process. Do you have a bit of sample code at hand?

Wim