Selective Clear Cube based on Variable Source Data

Yesterday 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. Then, how was I to do that when the source is not a complete data set for all history?

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 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 TI

Prolog

This sets up the temporary dimension by deleting it if it exists, then create it again.

DimName = 'zTemp Motor Vehicle Month';
 
# Delete the Temp dimension if it exists and recreate it
IF (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.

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.

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.