Analysis Services vs Power BI Premium Model Feature Matrix

Over two years ago I published a blog and document with a feature matrix comparing modeling features of the different releases of Analysis Services Tabular and Multidimensional. At that time, the Tabular model was in the process of becoming the flagship and default enterprise modeling tool by closing existing gaps with Multidimensional mode and introducing new features.

Today, Power BI Premium datasets are in the process of becoming Microsoft’s flagship enterprise modeling tool, a superset of Analysis Services, by closing existing gaps with the Analysis Services Tabular model and introducing new modeling features exclusive to Power BI such as composite models and aggregations.

In this context, you may download and review the following feature matrix from the Insight Quest GitHub repo:

Analysis Services vs. Power BI Premium Model Feature Matrix

Since Power BI Premium datasets will become a ‘superset’ of Analysis Services, you might think that the future Power BI Premium model would have only Green/Supported cells. To an extent this may be true and existing Analysis Services features will just start popping up in Power BI Desktop and via XMLA endpoints. However, for some longtime Analysis Services features (e.g. KPIs), the Power BI team may introduce something completely different in Power BI which satisfies the essential need but also provides additional functionality. For this reason, I left some cells on the future PBI Premium column as yellow (‘to be determined’).

More than Models

One thing to keep in mind is that Power BI Premium has a much larger mission than analytical data models (or ‘cubes’). Provisioning Power BI Premium capacity gives you the option to run other ‘workloads’ such as paginated reports (essentially SQL Server Reporting Services running in Power BI), Dataflows at scale for self-service ETL, the new AI workload, and more. It also allows you to support large groups of consumers and avoid individual pro license assignments.

Living in the Now

Let’s assume you’re confronted with the need to build an analytical data model for a BI project and this project involves typical, large-scale and enterprise modeling requirements such as many dimension and fact tables, large and diverse groups of users, vast amounts of data, etc. Which tool do you choose – Power BI (with Premium capacity) or Analysis Services?

In many of these scenarios, Power BI is currently not up to the task as large in-memory models, incremental refresh, XMLA endpoints, and shared datasets (across workspaces) is all still in preview and thus not suitable for production workloads. Additionally, you can’t define Perspectives to keep your broad model usable and relevant for diverse audiences, you can’t define Key Performance Indicators (KPIs) within the model, you can’t take control of the table partitions and data refresh process with custom scripts, and the list goes on. (This may not be what you hear at Microsoft technology events or via other information sources (which also may be influenced or owned by Microsoft) but ultimately current state technical realities have to be acknowledged.)

The modeling feature gaps and various scalability limitations mentioned above and in the matrix document are being addressed but Power BI Premium is just not there yet. My guess is that it will be around Q4 of 2020 when you can comfortably use Power BI Premium for all new enterprise modeling projects.

This being said, there are already some important and common scenarios where Power BI datasets can exclusively meet the requirement or deliver value that Analysis Services can’t. Composite models with multiple data access and storage modes across different sources and aggregations are both game changing modeling features with dramatic implications for DW/BI architectures. The additional data sources and connectivity options supported by Power BI (e.g. DirectQuery to Snowflake) also sometimes make Power BI as the only tool for the job.

Upgrade to Premium (in the future)

The good news is that it should be very easy to deploy an Analysis Services model to Power BI Premium in the future. You’ll simply change the value of the deployment server property from your existing SSAS or Azure AS server to the XMLA endpoint of the Power BI Premium workspace.

Deploying to Power BI Premium should both respect existing Analysis Services Tabular modeling features and also provide access to modeling features exclusive to Power BI.

Currently Power BI Desktop files run in 1465 compatibility level but per the above image from a Power BI Premium workspace, several other compatibility levels are supported including 1510.

Wrapping Up

Hopefully the new feature matrix document and this blog was useful to you. If nothing else you should recognize that there are many important features and considerations in Analysis Services and Power BI models and that this conversation is evolving. Feel welcome to share thoughts/feedback in the comments and click ‘Follow’ if you’d like to be notified of future blog posts.