Data-driven zero out procedures
- May 14, 2017
Introduction
Loading data to TM1 cubes very often needs a zeroout procedure badly… to avoid double counting data. You erase/zero a slice of the cube prior to loading the data again. Specifically, in a TI process you use the Advanced > Prolog tab to define and execute the zero out, and the Advanced > Data tab to load the new batch of data. The usual stuff let’s say.
But this method presupposes that you can define the cube slice to be erased. Either hard-coded element references, either the result of the user entering desired parameter values, either information taken from a lookup cube, … countless variations exist. But what happens if you do not (fully) know this information? Then the data will decide upon what to erase!
How can we do this?
Here is an overview of how you can set it up. The takeaway is that you record the combinations to be zeroed out in the Advanced > Metadata tab and do the zeroout once in the Advanced > Data tab. If implemented correctly, this is a winner! Let’s get started. Suppose that you have 1 variable (called vCostCenter) which decides upon what elements in the CostCenter dimension need to be zeroed out.
In the Prolog tab of the process, create a temporary subset, and set a counter to 0:
SubsetDestroy('CostCenter', 'tmp');
SubsetCreate('CostCenter', 'tmp');
vLineCounter = 0;
In the Metadata tab of the process, track what unique cost centers show data. The Metadata tab is convenient since, just as the Data tab, it loops through all of the values in the Data source (cube view, flat file, relational table, …).
If(Dtype('CostCenter', vCostCenter) @= 'N');
If(SubsetElementExists('CostCenter', 'tmp', vCostCenter) = 0);
SubsetElementInsert('CostCenter', 'tmp', vCostCenter, 0);
EndIf;
EndIf;
In the Data tab of the process, do your zero out. Make sure you only zero out once! Assume a cube called 'CostCenter data' having 2 dimensions - CostCenter and CostCenter_measures. Temporary views and subsets will be called 'tmp' for convenience.
vLineCounter = vLineCounter + 1;
If(vLineCounter = 1);
## Destroy View & Subsets
ViewDestroy(vCubeName, vViewName);
SubsetDestroy('CostCenter_measures', 'tmp');
## Create View & Subsets
ViewCreate('CostCenter data', 'tmp');
SubsetCreateByMDX('tmp', '{TM1SORT( TM1FILTERBYLEVEL( TM1SUBSETALL( [CostCenter_measures] ), 0), ASC)}');
## Assign Subsets to the View
ViewSubsetAssign('CostCenter data', 'tmp', 'CostCenter', 'tmp');
ViewSubsetAssign('CostCenter data', 'tmp', 'CostCenter_measures', 'tmp');
## Zero Out the View
ViewZeroOut('CostCenter data', 'tmp');
## Destroy View & Subsets
ViewDestroy('CostCenter data', 'tmp');
SubsetDestroy('CostCenter', 'tmp');
SubsetDestroy('CostCenter_measures', 'tmp');
EndIf;
If(Dtype('CostCenter', vCostCenter) @<> 'N');
ItemSkip;
EndIf;
AFTER this zero out code in the Data tab, you can have your usual CellIncrementN / CellPutS / CellPutS / AttrPutS / … statements.
In the Epilog tab of the process, you can clean up the temporary dimension again.
DimensionDestroy('my_tempdim');
Do not be forget…
… to use the function SubsetElementExists because a subset can contain the same element multiple times. So, if you insert the same element in the Metadata tab 1,000,000 times in a subset, you have a subset containing 1,000,000 elements. Another solution would be to populate a temporary dimension instead of a subset: dimensions can only contain unique elements.
To be continued
That’s all for now. The case of zeroing out over multiple dimensions will be treated in a next blog article.
