Multidimensional Model: TBM
In this section we will explore an example model. To download the model, click here. The model is totally table based.
This is not a tutorial so we will not be examining the entire workbook. Instead, we will concentrate on the major sections that define TBM: Inputs, Process and Outputs.
Inputs
This model offers the user a lot of things they can change. All gray cells are input enabled. There are eight user input tables. The first five are shown below
The first and last of these tables are Properties Tables.
Properties Tables
Property Tables contain distinct information that does not belong to any instance of any dimension. Each entry is usually converted to a named range so the values can be used in formulas as names instead of hard coded literals. The property tables are on the left: Model Properties and Labels:
Model Properties (tblMP)
This table contains the following entries which become named ranges. They are:
Base Price - Paul's model requirements dictated how price was calculated which requires this input.
Monthly Fixed Costs - This is the amount that must be allocated over all production units for each month.
Products - I added this so users could declare how many products (instances) they want in the model. This was not part of Paul's design but I wanted to show how TBM, without VBA but with Integrity Checks, can facilitate guiding users through the process of adding or removing dimension instances without modeler intervention.
Regions- I also allow users to declare how many regions they want...
Sectors - and how many sectors
Labels (tblLabels)
This table allows users to designate the currency and date format to accommodate other regional preferences.
Master Tables
The middle four input tables are Master Tables. Master tables hold a single dimension (ex. Sectors), all of its instances (ex. Government, Military, etc.), and all attributes unique to that dimension (ex. Name, Rebate Percentage, etc.). The master tables in this model are:
Sectors (tblSectors)
This contains market sectors and their attributes: name, rebate percentage, and demand factors A and B.
Regions (tblRegions)
This contains Regions and their attributes: name and unit delivery costs.
Product (tblProducts)
This contains Products and their attributes: name, base price multiplier, and unit production cost.
Primary Keys for Master Tables
Note that every master table starts with a sequential number. It is the master table's unique key, also known as a primary key. We will use these primary keys to retrieve values from our master tables. The system assigns the key using formula:
=ROW()-ROW([#Headers])
Association Tables
The remaining input tables are association tables. Association tables relate two or more dimensions and include values where dimensions intersect. They are shown below.
The association tables are:
Distribution Per Sector and Month (tblDSM)
This allocates the monthly seasonality distribution for each market sector.
Distribution per Sector and Product (tblDSP)
This allocates the annual distribution for each market sector and product.
Distribution per Sector and Region (tblDSR)
This allocates the annual distribution for market sector and region.
Primary Keys for Association Tables - The n-Fold Cartesian Product Formula
For association tables, the unique key is the combination of each master file's primary key. To create all unique keys for any number of dimensions and dimension instances, I created the n-Fold Cartesian Product Formula. Click that link to learn about that formula and where its crazy name came from.
Process
The process section is where the real work occurs. The tables here contain nothing but formulas and labels.
NOTE! If you want to know what formulas were used, download the model by clicking here. Protection has been removed to make it easy for readers to examine this model's inner workings.
Preliminary Calculations
The first four tables an preliminary calculations.
The tables progress from left to right starting with the simplest (no dimensions) to most complex (three dimensions). The preliminary calculation tables are:
Calculated Properties (tblCP)
This Properties Table calculates the number of records needed to accommodate all permutations of various dimension combinations. These results are used in the Permutation formula (see above) and by Integrity Checks (not discussed) to verify all tables are properly sized.
Sector Calcs (tblSC)
This table uses values from the Sectors master table and the Base Price from the Model Properties table to calculate the sector's base price and annual demand units.
Sector Product Calcs (tblSPC)
This association table uses values from the Products master table, Sector Calcs table, and Distribution Per Sector and Product table to determine distribution percentage, annual product unit sales, and price for each sector/product combination.
Sector Product Month Calcs (tblSPMC)
This association table uses values from the Sector Product Calcs table, Distribution per Sector and Month table to calculate the number of unit sales for each month, sector, and product combination (only first 12 records shown)
NOTE! The last two tables utilize the permutation formula to generate the indexes used by other columns in each table.
NOTE! The light teal columns contain indexes. The medium teal columns are INDEX() functions bringing values from other tables into this table so they are available for calculations which are represented by dark teal.
Final Calculations
The final calculations contain all dimensions, all instances, and all attributes needed by outputs as well as calculated results also needed by outputs.
Outputs
All outputs start with a single PivotTable over the final calculations table. All other pivots are modified copies of the first guaranteeing they all share the same data.
Standard Reports
In general, Pivots provide no calculations other than totaling calculation results by some category. In Paul's challenge he indicated the outputs should be of the nature shown below.
Interactive Analytics
Paul did not ask for what comes next, interactive analytics.
Analytics are easy to add to TBM. We start with copies of our original PivotTable. The first five are shown below.
Below are the final three pivots.
It takes about a minute to create each pivot. Each pivot produces a different perspective of our results. Each of these enable drill down to expose support for each value. Each of these can be filtered using slicers to focus our scrutiny. And each of these provide the data for pivot charts.
Below are the charts produced. In the picture I am filtering on market sector Education. At Education's current discount structure, it is draining this fictitious company's profits. That was hidden in Paul's suggested outputs.
This is the tremendous value of analytics. Analytics were not requested in the challenge but they were quick and easy to add. Analytics over traditional geometric oriented models would be very difficult to accommodate. This underscores the tremendous value of TBM.
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 gain insights hidden in the data.
This version of the model used traditional Excel formulas. The next version uses the data model to eliminate several columns in our final results table.
