Publication number: ELQ-64319-1
View all versions & Certificate
Multidimensional Modeling LAMBDA library
Multidimensional LAMBDA Library
IT Executive and author of Ribbon Recipes: Beginner's Guide to Creating Excel Menus available on AmazonFollow 27
In multidimensional modeling (MDM), we often need to combine several dimensions and we must make sure to include all possible combinations. To calculate all possible combinations for 2 dimensions requires what is known as a "Cartesian Product." A formula that is capable of calculating any number (n) of dimensions is known as an "n-Fold Cartesian Product" formula. That formula is included in this library.
What is a Dimension?
In MDM, a dimension is a category of things. Examples include: Distribution Regions, Market Sectors, Products and Months. In table based modeling (TBM) we put each dimension into its own table.
Each dimension has instances and in TBM, these instances are stored in table rows. As an example, if our Distribution Regions table had five Distribution Regions, such as Nortn, South-East, Sourth-West, East, and West, we would add five rows to that table and each region would be in its own row.
Each dimension also has attributes and in TBM, these attributes are stored in table columns. As an example, if each Distribution Regions had 3 attributes, such as an ID, Name, and how much it costs to deliver to that region, our Distribution Regions table would have three columns. The attribute values change for each region and so attribute values are stored in each row within their own column.
What does "Multidimensional" mean?
When all instances of two or more dimensions are needed to calculate something, that calculation is multidimensional. For example, assume we have a table for products and regions. Each product has a cost. Each region has a delivery cost. To calculate total landed cost we need to add product's cost plus the region's delivery cost.
How does the "n-Fold Cartesian Product" formula help?
If we have two products that are sold in each of the five regions, we need to make sure we combine all products with all dimensions. We can calculate how many possible combinations there are by multiplying the number of products (two) by the number of regions (five). Thus, we have 10 possible combinations. We call a single combination a "tuple".
Once we know how many tuples we need, we must then calculate how to uniquely combine each of the two dimensions. One way is to start with the five regions. We count from one to five for the first product, and then one to five for the second. This will give us ten unique combinations, or, "tuples". That's what the n-Fold Cartesian Product formula does.
Does the require LAMBDA?
No. It doesn't. We can create the n-Fold Cartesian Product formula with several normal Excel functions which we would have to place in one row for each tuple, and one column for each dimension. That is 20 formula cells.
LAMBDA to the Rescue!
LAMBDA can simplify the several normal Excel functions into just one function, and replace the twenty formula cells with one formula cell! Even better! LAMBDA can expand to as many columns and rows as needed, automatically. So if we add a third dimension called Market Sectors with four sectors, we can calculate all three columns and all 80 rows with just one LAMBDA in one cell. Amazing!
This library contains:
CrtIdxλ (the n-Fold Cartesian Product LAMBDA). learm more here:
UnPivotλ: Converts a 1 by n array of repeating elements (like months) into a normalized list.
RemoveZeroValuesλ: Removes rows from a dynamic array where the value column equals zero.
If you have the Advanced Formula Environment, you can skip the zip and download the most recent version from here: https://gist.github.com/CHatmaker/e97ed39668e7d9364c973d6c5627f51e
This Best Practice includes
1 zip file with LAMBDA text. https://gist.github.com/CHatmaker/e97ed39668e7d9364c973d6c5627f51e
Craig Hatmaker offers you this Best Practice for free!
download for free
Add to bookmarks
Simplify, speed, and error proof combining multiple dimensions.
Requires Office 365
Not tested on the MAC or Office Online.