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.
To include this LAMBDA in your work:
Use the Developer tab, click Add-Ins (the first Add-ins icon),
Got to the Microsoft store and download the Advanced Formula Environment.
After installing the add-in go to the Home tab and click the Advanced Formula Environment icon on the right
Click the Import icon (looks like a download icon)
Copy and paste this URL
https://gist.github.com/CHatmaker/e97ed39668e7d9364c973d6c5627f51eClick Import button
Click the Sync icon (looks like a folder with a small recycle icon).
To test, find an empty area and type this formula: =CrtIdxλ({1,2,3})
Summary
Adding CrtIdxλ eliminated a half dozen dynamic array formulas and standardized creating the Cartesian Product necessary for MD TBM.
Series Conclusion
This is the end of our exploration of multidimensional modeling approaches. Multidimensional modeling clearly demonstrates TBM's advantages in complex models. All of these versions are designed for expansion and adaptation. All also maintain the core concept that formulas should be self-documenting. Lastly, these 5 different versions exhibit how we can easily leverage whichever functionality Excel offers that best meets our needs.
