LAMBDA/Table Based Multidimensional Model

Part 4 - TBM with LAMBDA in a Multidimensional Implementation

Tables, Dynamic Arrays, and LAMBDAs are awesome together! Let me show you how.

To download the example model, click here. (https://www.dropbox.com/s/2lbp7jtfv722rhn/BXL%20MD-TBM%20LAMBDA.xlsx?dl=1)

If you missed Multidimensional Modeling Part 1 you may want to start there. That version used tables and the Data Model to solve the problem of multidimensional modeling.

Multidimensional Modeling Part 2 explained the formulas used to create indexes over dimensions so they could be combined into a single view.

Multidimensional Modeling Part 3 replaced calculation tables with dynamic arrays.

This version adds a LAMDA() (credit Peter Bartholomew) function to create the indexes.

The Magic of LAMBDA()

The key to making multidimensional modeling work is creating all permutations of indexes over all dimensions and instances. With these indexes, we can pick attributes from various dimensions and perform whatever calculations are needed.

Here is an example of indexes in action. In the table at right we have two dimensions: Sector and Product. There are 4 different Sectors (4 instances) and 2 different Products (2 instances). The indexes are in the first two columns. When the Sector's index changes, the Sector Name changes. When the Product's index changes, the Product Name changes. Attached to each permutation is the distribution percentage which can be unique to each permutation.

In the example model (that I encourage you to download) we have four dimensions; Product, Sector, Region and Month. The model's requirement was to calculate the sales for all products, in each sector, for each region, in every month. Each dimension has a piece of the puzzle. For example: Sectors had price factor; Products had quantities, base price and production costs; Regions had distribution costs; and Months had seasonality demand per sector. And in some cases, the combination of two or more dimensions had unique attributes as shown in the figure above. To determine sales we needed to appropriately combine all dimension attributes for every instance. The indexes made sure we created all permutations so our results would be all inclusive.

To calculate these indexes we used the permutation formula discussed in part 2. It is basically one formula.

=MOD(
     QUOTIENT(<Unit Counter>-1, <Product of Remaining Dimension instances>),
     <Current Dimension's Instance Count> + 1)
        

If you want the full explanation of that formula, read part 2. But what is important in this discussion is: IT IS JUST ONE FORMULA with different values that calculates each and every index. Because it is just one formula we can put it into a LAMBDA, and through the magic of LAMBDA, place our formula in JUST ONE CELL and it will calculate all permutations of all dimensions and their instances. If we add more dimensions and or more instances, the LAMBDA will accept them and change its SPILL region automagically making our models extremely flexible and insanely easy to maintain. Here is the LAMBDA formula:

CrtIdxλ?is the name I gave this LAMBDA. By defining the LAMBDA in Name Manager we can enter the name in our cell instead of the entire formula. Here is an example:

=CrtIdxλ( TRANSPOSE( tblDimensions[Instances] ) )        

As you can see, CrtIdxλ accepts parameters. In the code window (white background) we see a name reserved for the parameters: uBnd (for upper bounds of each dimension) . uBnd must be an horizontal array consisting of each dimension's instance count. In the above snippet (gray background), uBnd is what is enclosed in parentheses, which is the Instances column of a table named: tblDimensions.

The table's values are vertical so we use TRANPOSE() to make it horizontal.

Now let us examine inside CrtIdxλ. After declaring CrtIdxλ's parameter we move on to declaring some temporary variable names using LET(). LET() allows us to break down complex formulas into smaller, more easily understandable chunks and name them. We can create as many named chunks as we like in one LET() function. In CrtIdxλ we define 3 chunks: unitCtr, strideLen, and strideCtr.

unitCtr, SEQUENCE(PRODUCT(uBnd)),        

unitCtr (Unit Counter) is the name for this chunk: SEQUENCE(PRODUCT(uBnd)). PRODUCT() is an Excel function that multiplies all values in uBnd together (4 * 5 * 4 * 12) which is how many permutations are needed to accommodate these dimension instances (960). SEQUENCE() is a new Excel Dynamic Array function which, in this case, generates the numbers 1 through 960. Each number in that sequence identifies the specific permutation we are on.

strideLen, SCAN(1, uBnd, LAMBDA(s, n, s * n)) / uBnd,        

strideLen (stride length) names the second chunk. It uses a new LAMBDA() helper function: SCAN(). SCAN() scans an array and passes the array's values, one by one, to LAMBDA(). It is designed to accumulate values, thus, SCAN()'s first parameter is the initial accumulator, which, in this instance, is set to 1. SCAN()'s second parameter is the array: uBnd. SCAN()'s last parameter is a LAMBDA formula. This LAMBDA has two parameters. s is SCAN()'s first parameter and n is SCAN()'s second parameter. The LAMDA then multiplies these values together producing an array {(1*4), (1*4*5), (1*4*5*4), (1*4*5*4*12)} which equates to {4, 20, 80, 960}. We divide that array by the array uBnd producing {4/4, 20/5, 80/4, 960/12} which equates to {1, 4, 20, 80}.

These numbers determine when the index value in each column changes. The first value is 1 indicating the first column's index will change with every permutation (unitCtr). The second value is 4 indicating the second column's index changes with every 4th permutation. The third column's index changes every 20th permutation and the fourth column changes every 80th permutation.

strideCtr, QUOTIENT(unitCtr - 1, strideLen),        

strideCtr names the formula QUOTIENT(unitCtr - 1, strideLen). QUOTIENT() is an old Excel function that returns the integer portion of a division. It is equivalent to INT((unitCtr - 1)/strideLen).

1 + MOD(strideCtr, uBnd)        

The last parameter of our LET() function is a function that uses the previous named variables to calculate something. In this case it is the end of our permutation formula. If we substitute strideCtr's formula in this statement we get this:

1 + MOD( QUOTIENT(unitCtr - 1, strideLen), uBnd)        

Which looks very much like our permutation formula:

=MOD(
     QUOTIENT(<Unit Counter>-1, <Product of Remaining Dimension instances>),
     <Current Dimension's Instance Count> + 1)        

The result are indexes we can use to combine dimensions an instances. In the picture below, the light copper colored cells in row 7 contain CrtIdxλ. The border indicates the #SPILL region.

As you can see, a single LAMDA() can auto-size and calculate hundreds of values from just one entry in just one cell and it can adapt to any number of dimensions or dimension combinations. While LAMBDAs will most assuredly be more complicated than the typical FAST formula (I'm not picking on FAST, they just happened to coin the "Rule of Thumb" principal), that disadvantage can be more than offset by LAMBDA's other advantages:

1. One formula in one cell can replace thousands of typical FAST formulas making dynamic arrays and LAMBDA's faster than FAST.
2. It is impossible for a #SPILL range to be inconsistent with the source formula making models rock solid and secure.
3. The auto-sizing nature of these formulas make models extremely flexible and scalable.