Strategic Prototyping: Power BI Desktop vs Power Pivot

Perhaps one of the most important lessons I’ve learned over the years is that there’s no right-way / one-size-fits-all approach to implementing an Analysis Services solution. All have their pros and cons and being successful is more about leveraging the approach that best fits the client organization. That said, one approach that has proven successful in several projects over the last few years involves a process referred to as Strategic Prototyping by Javier Guillén and Melissa Coates in the outstanding whitepaper they recently published called: Power BI Governance and Deployment.

Strategic Prototyping

In the whitepaper, Strategic Prototyping is defined as the process of leveraging Power BI to explicitly seek out feedback from users during a requirements discovery session. The general idea is to use a prototyping tool to quickly slap together a model and mock up some reports while working closely with 1 or more business users. This helps ensure all reporting capabilities are flushed out and accounted for. After several iterations, the Power BI model becomes the blueprint upon which an enterprise solution can be based.

Prior to the emergence of Power BI, the tool of choice for strategic prototyping (at least in Microsoft shops) was Power Pivot. And even though the reporting side of Power Pivot is nowhere near as sexy as Power BI, there is one really awesome feature that does not (yet?) exist with Power BI… and that’s the “Import from PowerPivot” option in visual studio…

Once the Power Pivot prototyping process is complete, with only a few mouse clicks, we have an Analysis Services tabular model…

On the other hand, when using Power BI, once the prototyping process concludes and its time to convert to an Analysis Services Tabular solution, we have to do every thing from scratch: import tables, create relationships, clean up dimensions, recreate measures, etc. This can take a considerable amount of time and, as we’ll see in the next section, can introduce obstacles for the Analysis Services Tabular model developer.

Power BI Desktop –> SSAS Tabular

In case you haven’t been paying attention, Power BI has been operating on a very aggressive/impressive release schedule. Personally, I think this is a very good thing and when factoring in the cost and extensibility (i.e. custom visualizations and hopefully an open API), other self-service BI vendors should be very concerned.

However, there is one drawback to the aggressive Power BI release cycle, and that’s the divergence of functionality between Power BI models and (pre-2016) Analysis Services Tabular models. As a result of this divergence, when using Power BI in a strategic prototyping scenario where the end goal is to convert to a (pre-2016) Analysis Services Tabular model, you may run into the following hurdles…

1. Use of DAX 2.0 functions
2. Use of Relationships with Cross Filter Direction set to “Both”
3. Use of Relationships with Cardinality set to 1:1

Analysis Services Tabular (pre-2016) still uses DAX 1.0 functions and only supports Many:1 relationships with one-way cross filtering. And while it is possible (in most cases) to mimic the effect of these items, the workarounds require a much deeper understanding of DAX and even then may not perform as well. Furthermore, there are some scenarios (i.e. new DAX 2.0 functions such as CONTCATENATEX) for which there are no workarounds leaving the model developer with no other option than to circle back around with the business user to work through an alternate modeling approach.

Note: Setting the cross filter direction to “both” works well fine in very simple models where there’s only a single fact/transaction table surrounded by a number of dimension/lookup tables. I don’t want to say it works “well” or works “fine” because I personally think it is less intuitive than “one-way” but perhaps that’s not the case for less experienced users who haven’t been dealing w/ dimensional models for the past 10+ years. That said, things can get very tricky once you start adding additional fact/transaction tables that share common dimensions (i.e. conformed dimensions). Be careful if you are creating a separate Power BI prototype file for each “star”/”business process”… if the intention is to eventually combine things into a single model you may run into issues with ambiguous relationships.

Alternative Approach: Power Pivot 2013 + Power BI –> SSAS Tabular

One way to avoid the issues above (while still retaining the pretty report mock-ups and reporting functionality requirements) is to use Power Pivot in Excel 2013 as the tool for prototyping the “model” and Power BI for prototyping the “reports and dashboard”.  This version of Power Pivot will prevent the resource handling the prototyping from making use of the aforementioned features that aren’t supported in Tabular 2012/2014. Furthermore, it allows the development team to leverage the “Import from PowerPivot” option to kick start the Analysis Services project at the end of the strategic prototyping process.

In order to build reports based on a Power Pivot model, you’ll need to import the excel workbook into Power BI Desktop…

 

There is one glaring issue with this approach… and that is if/when you need to make a change to the model (e.g. add a measure, rename a column, etc) after the excel workbook has been imported into a Power BI Desktop file – strategic prototyping is an iterative process. In this scenario a decision must be made as to whether you make the change in the Power BI Desktop file or go back to the Power Pivot model and make the change there.

Making the changes in Power BI Desktop keeps you moving forward, but you will need to make sure you remember to (at some point) go back and make the equivalent changes in the Power Pivot workbook if you plan to leverage the “Import from PowerPivot” option later on. If there are only a small handful of changes, then this is not that big of an issue… however, the more changes you make directly in the Power BI Desktop file, the more duplicate effort and more risk.

Making the changes in the Power Pivot means you won’t have to worry about missing a minor change when it comes time to use the “Import from PowerPivot” option, but you will lose any reports you already created in the Power BI Desktop file. Depending on how much time has been spent by you and the business user(s), this may or may not be palatable.

Bottom Line

No matter which tool or tools you use to during the Strategic Prototyping process… Power BI only, Power Pivot only, Power BI + Power Pivot… there are going to be trade offs. The best you can do in order to be successful is to pick the approach that best fits the client organization.

 

Additional References:

• Upgrading Power BI Desktop Models to Tabular