Multidimensional Model: Dynamic Arrays
This original multidimensional model is at MDM: TBM. To download the Dynamic Array version, click here.
This version uses dynamic arrays (DAs) for calculations. It does not use them for inputs or outputs. DAs make our calculations automatically adjust to however many instances we have in any dimension. While that is wonderful, I have some concerns with DAs. I both love and hate DAs.
I LOVE that DAs expand automatically with NO modeler intervention. This makes models built with them incredibly flexible and scalable while decreasing complexity and potential points of failure.
I HATE that DAs don't have names like tables and structured references.
Structured References are incredibly important. When dealing with complex models, it is hard to keep track of what belongs to what and what impacts what. Cell references make keeping track of that harder because to get any idea of what a reference is, we must go find it and hope it is labeled. Structured references ARE the reference's label so we NEVER have to find the reference to get its label. Its label is IN the formula and that makes the formula self-documenting.
Names are almost as good as structured references. But unlike structured references, are we must maintain names and it is possible that the name will not agree with the label sitting next to the cell it refers to. To address that, I created a naming convention to mimic structured references. This seemed natural because as I converted the original model to dynamic arrays, I grouped the various array columns identically to the tables on which they were based. They look like tables, they just aren't tables.
Dynamic Array Naming Convention
Unlike structured references which are automatically created, manually creating dynamic named ranges is a huge pain. I have an add-in that automates it for me which makes my work life much better. I intend to make it available on Eloquens.com in a few months so be sure to check there for it in a month or two. Until then, below is the naming convention I use, and that my add-in uses. The explanation is in parent then child order but the way we must enter them is reversed. We must create child names first (individual DAs) then the parents (DA Collections)
Dynamic Array Collection Prefix
A dynamic array collection is several related dynamic arrays placed together in such a way as to look like a table with columns. The figure above is the first 12 rows of the final calculations collection. Each column has a heading just like each table column has a heading. I use the prefix "tbl" for tables so I decided to use "da" for dynamic array collections.
Dynamic Array Collection Names
If my table's description is one word, like Products my table name will be tblProducts. If the table's description is more than one word, I abbreviate the name so Sector Calculations becomes tblSC. Such abbreviated names a referred to in computer circles as Mnemonics which means we have enough information to remember what it refers to without having to spell it out completely. Remember, this is for the modeler who is intimately familiar with his or her creation, not the end user. Mnemonics work for tables so they will also work DA collections. When I converted tblSC to a dynamic array collection, I named the dynamic array collection: daSC.
The formula for a dynamic array collection will be a dynamic named range formula. This formula assumes the first column is a dynamic array and has been named. That provides the dynamic row count. It also assumes that all headings are entered, there are no more than 99 columns, and that the collection ends at the first blank cell after the heading start in the heading row. In this example, the first column is named daSC.Row.
=LET(FirstColumn, daSC.Row,
Rows, ROWS(FirstColumn),
Columns, MATCH(TRUE, ISBLANK(OFFSET(FirstColumn,-1,0,1,99)),0)-1,
OFFSET(FirstColumn,,,Rows, Columns))
That's a horrible formula but it is fully dynamic and easily replicated, just change daSC.Row to the first column's name.
Dynamic Array Collection Item Names
If a dynamic array is in a collection, each column is named using the collection's name, a period, and then the column name. Thus, if daSC has a "Product ID" column heading, the dynamic array under that column heading is named daSC.Product_ID
NOTE! Excel's names cannot have spaces so if a column heading has spaces I choose to replace spaces with underlines. Another good approach is to use Camelcase which apply proper case to the name and then removes spaces. Thus "Product ID" becomes ProductId
The formula for dynamic arrays is very simple. It is the first cell in the array with # added. Thus, if aSC.Product_ID is in cell A3 its dynamic named range formula is:
=$A$3#
Dynamic Array Collection Parts
For preliminary calculation collections, this part is totally optional and probably not needed. But when needed, we can easily modify the formula and names to more closely mimic all of a table's structured references, specifically [#Headers] and [#All].
[#Headers]
Append .Headers to the dynamic array collection's name. Thus the headers for daSC is daSC.Headers. The formula is:
=OFFSET(daSC,-1,0,1)
[#All]
Append .All to the dynamic array collection's name. Thus the headers and data for daSC is daSC.All. This is needed for Pivots and Power Query which are only built over the final calculations collection. The formula is:
=OFFSET(daSC,-1,0,ROWS(daSC)+1)
With names in place, our formulas look very similar to structured references. Let us compare the MSPR Profit formula from the Excel formula version and this DA version.
Structured Reference from Table =[@[MSPR Sales Amount]] + [@[Total Costs]]
DA Named reference from collection =daProcess.MSPR_Sales_Amount + daProcess.Total_Costs
The most noticeable differences are:
No table name in the structured reference; but, if the formula is outside the table, the table name is required.
No brackets in DA formulas
No @ sign in DA Formulas.
The @ sign in structured references explicitly associates these references to the same row. In DAs, the relationship is implicit. I like explicit. But I also like that because the implicit nature of DAs makes the #SPILL range possible and the #SPILL range eliminates the need to copy the formula down every row in a column like we must with structured references. And that means we have just one formula in one cell to audit and maintain. We have zero chance that formulas in a column are inconsistent.
Summary
Naming removes my primary objection to DAs. What is left is pure goodness - mostly:
Pros
Works well with tables for inputs (does not work as a data input/user entry mechanism).
With names DAs are self documenting like table structured references.
With DA collection naming, we have an easy method for linking DAs to Pivots.
Incredibly dynamic models are possible reducing maintenance demand and delighting clients
Orders of magnitude fewer formula cells
Dramatically reducing potential points of failure (every spreadsheet cell modified in any way is a potential point of failure).
Dramatically reducing model audit/review times
Set PivotTable Data Source to DA Collection
Cons
Cannot be used in the data model without moving through Power Query first
Moving into Power Query, at this moment, requires some knowledge of "M". Example:
let
Source = Excel.CurrentWorkbook(){[Name="daProcess.All"]}[Content],
AddHdrs = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
AddHdrs
Lastly, and this is a big one for financial modelers, to the best of my knowledge, DAs cannot calculate corkscrew accounts.
