Zero-out data in TM1 cube

[ravi]

Hi All,

Please advise me the procedure to zero-out the data in TM1 Cube.

Regards,
Ravi

[Alan Kirk]

Please advise me the procedure to zero-out the data in TM1 Cube.

Option 1: Create a view of the data at N level. Type a zero into every cell.
Option 2: Create a view at the highest consolidation levels for the data that you want to clear. Consult the section of the User Guide manual relating to Data Spreading. Specifically, look for the Clear command. Be aware that if you're clearing a very large number of values in this fashion from a cube that has logging enabled, you may need a forklift to move your transaction log file.
Option 3: Consult the TurboIntegrator manual for the functions ViewZeroOut, as well as CubeSetLogChanges to temporarily turn off logging if you need to.

[craig_karr]

If you go with option three, is it so that you have to create a view that actually displays all the values on N level that you want to zero out? I have tried to do that but the view gets way to big when I nest several large dimensions so TM1 crashes. In that case is my only option to create several views and then write a turbointegrator process to zero out each of them?

I guess there is no way that you can zero out a view with consolidated elements with the turbointegrator option?

[Alan Kirk]

If you go with option three, is it so that you have to create a view that actually displays all the values on N level that you want to zero out? I have tried to do that but the view gets way to big when I nest several large dimensions so TM1 crashes. In that case is my only option to create several views and then write a turbointegrator process to zero out each of them?

I guess there is no way that you can zero out a view with consolidated elements with the turbointegrator option?

Look for ViewExtractSkipCalcsSet (and its related function, ViewExtractSkipRuleValuesSet) in the TM1 TurboIntegrator Functions section of the TM1 Reference Guide.

[jim wood]

There is another option. Export what you need to clear to a flat file and create a TI process that points to that flat file. The advantage of doing it this way is that you have a backup that you can archive or restore quickly. You can combine this in a Chore with a process that first exports a view using ASCIIOutput. Again as Alan said consult your manual (or online help) to find out more about this function.

[sachin]

I am currently using these 2 lines in rules to zero-out data -

[] = S: ('');
[] = N: 0;

Whenever I have to zero out data, I add these 2 lines to the beginning of my rules code (just after feedstrings and skipcheck). We are working with strings in our cube and hence the 1st line of the rule.

I did not see any one recommending this (using the rule approach)! I am new to TM1 and sometimes don't understand the pitfalls of my approach; is this approach undesirable? Please share your thoughts. Thanks.

- Sachin

[Michel Zijlema]

I am currently using these 2 lines in rules to zero-out data -

[] = S: ('');
[] = N: 0;

Whenever I have to zero out data, I add these 2 lines to the beginning of my rules code (just after feedstrings and skipcheck). We are working with strings in our cube and hence the 1st line of the rule.

I did not see any one recommending this (using the rule approach)! I am new to TM1 and sometimes don't understand the pitfalls of my approach; is this approach undesirable? Please share your thoughts. Thanks.

- Sachin

Hi Sachin,

I wouldn't recommend what you're doing. For once because (I think depending on the actual TM1 version) the data will not be cleared this way - when you remove the rules the original numbers will be back.
And second (in case your approach would work) I would think that running a TI process is easier / less work than changing two rules, save the rulessheet, changing the rules back and save the rulessheet...

Michel

[jstrygner]

I usually zero-out data in a view to make TI write to it new values, that is why I would not think of zeroing values via rule.

[SBrenner]

Thanks to all, it works!
These few lines made it possible.

Code: Select all

CubeSetLogChanges('WekoMCP', 0);
ViewExtractSkipCalcsSet ('WekoMCP', 'AllBillData', 1);
ViewZeroOut('MCPWeko', 'AllBillData');
CubeSetLogChanges('WekoMCP', 1);

Greetings

Steve

[Atif.Hameed]

Great post. Great Info. Thanks All

[ajain86]

