Data-driven zero out procedures over more than 1 dimension
- Dec. 20, 2013
Introduction
In this blog article, I would like to expand on the previous article on data-driven zero out procedures, that you can read here. Now I would like to discuss the Turbo Integrator coding when we need to erase data over multiple dimensions, when we do not know upfront what data to expect and thus, to clear in the cube.
To continue with the example cube in that latest article, we will now add a new dimension called CostType as the first dimension of the cube. The 'CostCenter data' cube will now consist of dimensions CostType, CostCenter and CostCenter_measures. The complexity is this: if you receive data on CostType X and CostCenter Y, you cannot simply clear the data on CostType X (whatever the CostCenter may be), nor on CostCenter Y (whatever the CostType may be). The combination (X, Y) matters and should be cleared.
How can we do this?
In the Prolog tab of the process, create a temporary dimension, and set a counter to 0:
DimensionDestroy('my_tempdim');
DimensionCreate('my_tempdim');
vLineCounter = 0;
In the Metadata tab of the process, track what unique combinations of cost types and cost centers show data. A trick that I can give to you is, work with dimension indexes, rather than with dimension element names or alias values that show up in the data source.
If(Dtype('CostCenter', vCostCenter) @<> 'N');
ItemSkip;
EndIf;
vElement = NumberToString(Dimix('CostType', vCostType)) | '|' | NumberToString(Dimix('CostCenter', vCostCenter));
DimensionElementInsert('my_tempdim', '', vElement, 'N');
In the Data tab of the process, do your zero out. Make sure you only zero out once! You will need to do as many zero out statements as you have unique combinations of CostType and CostCenter (i.e. the number of elements in your temporary dimension). We will loop over that dimension.
vLineCounter = vLineCounter + 1;
If(vLineCounter = 1);
m = 1;
While(m <= Dimsiz('my_tempdim'));
vElement = Dimnm('my_tempdim', m);
vScan = Scan('|', vElement);
vCostType_Element = Dimnm('CostType', StringToNumber(Subst(vElement, 1, vScan-1)));
vCostCenter_Element = Dimnm('CostCenter', StringToNumber(Subst(vElement, vScan+1, Long(vElement))));
## Destroy View & Subsets
ViewDestroy(vCubeName, vViewName);
SubsetDestroy('CostType', 'tmp');
SubsetDestroy('CostCenter', 'tmp');
SubsetDestroy('CostCenter_measures', 'tmp');
## Create View & Subsets
ViewCreate('CostCenter data', 'tmp');
SubsetCreate('CostType', 'tmp');
SubsetElementInsert('CostType', 'tmp', vCostType_Element, 1);
SubsetCreate('CostCenter', 'tmp');
SubsetElementInsert('CostCenter', 'tmp', vCostCenter_Element, 1);
SubsetCreateByMDX('tmp', '{TM1SORT( TM1FILTERBYLEVEL( TM1SUBSETALL( [CostCenter_measures] ), 0), ASC)}');
## Assign Subsets to the View
ViewSubsetAssign('CostCenter data', 'tmp', 'CostType', 'tmp');
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('CostType', 'tmp');
SubsetDestroy('CostCenter', 'tmp');
SubsetDestroy('CostCenter_measures', 'tmp');
m = m + 1;
End;
EndIf;
If(Dtype('CostCenter', vCostCenter) @<> 'N');
ItemSkip;
EndIf;
AFTER this zero out code in the Data tab, you can have your usual CellIncrement / CellPutN / CellPutS / AttrPutS / … statements.
In the Epilog tab of the process, you can clean up the temporary dimension again.
DimensionDestroy('my_tempdim');
That’s all. Of course you can go further, zeroing out over 3 dimensions for example (like combinations year, month, scenario). In that case, you will want to add different element indexes within your concatenated unique elements in the temporary dimension. The last thing to add is that the zero out code could be more optimised. For example, destroying and creating subsets should be done in the Prolog tab, but certainly outside the Data tab. The codes are one-off statements, so you will benefit if these statements occur in the Prolog and/or Epilog tab of a TI process. The code could then look like:
######################################## PROLOG TAB:#######################################DimensionDestroy('my_tempdim'); DimensionCreate('my_tempdim'); ## Destroy View & Subsets ViewDestroy(vCubeName, vViewName); SubsetDestroy('CostType', 'tmp'); SubsetDestroy('CostCenter', 'tmp'); SubsetDestroy('CostCenter_measures', 'tmp'); ## Create View & Subsets ViewCreate('CostCenter data', 'tmp'); SubsetCreate('CostType', 'tmp'); SubsetCreate('CostCenter', 'tmp'); SubsetCreateByMDX('tmp', '{TM1SORT( TM1FILTERBYLEVEL( TM1SUBSETALL( [CostCenter_measures] ), 0), ASC)}'); ## Assign Subsets to the View ViewSubsetAssign('CostCenter data', 'tmp', 'CostType', 'tmp'); ViewSubsetAssign('CostCenter data', 'tmp', 'CostCenter', 'tmp'); ViewSubsetAssign('CostCenter data', 'tmp', 'CostCenter_measures', 'tmp'); vLineCounter = 0;######################################## METADATA TAB:#######################################If(Dtype('CostCenter', vCostCenter) @<> 'N'); ItemSkip; EndIf; vElement = NumberToString(Dimix('CostType', vCostType)) | '|' | NumberToString(Dimix('CostCenter', vCostCenter)); DimensionElementInsert('my_tempdim', '', vElement, 'N');######################################## DATA TAB:#######################################vLineCounter = vLineCounter + 1; If(vLineCounter = 1); m = 1; While(m <= Dimsiz('my_tempdim')); vElement = Dimnm('my_tempdim', m); vScan = Scan('|', vElement); vCostType_Element = Dimnm('CostType', StringToNumber(Subst(vElement, 1, vScan-1))); vCostCenter_Element = Dimnm('CostCenter', StringToNumber(Subst(vElement, vScan+1, Long(vElement)))); SubsetElementInsert('CostType', 'tmp', vCostType_Element, 1); SubsetElementInsert('CostCenter', 'tmp', vCostCenter_Element, 1); ## Zero Out the View ViewZeroOut('CostCenter data', 'tmp'); SubsetElementDelete('CostType', 'tmp', 1); SubsetElementDelete('CostCenter', 'tmp', 1); m = m + 1; End; EndIf; If(Dtype('CostCenter', vCostCenter) @<> 'N'); ItemSkip; EndIf;######################################## EPILOG TAB:######################################## Destroy View & Subsets ViewDestroy('CostCenter data', 'tmp'); SubsetDestroy('CostType', 'tmp'); SubsetDestroy('CostCenter', 'tmp'); SubsetDestroy('CostCenter_measures', 'tmp'); DimensionDestroy('my_tempdim');
Enjoy the holidays and the festivities ! :-)
