Multidimensional Model: Data Model
This original multidimensional model is at MDM: TBM. To download the Data Model version, click here.
Why use the data model?
Eliminate Some Excel LOOKUP()/INDEX() Functions
We can eliminate some calculations by making the data model the one version of the truth instead of the final calculations. This is possible because the data model can establish relationships between all tables, and feed information from any table, directly to PivotTables. This is, of course, only possible if we use tables.
Add Hierarchies to Enable Pivot Chart Drill Down
Drill down is a fundamental requirement of executive dashboards. Many years ago I create a VBA solution that lets us click a chart bar and see the supporting records just like PivotTables do. A decade later, Microsoft introduced Power Pivot and Power Pivot had an interesting take on drill down as shown at right. I sill wish Pivot Charts could support click-to-detail, but Power Pivot's drill down solution is very cool and very easy with the data model. We simply select the dimensions we want, click Create Hierarchy, and if needed, arrange the dimensions in the hierarchical order we want. To use the hierarchy in a Pivot Table we just drag the hierarchy, which looks just like any other table field, into our Pivot Table. This adds drill down to the PivotTable and any pivot chart built on it.
Move Calculations from Excel to the Data Model
We can use DAX to replace most Excel functions. We can also use DAX to do things Excel functions cannot do.
What are the differences between the data model version and the previous version?
There are not many. All inputs stay the same. Calculations change. Below is the previous version's final calculation table. To make it output friendly, we stuffed dimension names into it. These names have no function in calculating MSPR Profit. They are here solely to make pivots human friendly.
Below is the data model's version of the final calculation table. The names are gone. It is totally focused on calculating MSPR values.
How do we get the names, along with the calculations into our pivots?
The answer is the data model. Below is the data model's diagram view of all tables in our model and how they relate to each other. It looks complex but it is easy and intuitive to create. Once we have linked our tables, the entire data model can be fed to our pivots making every field from every table available to them. Thus, we do not need to get the names from the final calculations. Instead, we can have the data model get them from the master tables.
Importing all tables and linking them in Power Pivot's diagram view takes about five minutes. No formulas required. And it requires no more effort to create PivotTables from the data model than it does creating them from the final calculations table.
Conclusion
I prefer keeping the final calculations table dedicated solely to calculating results. The data model helps me achieve that. And if our client forbids VBA, we can still give them chart drill down.
In the next version we will use Power Query which will eliminate ALL Excel formulas.