I have found it best to use a TI process to clear out my data. I like to create a dynamic view with dynamic subsets and run the clear out from there. This allows me to clear specific sections of the data without having to manually create views everytime. It also allows me to clear data for any cube on the server all with 1 process. I can also just copy the exact process onto another server.

Here is the process:
There is no data source for the process.
There are several parameters (all Type of String, all have a relevant question):
pCube, pDim1, pVal1, pDim2, pVal2, pDim3, pVal3... pDim10, pVal10.
You can add more dimension and value parameters if you need. I have found 10 to be sufficient for me. Adding additional parameters would also require you to add a section for those parameters on the prolog tab.

You do not need to enter a dimension and a subsequent value if you want to clear data for all members from that dimension. These values are only required where you want to limit your clear.

Prolog -

Code: Select all

#=========================================================
# Declare variables
#=========================================================
sView = 'zero_out';
sSub = 'zero_out';

#=========================================================
# Trim Input Values
#=========================================================
pCube = TRIM( pCube );

pDim1 = TRIM( pDim1 );
pDim2 = TRIM( pDim2 );
pDim3 = TRIM( pDim3 );
pDim4 = TRIM( pDim4 );
pDim5 = TRIM( pDim5 );
pDim6 = TRIM( pDim6 );
pDim7 = TRIM( pDim7 );
pDim8 = TRIM( pDim8 );
pDim9 = TRIM( pDim9 );
pDim10 = TRIM( pDim10 );

pVal1 = TRIM( pVal1 );
pVal2 = TRIM( pVal2 );
pVal3 = TRIM( pVal3 );
pVal4 = TRIM( pVal4 );
pVal5 = TRIM( pVal5 );
pVal6 = TRIM( pVal6 );
pVal7 = TRIM( pVal7 );
pVal8 = TRIM( pVal8 );
pVal9 = TRIM( pVal9 );
pVal10 = TRIM( pVal10 );

#=========================================================
# Check if cube exits
#=========================================================
If( DIMIX( '}Cubes', pCube ) = 0 );
     ASCIIOutput( sLog, 'Cube '| pCube |' does not exist on the server.' );
     ProcessError;
endif;

#=========================================================
# Verify dimensions are part for the cube specified. 
#=========================================================
nChkCount = 0;
nDimCount = 0;

If( pDim1 @<> '' ); nDimCount = nDimCount + 1; endif;
If( pDim2 @<> '' ); nDimCount = nDimCount + 1; endif;
If( pDim3 @<> '' ); nDimCount = nDimCount + 1; endif;
If( pDim4 @<> '' ); nDimCount = nDimCount + 1; endif;
If( pDim5 @<> '' ); nDimCount = nDimCount + 1; endif;
If( pDim6 @<> '' ); nDimCount = nDimCount + 1; endif;
If( pDim7 @<> '' ); nDimCount = nDimCount + 1; endif;
If( pDim8 @<> '' ); nDimCount = nDimCount + 1; endif;
If( pDim9 @<> '' ); nDimCount = nDimCount + 1; endif;
If( pDim10 @<> '' ); nDimCount = nDimCount + 1; endif;

nChkDim = 1;
sChkDim = TABDIM( pCube, nChkDim );
While( sChkDim @<> '' );
     If( pDim1 @= sChkDim ); nChkCount = nChkCount + 1;
     elseif( pDim2 @= sChkDim ); nChkCount = nChkCount + 1;
     elseif( pDim3 @= sChkDim ); nChkCount = nChkCount + 1;
     elseif( pDim4 @= sChkDim ); nChkCount = nChkCount + 1;
     elseif( pDim5 @= sChkDim ); nChkCount = nChkCount + 1;
     elseif( pDim6 @= sChkDim ); nChkCount = nChkCount + 1;
     elseif( pDim7 @= sChkDim ); nChkCount = nChkCount + 1;
     elseif( pDim8 @= sChkDim ); nChkCount = nChkCount + 1;
     elseif( pDim9 @= sChkDim ); nChkCount = nChkCount + 1;
     elseif( pDim10 @= sChkDim ); nChkCount = nChkCount + 1; endif;
     nChkDim = nChkDim + 1;
     sChkDim = TABDIM( pCube, nChkDim );
