Data Model Options for Power BI Solutions

At the heart of every a Business Intelligence reporting  solution is a data model to optimize queries and enable ad hoc report interactions.  Data modelling technology has evolved quite a lot over the past 20 years or so.  You have several modelling technology choices with options depending on the simplicity or formality of your project and factors like data size and security.  In the past, choices were simpler.  Power BI was the choice for smallish, “good enough” projects; and when data quality, high volume and exactness were the priority, Analysis Services (SSAS) was the better choice.  Now, using Power BI for modelling larger data sets is even advantageous compared – with new features like hybrid models, aggregations and incremental data refresh.  Just in the past year or so enhancements have been added – or are in the process of being added – to the Power BI platform, that the list of options and considerations continue to grow.  Here’s a quick (and I mean quick) summarized list of the modelling options for Power BI solutions:

• Power BI Desktop model published to the Power BI service – limited to 1 GB model size, no partitioning.  Pay per user with Power BI Pro licenses.
• Power BI Desktop model published to a Premium Capacity Power BI Service – 12 GB size limit, dataflows & numerous “grown-up” features.  Unlimited read-only domain users.
• Power BI Desktop model published to on-prem Power BI Report Server – limited to 2 GB model size, no partitioning.  Prior version & feature restrictions.
• SSAS on-prem or AAS using report published to PBIRS – no model size limit.  Prior version & feature restrictions.
• SSAS tabular or multidimensional on prem connected to Power BI report in service via the data gateway.  No size limits.  License server cores for SSAS.  License Power BI using Pro or Premium.
• Azure Analysis Services = SSAS tabular.  No size limit, pay as you go and scale as needed.
• Power BI reports in the service or PBIRS using DirectQuery to connect to data source with no cached model.  Report interaction queries may be slow, with some calculation limits.  *Aggregations & hybrid model design can optimize & reduce query latency, with moderately complex model development overhead (Premium features).

There are numerous advantages and disadvantages with each option so the best choice always clear but we can certainly compare the pros and cons.  I’ve been on a quest to find the best medium to break these choices down into simple terms but it truly is a journey and not a destination.  Along this journey, earlier this year, I presented a session called “The Nine Realms of Power BI” where I enumerated different categories of solution architectures for Power BI solutions; and they are numerous.

One important consideration is Microsoft’s commitment to support a product with new features in the future.  They have made it very clear that the Power BI platform is their primary focus and that they will continue to invest in enterprise-scale capabilities in the cloud service.  Never plan on a crucial feature being added later to a product but give serious consideration to  where a product is headed.

Making a side-by-side comparison of features between products and options is a little like comparing oranges, apples, grapes and bananas.  As a best effort, I started with the feature lists for SQL Server Analysis Services and added the Power BI variations.  Admittedly, this type of comparison doesn’t fit perfectly into this type of pivot format but I think it serves the purpose.  This post will likely evolve a bit with time.  Your feedback and input are welcome.

Some additional resources from trusted colleagues: Matthew Roche from Microsoft recently posted this article.  He refers back to this blog post and mentions an excellent session at the recent PASS Summit from Gabi Münster and Oliver Engels.  On Matt’s post, he links to the session slide deck that Gabi and Oliver shared.  An official whitepaper written by Chris Webb and Melissa Coates covers Power BI enterprise deployment options in 105 pages.  Keep in mind that many new features have been added since this paper was written but the whitepaper will likely be updated in the future.  Matt also references another recent on this topic by James Fancke where he contrasts features and costs.  This is, no doubt, a timely and important topic.

Guy-In-A-Cube’s Adam Saxton and Patrick LeBlanc have posted numerous short videos highlighting specific features of Power BI with Analysis Services and here are a few to get you started.  

Side-by-Side Feature Comparison

In the following table I compare capabilities, starting with SSAS Tabular and SSAS Multidimensional, with Power BI Pro and Premium capacity.  My blog theme doesn’t current support wide tables like this (apologizes for the overlapping text) so you can Open or download the Excel file here.  I hope this is helpful.  I’ll work on updates to optimize this comparison chart as features are added and time allows.