Multidimensional Model: LAMBDA

LAMDA() lets us create functions from Excel's formulas. It can reduce complicated formulas to extremely simple formulas. 

Cons

One downside is LAMBDA really only comes in to play when formulas are complicated, and we really do not want complicated formulas. 

Another downside is LAMBDA 'hides' the formula somewhat making it less transparent, even for skilled modelers. 

Pros

On the upside, if a formula is used often, it ensures that every instance of the formula is identical and it reduces auditor effort because once the LAMBDA is verified in one cell, we know all cells that use it cannot be different and thus, are also correct.

To LAMBDA, or not to LAMBDA

In light of the pros and cons, I recommend using LAMBDA only for 'standard' formulas. This can have a huge benefit in terms of productivity and reliability because once a LAMBDA is fully tested, it can be stored in a LAMBDA repository (GitHub provides repositories). Armed with a LAMBDA library, we can move from building models from formulas to assembling models from pretested, prebuilt components. 

This is no different than using Excel's library of pretested, prebuilt functions. No one every wants to see the inner workings of, say, SUM(). The fact that its inner workings are totally opaque to us is of no concern to anyone. We know it works and so we use it without question. The concept can be the same for LAMBDAs that we trust. And if we have any concern whatsoever with a given LAMBDA, we can, unlike SUM(), examine its inner workings.

Another advantage of a LAMBDA library is if we find something isn't quite right, either because of a design flaw, or a business logic change, or a standards change.... we can do what is necessary to update the LAMBDA and place it back into our standards. This is TQM (Total Quality Management), Continuous Improvement, ITIL, and ISO9001 in a nutshell; standardize and document > Implement > monitor and Improve > repeat. A library of LAMBDA's is a good step in implementing these world class standards.

LAMBDA and Multidimensional Table Based Modeling (MD TBM)

A prime candidate for LAMBDA is the n-Fold Cartesian Product Formula.  It is a somewhat difficult to understand formula and it is standard in MD TBM. What is more, in a LAMBDA implementation, we can, with one formula in one cell, create the entire cartesian product (dimension indexes) for all required dimensions. In the image below we can see the LAMBDA() formula in the formula bar. We can also see three light copper shaded cells. These cells contain the LAMBDA(). The bordered cells are its #SPILL range. Those cells have no formulas; thus,

• There is no possibility that any of those cells could be inconsistent. 

• There is no reason to audit them

• They automagically extend and contract as dictated by the LAMBDA()

• We have just one formula in one cell to import and all that would be a sea of formulas is done for us.