END;

If( nDimCount <> nChkCount );
     ASCIIOutput( sLog, 'You have entered a value for '| NumberToString( nDimCount ) |' of the dimension parameters.' );
     ASCIIOutput( sLog, 'Only '| NumberToString( nChkCount ) |' out of the '| NumberToString( nDimCount ) |' dimension(s) exist(s) in the '| pCube |' cube.' );
     ProcessError;
endif;

#=========================================================
# Check if View exists and destroy it if it does
# Create View
#=========================================================
If( ViewExists( pCube, sView ) = 1 );
ViewDestroy( pCube, sView );
endif;

ViewCreate( pCube, sView );

#=========================================================
# Check if dimension exists on the server, 
# Destroy subset,
# Create if member exists in the dimension, 
# A null value for member name will create an all subset. 
# Create subset.
# Any incorrect value will cause process to quit. 
#=========================================================
If( DimensionExists( pDim1 ) = 1 );
     If( SubsetExists( pDim1, sSub ) = 1 );
          SubsetDestroy( pDim1, sSub );
     endif;
     If( DIMIX( pDim1, pVal1 ) > 0 );
          SubsetCreateByMDX( sSub, '{TM1DRILLDOWNMEMBER( {['| pDim1 |'].['| pVal1 |']}, ALL, RECURSIVE )}' );
          ViewSubsetAssign( pCube, sView, pDim1, sSub );
     elseif( pVal1 @= '' );
          SubsetCreateByMDX( sSub, '{TM1SUBSETALL( ['| pDim1 |'] )}' );
          ViewSubsetAssign( pCube, sView, pDim1, sSub );
     else;
          ASCIIOutput( sLog, 'The member '| pVal1 |' does not exist in dimension '| pDim1 |'.' );
          ProcessError;
     endif;
elseif( pDim1 @= '' );
elseif( DimensionExists( pDim1 ) = 0 );
     ASCIIOutput( sLog, 'Dimension '| pDim1 |' does not exist on the server.' );
     ProcessError;
endif;

If( DimensionExists( pDim2 ) = 1 );
     If( SubsetExists( pDim2, sSub ) = 1 );
          SubsetDestroy( pDim2, sSub );
     endif;
     If( DIMIX( pDim2, pVal2 ) > 0 );
          SubsetCreateByMDX( sSub, '{TM1DRILLDOWNMEMBER( {['| pDim2 |'].['| pVal2 |']}, ALL, RECURSIVE )}' );
          ViewSubsetAssign( pCube, sView, pDim2, sSub );
     elseif( pVal2 @= '' );
          SubsetCreateByMDX( sSub, '{TM1SUBSETALL( ['| pDim2 |'] )}' );
          ViewSubsetAssign( pCube, sView, pDim2, sSub );
     else;
          ASCIIOutput( sLog, 'The member '| pVal2 |' does not exist in dimension '| pDim2 |'.' );
          ProcessError;
     endif;
elseif( pDim2 @= '' );
elseif( DimensionExists( pDim2 ) = 0 );
     ASCIIOutput( sLog, 'Dimension '| pDim2 |' does not exist on the server.' );
     ProcessError;
endif;

If( DimensionExists( pDim3 ) = 1 );
     If( SubsetExists( pDim3, sSub ) = 1 );
          SubsetDestroy( pDim3, sSub );
     endif;
     If( DIMIX( pDim3, pVal3 ) > 0 );
          SubsetCreateByMDX( sSub, '{TM1DRILLDOWNMEMBER( {['| pDim3 |'].['| pVal3 |']}, ALL, RECURSIVE )}' );
          ViewSubsetAssign( pCube, sView, pDim3, sSub );
     elseif( pVal3 @= '' );
          SubsetCreateByMDX( sSub, '{TM1SUBSETALL( ['| pDim3 |'] )}' );
          ViewSubsetAssign( pCube, sView, pDim3, sSub );
     else;
          ASCIIOutput( sLog, 'The member '| pVal3 |' does not exist in dimension '| pDim3 |'.' );
          ProcessError;
     endif;
