wimgielis.com

An alternative way to clear cube data

• Mar. 29, 2011

Introduction

It can sometimes be relatively complex to write a zero out process when it comes to the time component (or in general, situations in which there is an asynchronous view to be zero’ed out). In any case, try to avoid situations where the view zero out processes use views that physically exist in the database - the risk to open such big views inadvertantly is simply too big.

A real customer case

Think about this situation that I recently encountered at the customer. The customer wants to load data for all cube combinations starting at a given date, until today. The cube contains data at the daily level. For instance, a (simple) data load process loads data from March 15, 2011 to today. We definitely need a zero out type of process, to be able to load the data more than once without double counting or leaving numbers in the cube from an earlier process run.

The beginning date is set up as a (string) parameter: 20110315 would be filled in by the user. Hence, we need to clear the data from March 15, 2011 to "today", say March 28, 2011. The cube contains a Years dimension, coupled to a dimension containing 366 lowest-level dates grouped into months.

Year crossings

Hence, when we cross years (loading data from e.g. December 19, 2010 to January 10, 2011) we have an asynchronous selection: zeroing out the days December 19 to December 31 and January 1 to January 10 for the years 2010 and 2011 will be sure to get us into trouble. We remove too many data (Jan. 1, 2010 to Jan 10, 2010 and December 19, 2011 to Dcember 31, 2011 to be precise, although future days could be empty and as such not really a big concern).

2 views…

The solution would be to create 2 views: 1 view containing the combinations for 2010 and 1 view containing the combinations for 2011. Not very handy and in fact too much code. I have a simpler solution: the CellPutProportionalSpread function, heavily used in budgeting cycles.

We just need to make a loop from the starting date (a parameter) to the end date (today). For each day, we test the number in all top consolidations for the other dimensions. If the total is different from 0, initiate a CellPutProportionalSpread whereby you spread a value of 0. Done!

CellPutProportionalSpread

The CellPutProportionalSpread function will only put a 0 in the cells containing data, so this procedure is very fast in TM1. You are not obliged to take consolidated values in the other dimensions, the CellPutProportionalSpread will even work at the lowest level in the cube, so any combination is possible. To ease the process make sure your top consolidations contain all data to be zero'ed out, if not you will potentially miss cube cells. In addition, you can have temporary consolidations that take a number of leaf-level elements (like for example day elements in a cube with years, months, days as separate dimensions).

Sample code

First create a process containing a parameter:

Parameter	Type	Default Value	Prompt Question
pEarliestDay	String	20110315	From what date would you like to remove the cube data? (yyyymmdd)

Then we have the code in the Prolog section of the process:

# Wim Gielis
# https://www.wimgielis.com
#####
# An alternative way to clear cube data
# 03/29/11
#####
# Zero out process to erase numbers in different years

# cells to be cleared:
#  - Years/dates: all days from a certain beginning date
#    to today (parameter: pEarliestDay)
#  - 1 measure
#  - all n-level elements in the other dimensions

# test the user input
If(Long(pEarliestDay)<>8);
   ProcessQuit;
EndIf;

vBeginningYear=Subst(pEarliestDay,1,4);
If(Dimix('Years',vBeginningYear)=0);
   ProcessQuit;
EndIf;

vBeginningDay=Subst(pEarliestDay,5,4);
If(Dimix('Days',vBeginningDay)=0);
   ProcessQuit;
EndIf;

CubeSetLogChanges('MyCube',0);

# we make a loop through the days, from the earliest day to today
vDate=Dayno(vBeginningYear | '-' | Subst(vBeginningDay,1,2) | '-' | Subst(vBeginningDay,3,2));

While(vDate<=Dayno(Today(0)));

   vYear=Timst(vDate,'\Y');
   vMonthDay=Timst(vDate,'\m\d');

   # proportional spread operation
   If(CellGetN('MyCube','dim 1','dim 2','dim 3','…',vYear,vMonthDay,'…','MyMeasure')<>0);
      CellPutProportionalSpread(0,'MyCube','dim 1','dim 2',
            'dim 3','…',vYear,vMonthDay,'…','MyMeasure');
   EndIf;

   vDate=vDate+1;

End;

CubeSetLogChanges('MyCube',1);

That’s it for now! See you next time at my website.

• « Previous TM1 article
• Next TM1 article »