Once I needed to load some motor vehicle fleet data from a csv file. I thought it had unique records for each measure, month and car. Easy, I thought. I’ll just use a CellPutN so it overwrites each month’s value with the new value on reload. Then I found that there were a few examples where there were debits and credits on separate lines in the source. So the answer was then CellIncrementN. But to make that work, I needed to zap the existing data, or it would just keep on accumulating. I needed to do a selective clear of data – but only for the months of data in the source file.
We at Infocube have a standard TI that we use to clear out a view of all data from a cube. It uses variables on dimensions where a selection is required to be cleared. So, for example, let’s say you have a time dimension with 10 years of months and only want to clear 6 months. You would choose those 6 months in your view and they would be the only ones zapped. It was fairly obvious I needed to use this Clear View TI to clear out the required periods. But which periods should I clear?
Options
I wanted to use this standard TI, but how would I know what periods to clear? One answer could have been to prompt the user for the range of periods. But that leaves it open to user error and doesn’t allow for non-contiguous periods. It also doesn’t allow for random elements that need to be cleared, when you don’t want all elements zapped.
I spoke with Pfaf and he recommended doing a selective clear by creating a temporary dimension with all the periods in the source data listed. Then use that temporary dimension in a loop in the primary TI’s Prolog to run our Clear View TI for each element in the temporary dimension. Sound complicated, yeah? Well, no. It was actually really simple.
Solution – a Selective Clear based on the Source data
We went with the second option above – create a temporary dimension and use that in a loop to clear a view using each element. Here’s what we did in detail:
Create Temporary Dim for Selective Clear via a TI
Prolog
This sets up the temporary dimension with the elements to subsequently be selectively cleared. It deletes the temporary dim if it exists, then creates it again.
DimName = 'zTemp Motor Vehicle Month';# Delete the Temp dimension if it exists and recreate itIF (DimensionExists ( DimName ) =1); DimensionDestroy ( DimName );ENDIF;DimensionCreate ( DimName);Metadata
This does a bit of manipulation of the source data and then adds an element for each Invoice Month from the source. In essence this creates the dim of elements that will be zapped by the selective clear.
InvoiceMonth = SUBST ( InvoiceMonth , 1, 3 ) | '-' | SUBST ( InvoiceMonth , 6, 2 );DimensionElementInsert ( DimName, '' , InvoiceMonth , 'n' );CubeLoad TI
Prolog
On the Prolog we create a loop that goes through each element of the temporary dim and clears the data using our zInfocube.CubeViewBuild TI.
nCount = 1;nElements = DIMSIZ( TempDimName );WHILE (nCount <= nElements ); sMonthYear = DimNM ( TempDimName , nCount ); ExecuteProcess ( pProcess , 'pCubeName' , CubeName, 'pViewName' , pViewName , 'pAction' , pAction , 'pDestroy' , pDestroy, 'pDim1' , 'Month-Year' , 'pDimElement1' , sMonthYear ); nCount = nCount + 1;END;The clever part is in the looping definition of sMonthYear, using the value in nCount to go get the next element in the temporary dimension and use that as the source for the View Clear TI.
Extending the Method
We have illustrated the method here for doing a selective clear with a single dimension. However it would be fairly easy to extend the idea to handle multiple dimensions in the one clear out process.
Pfaf reckons that if you were to create a single temporary dimension with a concatenation of the contents of two or more elements from the source. Then dissect the concatenated element into the required target elements when doing the clear. For example, let’s say we have Sep-19, Oct-19 in period and Australia in country. If we concantenated this to Sep-19zzzzAustralia and Oct-19zzzzAustralia in the temporary dimension, we could then dissect those with a SUBST in the clear out process to derive just the elements to be zapped.
