Table Based Multidimensional Model
Part 1 - Introduction to TBM in a Multidimensional Implementation
A few years back, researcher Paul Mireault issued a challenge to financial modelers in order to collect samples of how we tackle the problem of multidimensional modeling. Multidimensional modeling is where we have several different entities that interrelate. In Paul's challenge the dimensions are: Products (P), Market Sectors (S), Distribution Regions (R), and Months (M). Paul's challenge was to determine the projected demand and profit.
Traditional Spreadsheet Approach
The task proved challenging to traditional modelers who structured their solutions in various geometries with four examples shown below. As you can see, each is configured to create that intersection in the center (MSP Unit Sales) of all dimensions.
Now, after looking at these examples, imagine the challenge of modifying any of these solutions to accommodate more products, sectors, regions or months, or worse, adding dimensions. Note that the diagrams omit the region (R) dimension. I assume this was Paul's decision to keep the diagrams from looking too complex. If so, that emphasizes how representing dimensions with 'geometry' can become unwieldy. Now imagine the challenge of adding analytical features so we can easily compare each dimension to identify under performers.
TBM (Table Based Modeling) Approach
This problem was solved long ago in relational databases that simply relate and aggregate various entity values. This is how all ERPs work and this is how I created my TBM solution. In fact, I created three solutions (and later added a fourth) all using TBM. The first (click link below to download) uses Excel formulas.
The second uses the data model to reduce formula count. The third uses Power Query to reduce formula count even more and add an 'auto-extending' feature. The fourth uses Dynamic Arrays which make auto-extending models awesome (Follow me to be notified when I post articles on these other solutions). Imagine a methodology that works with any of these powerful features, easily.
领英推荐
Examining the TBM Solution
Open the model and follow along if you like.
On the Inputs tab we find separate 'entity' tables for each dimension. An 'entity' table is a table in which each row contains information related to one and only one entity. They are shown below.
We will also find 'association' tables that allocate each entity across one or more dimensions as dictated by Paul's specification. An association table relates one or more entities where each row contains information specific to the union of each entity. These tables are shown below.
On the Process tab we have a table that creates all intersections of all dimensions. Each intersection is a single row in which we calculate price, cost, demand and profit for that specific intersection.
On the Summary and PVTs tabs we see how we can leverage several PivotTables to aggregate the detailed data - the one version of the truth - and present each dimension's performance result.
On the EDB (Executive Dashboard) tab (shown at top) we see how we can add slicers to interactively analyze the various dimensions either combined, in isolation, or in relation to other dimensions.
Conclusion
TBM is a natural solution to multidimensional modeling. It easily accommodates adding more products, sectors, regions, months or other dimensions. Table calculations can create a single version of the truth upon which we can layer as many pivots as we like to reveal key aspects hidden in the data. Pivots provide the foundation for Pivot Charts to which we can attach Slicers so we can interact with results to get the information we are most interested in.