elseif( pDim3 @= '' );
elseif( DimensionExists( pDim3 ) = 0 );
     ASCIIOutput( sLog, 'Dimension '| pDim3 |' does not exist on the server.' );
     ProcessError;
endif;

If( DimensionExists( pDim4 ) = 1 );
     If( SubsetExists( pDim4, sSub ) = 1 );
          SubsetDestroy( pDim4, sSub );
     endif;
     If( DIMIX( pDim4, pVal4 ) > 0 );
          SubsetCreateByMDX( sSub, '{TM1DRILLDOWNMEMBER( {['| pDim4 |'].['| pVal4 |']}, ALL, RECURSIVE )}' );
          ViewSubsetAssign( pCube, sView, pDim4, sSub );
     elseif( pVal4 @= '' );
          SubsetCreateByMDX( sSub, '{TM1SUBSETALL( ['| pDim4 |'] )}' );
          ViewSubsetAssign( pCube, sView, pDim4, sSub );
     else;
          ASCIIOutput( sLog, 'The member '| pVal4 |' does not exist in dimension '| pDim4 |'.' );
          ProcessError;
     endif;
elseif( pDim4 @= '' );
elseif( DimensionExists( pDim4) = 0 );
     ASCIIOutput( sLog, 'Dimension '| pDim4 |' does not exist on the server.' );
     ProcessError;
endif;

If( DimensionExists( pDim5 ) = 1 );
     If( SubsetExists( pDim5, sSub ) = 1 );
          SubsetDestroy( pDim5, sSub );
     endif;
     If( DIMIX( pDim5, pVal5 ) > 0 );
          SubsetCreateByMDX( sSub, '{TM1DRILLDOWNMEMBER( {['| pDim5 |'].['| pVal5 |']}, ALL, RECURSIVE )}' );
          ViewSubsetAssign( pCube, sView, pDim5, sSub );
     elseif( pVal5 @= '' );
          SubsetCreateByMDX( sSub, '{TM1SUBSETALL( ['| pDim5 |'] )}' );
          ViewSubsetAssign( pCube, sView, pDim5, sSub );
     else;
          ASCIIOutput( sLog, 'The member '| pVal5 |' does not exist in dimension '| pDim5 |'.' );
          ProcessError;
     endif;
elseif( pDim5 @= '' );
elseif( DimensionExists( pDim5 ) = 0 );
     ASCIIOutput( sLog, 'Dimension '| pDim5 |' does not exist on the server.' );
     ProcessError;
endif;

If( DimensionExists( pDim6 ) = 1 );
     If( SubsetExists( pDim6, sSub ) = 1 );
          SubsetDestroy( pDim6, sSub );
     endif;
     If( DIMIX( pDim6, pVal6 ) > 0 );
          SubsetCreateByMDX( sSub, '{TM1DRILLDOWNMEMBER( {['| pDim6 |'].['| pVal6 |']}, ALL, RECURSIVE )}' );
          ViewSubsetAssign( pCube, sView, pDim6, sSub );
     elseif( pVal6 @= '' );
          SubsetCreateByMDX( sSub, '{TM1SUBSETALL( ['| pDim6 |'] )}' );
          ViewSubsetAssign( pCube, sView, pDim6, sSub );
     else;
          ASCIIOutput( sLog, 'The member '| pVal6 |' does not exist in dimension '| pDim6 |'.' );
          ProcessError;
     endif;
elseif( pDim6 @= '' );
elseif( DimensionExists( pDim6 ) = 0 );
     ASCIIOutput( sLog, 'Dimension '| pDim6 |' does not exist on the server.' );
     ProcessError;
