Multidimensional Model: Power Query
This original multidimensional model is at MDM: TBM. To download the Power Query version, click here.
This model explores leveraging Power Query (PQ) for multidimensional modeling. It was an experiment that provided some interesting learnings. The model has few stark differences from other versions. They are:
PQ replaces all Excel formulas
I wanted to see how PQs calculation engine compared to Excel's formulas. This rendered the Process worksheet useless so it was removed.
PQ formulas look very much like Excel's Structured Reference formulas. Here is an example:
Benefits:
We can place the inner workings out of reach of users so it is less likely they will change it.
Given the nature of PQ formulas, it is impossible to have inconsistent formulas in calculations.
Drawbacks:
We must use refresh to calculate anything; but, given that this model uses pivots that also require refresh when things change, this isn't really a problem.
This requires reviewers have PQ skills.
PQ controls input table sizes
PQ has the ability to generate table rows. We can feed those rows into an Excel table and attach input cells. If we want more items in any dimension we can change the number of items in the Model Properties table (shown and right) then refresh the model.
Benefits
All tables will resize automatically
Drawbacks:
New rows are often added to the middle of the table instead of the end.
tblMP
I'd like to remind us of a few things mentioned in prior posts:
A key to multidimensional modeling is combining all items from all dimensions.
Multidimensional modeling has been around for a long time in database management systems (DBMS) like Oracle, DB2, MS SQL and the like.
Tables allow us to mimic DBMSs (in some ways).
The DBMS method for combining tables is called joining. PQ calls it merging. PQ supports multiple merging methods. The method we need is known in the DBMS space as a cross join (aka Cartesian Product). Unfortunately, that method is not listed in PQ's Join Kind dropdown as of this writing (see below).
Cross Join
A cross join combines all records from table 1 with all records in table 2.
PQ's documentation claims the Cross Join option is there. While it's not in the dropdown, it is supported and it is very easy to implement. Just add a custom column and type in the name of the table to cross join (see below).
Benefits:
No complex formula to learn - no formula at all!
Drawbacks:
None!
PQ and PivotTables
In the TBM world, we use PivotTables to summarize/aggregate calculations. PQ results can be loaded to tables, which PivotTables love.
Benefits:
We can more easily check our results in Excel's grid.
Drawbacks:
It duplicates what is in PQ
It is restricted to 1 million rows
PQ results can also be loaded directly to PivotTables
Benefits:
We can supply more than a million rows of data to PivotTables!
Drawbacks:
We must go to PQ to check our results
PQ results can also be loaded to the data model and then to PivotTables. This has the same benefits and drawbacks to loading PQ directly to PivotTables with two additional benefits:
Benefits:
We can have hierarchies for pivot chart drill down!
We can add DAX measures when they are better suited for specific calculations
Summary
I really like using PQ. It is more structured than Excel's grid which is important to me. For clients who don't care how we calculate results, we are free to use this method. It requires refresh which is a simple 30 second training opportunity.
PQ is slower than formulas which is important to clients so while this experiment proved we could use PQ to replace a lot of what we do in Excel, I'm not ready to go there. Additionally, I do not recommend using PQ to auto-extend entry tables. While I am proud of that little trick, experience shows it causes more confusion than it solves. Instead, use integrity checks to alert users when they need to expand tables or use BXL's Appify Add-in when VBA is allowed.
