A Second Way: Using the DIMNM - DIMIX Idiom

TM1® dimension handling functions allow you to write much more powerful, and easy-to-maintain rules than those in the example.

A pair of functions, DIMIX and DIMNM, when used in combination, allow you to move forwards and backwards along dimensions.

The DIMIX function returns a number corresponding to the position, or index, of a given element in a dimension. The DIMNM function is its inverse, returning a dimension element given the index.

For example, DIMIX ('Date', 'Nov -17') returns the index of the element Nov -17 in the Date dimension, which happens to be 322. Correspondingly, DIMNM ('Date', 322) returns Nov -17, the 322nd element of the Date dimension.

The real power of these functions, though, is when they are combined.

DIMNM('Date',DIMIX('Date', 'Nov -17')) uses the DIMIX function to retrieve the index of Nov -17, then passes that index to the DIMNM function to retrieve the element with that index. Thus, it returns what it started with, Nov -17.

DIMNM('Date',DIMIX('Date', 'Nov -17') -1) retrieves the index of Nov -17 (322), subtracts 1 from it to get 321, and returns the element with that index. It therefore returns Nov -16, the element immediately preceding Nov -17.

Similarly, DIMNM('Date',DIMIX('Date', 'Nov -17') +1) returns Nov -18, the element immediately following Nov -17.

Perhaps most useful, though, a DB function containing the expression DIMNM('Date',DIMIX('Date', !Date) - 1) will "step back" through every element of the Date dimension and relative to each date element, retrieve the previous date.

Using this construction, you can replace the ten month-specific statements in the Plan rule that calculate future planned production quantities with one (somewhat more complex) statement.

['January','Planned Production Qty - Kgs']=DB(Production',!CakeType,!Month,'Quarterly
Produced - Kgs');
['February','Planned Production Qty - Kgs']=DB(Production',!CakeType,!Month,'Quarterly
Produced - Kgs');
['Planned Production Qty - Kgs']=DB('Plan',!CakeType,DIMNM('Month',
(DIMIX('Month',!Month)-1)),['Planned Production Qty - Kgs'] * 
['Adj. % from last month'];

The single calculation statement

['Planned Production Qty - Kgs']=DB('Plan',!CakeType,DIMNM('Month',
(DIMIX('Month',!Month)-1)),['Planned Production
Qty - Kgs'] * ['Adj. % from last month'];

accurately retrieves Planned Production Qty - Kgs values for the months March through December. This statement uses a DB formula to refer to the current cube. (As you may recall, the DB formula is much more flexible than bracket notation. In this case, a DIMNM function returns the argument for the Month dimension to the DB formula. Such formulas cannot be used in bracket notation.)

To better understand how this statement works, consider what happens when the server receives a request for the value of Planned Production Qty - Kgs of Salty Cake in the month of March:

Procedure

  1. The server evaluates !Month to March, the month that is being requested.
  2. DIMIX('Month', 'March') evaluates to 3, the index value of March in the Month dimension.
  3. 3-1 evaluates to 2.
  4. DIMNM('Month',2) evaluates to February.
  5. !CakeType evaluates to Salty Cake, the caketype of the value that is being requested.
  6. DB('Plan', 'Salty Cake', 'February', 'Planned Production Qty - Kgs') evaluates to 170.00.
  7. ['Adj. % from last month'] for Salty Cake and March evaluates to 96%. (Remember, the server is processing a request for a value in March.)
  8. The server multiplies the value of 170.00 by 96% to return 163.20, the Planned Production Qty - Kgs of Salty Cake in the month of March.

    This new statement has the benefit of replacing ten statements with one. However, the rule for the Plan cube still includes two month-specific statements that pull values from the Production cube, and a new month-specific statement will have to be added to the rule each month to pull the latest actual values into the Plan cube.

    Ideally, the rule for the Plan cube should use a single statement to retrieve values for all months, and it should not be necessary to edit the rule from month-to-month.