endif;

If( DimensionExists( pDim7 ) = 1 );
     If( SubsetExists( pDim7, sSub ) = 1 );
          SubsetDestroy( pDim7, sSub );
     endif;
     If( DIMIX( pDim7, pVal7 ) > 0 );
          SubsetCreateByMDX( sSub, '{TM1DRILLDOWNMEMBER( {['| pDim7 |'].['| pVal7 |']}, ALL, RECURSIVE )}' );
          ViewSubsetAssign( pCube, sView, pDim7, sSub );
     elseif( pVal7 @= '' );
          SubsetCreateByMDX( sSub, '{TM1SUBSETALL( ['| pDim7 |'] )}' );
          ViewSubsetAssign( pCube, sView, pDim7, sSub );
     else;
          ASCIIOutput( sLog, 'The member '| pVal7 |' does not exist in dimension '| pDim7 |'.' );
          ProcessError;
     endif;
elseif( pDim7 @= '' );
elseif( DimensionExists( pDim7 ) = 0 );
     ASCIIOutput( sLog, 'Dimension '| pDim7 |' does not exist on the server.' );
     ProcessError;
endif;

If( DimensionExists( pDim8 ) = 1 );
     If( SubsetExists( pDim8, sSub ) = 1 );
          SubsetDestroy( pDim8, sSub );
     endif;
     If( DIMIX( pDim8, pVal8 ) > 0 );
          SubsetCreateByMDX( sSub, '{TM1DRILLDOWNMEMBER( {['| pDim8 |'].['| pVal8 |']}, ALL, RECURSIVE )}' );
          ViewSubsetAssign( pCube, sView, pDim8, sSub );
     elseif( pVal8 @= '' );
          SubsetCreateByMDX( sSub, '{TM1SUBSETALL( ['| pDim8 |'] )}' );
          ViewSubsetAssign( pCube, sView, pDim8, sSub );
     else;
          ASCIIOutput( sLog, 'The member '| pVal8 |' does not exist in dimension '| pDim8 |'.' );
          ProcessError;
     endif;
elseif( pDim8 @= '' );
elseif( DimensionExists( pDim8 ) = 0 );
     ASCIIOutput( sLog, 'Dimension '| pDim8 |' does not exist on the server.' );
     ProcessError;
endif;

If( DimensionExists( pDim9 ) = 1 );
     If( SubsetExists( pDim9, sSub ) = 1 );
          SubsetDestroy( pDim9, sSub );
     endif;
     If( DIMIX( pDim9, pVal9 ) > 0 );
          SubsetCreateByMDX( sSub, '{TM1DRILLDOWNMEMBER( {['| pDim9 |'].['| pVal9 |']}, ALL, RECURSIVE )}' );
          ViewSubsetAssign( pCube, sView, pDim9, sSub );
     elseif( pVal9 @= '' );
          SubsetCreateByMDX( sSub, '{TM1SUBSETALL( ['| pDim9 |'] )}' );
          ViewSubsetAssign( pCube, sView, pDim9, sSub );
     else;
          ASCIIOutput( sLog, 'The member '| pVal9 |' does not exist in dimension '| pDim9 |'.' );
          ProcessError;
     endif;
elseif( pDim9 @= '' );
elseif( DimensionExists( pDim9 ) = 0 );
     ASCIIOutput( sLog, 'Dimension '| pDim9 |' does not exist on the server.' );
     ProcessError;
endif;

