If your organization has committed to building a data-driven culture, enabling day-to-day usage of data is a critical component of your strategy. That means modeling data (so it makes sense to the average employee) and surfacing it (so they can access it easily). These are standard tasks for most business intelligence tools – but if you’re invested in Microsoft’s stack, you have two primary options: SQL Server Analysis Services vs. Power BI.
Traditionally, Analysis Services has been the default choice for delivering an enterprise semantic layer. However, Microsoft has been investing more and more into Power BI with the addition of features such as Dataflows and composite models. This makes Power BI a real contender as both a front-end visualization and enterprise modeling solution. So which one should you choose? It depends on many factors, including your organization’s data strategy, skill set, and budget. Below are 3 reasons to choose Analysis Services .
Reasons to choose Analysis Services
Note: the details below apply specifically to Analysis Services Tabular.
There is no limit on Tabular model sizes. This allows you to take advantage of the in-memory Vertipaq engine’s performance with large, growing data sets. If you’re using Azure Analysis Services, you can also scale up (or down) as needed. This gives Analysis Services room to grow with your organization’s data estate. If you’re investing in big data with Azure SQL Data Warehouse (combined with Polybase for Azure Data Lake Store), you can also use Analysis Services in DirectQuery mode.
Using the Tabular Object Model, developers and administrators can manage tabular models programmatically. This grants you fine control over processing, partitioning, security, and more, which makes your solution more flexible. For example, you can use your existing scheduling tools to refresh tabular models. In addition, the implementation of TOM allows you to use multiple languages, accommodating your organization’s existing skill sets.
3. On-premises and cloud options
Azure Analysis Services includes features that help you tailor your instance to fit your workload, such as query replicas and synchronization. But if you’re not ready to move to the cloud, you can deploy your model to an on-premises server with all of the core BI features. The same model can later be deployed to Azure Analysis Services if needed.
Reasons to choose Power BI
Note: the details below apply specifically to Power BI Premium.
1. New features and investment by Microsoft
It’s clear that Microsoft is investing heavily in Power BI. Monthly updates include features aimed at regular business users as well as enterprises – and the roadmap remains ambitious. With enterprise-level features such as XMLA protocol support and shared datasets, Power BI is closing the gap with more traditional solutions – all while continuing to add exciting new features such as online Python support.
2. Direct path from self-service to standard models
With Power BI as your standard modeling tool, developers can directly leverage work done by business experts and power users. They can then bring this work up to enterprise standards. With the right processes, “promoting” self-service models can ultimately improve the speed at which your organization delivers usable data.
3. Composite models
As you collect increasing amounts of data, even the in-memory engine will begin to struggle, especially when refreshing large data sets. Using composite models, you can combine DirectQuery and in-memory (imported) data sources in a single model. This flexibility allows you to take advantage of the in-memory engine’s performance for some data sets while offloading work for very large data sets to an external solution like Azure SQL Data Warehouse.
There’s no absolute best choice for Analysis Services vs. Power BI. For now, Analysis Services continues to hold the edge as an enterprise semantic layer. But as Microsoft continues to add features to Power BI Desktop and the Power BI service, this could change. Above, I laid out reasons to choose each solution for delivering semantic models. Ultimately, choosing a solution will require deliberate evaluation of each product in the context of your organization’s analytics roadmap.