If( DimensionExists( pDim10 ) = 1 );
     If( SubsetExists( pDim10, sSub ) = 1 );
          SubsetDestroy( pDim10, sSub );
     endif;
     If( DIMIX( pDim10, pVal10 ) > 0 );
          SubsetCreateByMDX( sSub, '{TM1DRILLDOWNMEMBER( {['| pDim10 |'].['| pVal10 |']}, ALL, RECURSIVE )}' );
          ViewSubsetAssign( pCube, sView, pDim10, sSub );
     elseif( pVal10 @= '' );
          SubsetCreateByMDX( sSub, '{TM1SUBSETALL( ['| pDim10 |'] )}' );
          ViewSubsetAssign( pCube, sView, pDim10, sSub );
     else;
          ASCIIOutput( sLog, 'The member '| pVal10 |' does not exist in dimension '| pDim10 |'.' );
          ProcessError;
     endif;
elseif( pDim10 @= '' );
elseif( DimensionExists( pDim10 ) = 0 );
     ASCIIOutput( sLog, 'Dimension '| pDim10 |' does not exist on the server.' );
     ProcessError;
endif;

#=========================================================
# Turn off Cube logging
# Clear View
#=========================================================
CUBESETLOGCHANGES( pCube, 0 );
ViewZeroOut( pCube, sView );
CUBESETLOGCHANGES( pCube, 1 );

Epliog -

Code: Select all

#=========================================================
# Remove dymanic objects
#=========================================================
ViewDestroy( pCube, sView );

If( SubsetExists( pDim1, sSub ) = 1 );
     SubsetDestroy( pDim1, sSub );
endif;
If( SubsetExists( pDim2, sSub ) = 1 );
     SubsetDestroy( pDim2, sSub );
endif;
If( SubsetExists( pDim3, sSub ) = 1 );
     SubsetDestroy( pDim3, sSub );
endif;
If( SubsetExists( pDim4, sSub ) = 1 );
     SubsetDestroy( pDim4, sSub );
endif;
If( SubsetExists( pDim5, sSub ) = 1 );
     SubsetDestroy( pDim5, sSub );
endif;
If( SubsetExists( pDim6, sSub ) = 1 );
     SubsetDestroy( pDim6, sSub );
endif;
If( SubsetExists( pDim7, sSub ) = 1 );
     SubsetDestroy( pDim7, sSub );
endif;
If( SubsetExists( pDim8, sSub ) = 1 );
     SubsetDestroy( pDim8, sSub );
endif;
If( SubsetExists( pDim9, sSub ) = 1 );
     SubsetDestroy( pDim9, sSub );
endif;
If( SubsetExists( pDim10, sSub ) = 1 );
     SubsetDestroy( pDim10, sSub );
endif;

This is a very generic process and should suit everyones need. I do have error checks in the process and output a message to a log if an error does occur. You can remove the ASCIIOutput statements if you do not want them. The do require that you define the variable sLog with the filename of the log file.

[rkaif]

If you want to Zero out the whole cube then the easiest way is to use the CubeClearData() function in a TI Process.

This is the quickest and the easiest way to delete all of the data in the cube. You do not have to create any Views or something.

[sdm]

I have done this...
1. Create a dimension. Include cube names as elements to be zeroed out.
2. create process as below (in prolog)

Code: Select all

Cubecount = DIMSIZ('Dim with cube names');

i = 1;
# loop for all cubes in dim
While (i <= Cubecount);

CubeName = DIMNM ('Dim with cube names', i );

if ( ViewExists (CubeName, 'ZEROOUT') = 1 );
ViewDestroy(CubeName, 'ZEROOUT');
endif;

#create view with all dimesions in cube
ViewCreate(CubeName, 'Zeroout');

#zap data
ViewZeroOut(CubeName, 'Zeroout');

if ( ViewExists (CubeName, 'ZEROOUT') = 1 );
ViewDestroy(CubeName, 'ZEROOUT');
endif;

#next cube in dim
i = i + 1;

end;

[ajain86]

sdm,

If you are clearing out ALL data for a list of cubes with no regard for specific element restrictions, your process could be much simpler if you utilize the CubeClearData() function as rkaif pointed out.

[Martin Ryan]

According to his profile sdm is running 9.4 and this function isn't available in that version (see http://forums.olapforums.com/viewtopic.php?f=3&t=3602)

Martin