What? Is that even a valid question?
At first glance this isn’t an obvious topic, but I’ve had a couple of conversations recently with partners that echoed some of my own thoughts about where and how Power BI Premium sits with Analysis Services.
Let’s recap what Power BI Premium brings in terms of performance enhancements (read about its wider capabilities here):
- Increased dataset limits
- Real time cache/pin to memory
- Secondary model copies
- Incremental/Dedicated Refreshes
With the new support for very large data sets, incremental refreshes etc, this has led to the following question being asked:
“If I have Power BI Premium with all these enhancements, why would I need Analysis Services?”
To me, there’s one main reason above all others why you would use Analysis Services in conjunction with Power BI, and you can skip to the summary to see it, but in order to answer this, let’s first look at the two products in more detail:
Until recently, we had Power BI Pro (or Free) and Analysis Services. Both had quite a clear, defined place in the BI ecosystem. Power BI is described as the following:
“…a suite of business analytics tools to analyze data and share insights”
These tools include Power BI Desktop (a powerful desktop data mashup and reporting tool), Power BI Mobile (mobile apps allowing you to access reports from anywhere) and the Power BI Service (allows you to publish and share reports throughout the organisation).
So, Power BI allows users to ingest data from a variety of sources, mash it up, build some reports and publish them to the Power BI Service for sharing and collaboration.
Power BI Premium is an add on to Power BI Pro that brings dedicated capacity, performance and scalability (see earlier in this article).
With Analysis Services, you can mashup and combine data from multiple sources, define metrics, and secure your data in a single, trusted semantic data model.
In many ways, Power BI and Analysis Services are very similar. In fact, Power BI has at its heart a Tabular Analysis Services engine. When you ingest and mash up data into Power BI Desktop, you’re actually populating a local version of Analysis Services.
In fact with both tools you have very similar features:
- Create hierarchies
- Calculated columns
- Row Level Security
- Live Connection/Direct Querying
Data Visualization and reporting aside (Power BI does this, Analysis Services doesn’t – it’s not a reporting tool) there are also differences, but the lines have become blurred in recent times.
The Main Difference
The key difference is that with a data model created in Power BI directly, it’s essentially a closed model to Power BI only. Users can create pages (reports) in Power BI Desktop with multiple visualizations and then publish this workbook to the Power BI Service. Within the Service itself, users can edit the published reports on this dataset as well as adding new ones:
The point here is that this model and its data can be reused within and by Power BI only. Users must login to Power BI to edit the report, or they can publish a Power BI Desktop file as a central model and connect to it as a Power Service Live Connection. All of this functionality is pure Power BI.
Where Analysis Services differs is that it is essentially an open model. Yes, you create a data model by ingesting data and creating a user-friendly reporting model in exactly the same way you do with Power BI, but that’s where you stop. You publish the Analysis Services model as a central, trusted data source and – and this it the crux of the matter – ANY tool that can submit DAX or MDX can use your Analysis Services model and get exactly the same answer. This is the ultimate “single version of the truth” that is the goal of so many BI projects. You create your central model, publish it, then connect to it from any of the following tools, plus many more:
In the end, Power BI Premium on its own is a compelling option if you’re just a Power BI shop (and Excel, sort of) and nothing else. Your analysts and developers can develop Power BI models in Desktop, and these models can be used to create reports in the Service, or from Power BI Desktop using the Power BI Service Live Connection. My advice in this scenario is to start with Power BI as your data models, especially if your models are small or moderate in size, then consider moving to Analysis Services as a scaling up option. This is an especially appealing option now with the release of the Analysis Services Web Designer, which allows you to publish Power BI workbooks to Analysis Services and create a data model from them. This is an ideal scenario where you’re running a PoC and want to promote it to something more “enterprise”.
Scale Up Power BI to Analysis Services
However, if you want a central data model to act as the single version of the truth and have many different tools in use across the organization, then Analysis Services is your friend:
In conclusion, I recommend Analysis Services as a key component for any relational data warehouse platform in combination with Power BI. Whilst the decision isn’t as clear cut if you only use Power BI, the ability to create a completely abstracted, semantic layer that serves as your golden view of data regardless of what visualization tool is used is a point that can’t be over sold.
Agree? Disagree? Please feel free to comment below.
Power BI Service Live Connection
Interesting post! Another thing to bear in mind is that Analysis Services exposes a lot more properties and options for the developer: this means it is a lot more flexible than Power BI Premium, but also that it is a lot more complex (and therefore time-consuming and more expensive) to manage. Some people will need that flexibility, others will need the simplicity of Premium.
LikeLiked by 1 person
Thanks Chris! Yeah totally agree, I’m a massive fan of Analysis Services and hadn’t even considered the question really of Power BI being used as an “alternative” to it until it was raised. Personally I’d always try and have a semantic layer that is separate from the reporting layer but I can envisage some scenarios where perhaps Power BI on its own could do the job.
Great post and this has got me thinking about some scenario’s where it might be better doing a proof of concept with Power BI. And if it requires to scale then Analysis Services is there to pick it up and easily transform it.
As well as on more traditional BI projects it makes perfect sense to leverage Analysis Services (Especially Azure Analysis Services) as this can then be easily integrated into Power BI. It can give you a similar experience to Power BI Premium, but if required with additional functionality.
LikeLiked by 1 person
Thanks Gilbert, and yes I totally agree. Especially with the Analysis Services Web Designer functionality it’s easier than ever to start off with Power BI before promoting to a fully fledged Analysis Services model.
Thanks for the post, Mike. Please note the capabilities you mentioned for Power BI Premium: “Increased dataset limits, Real time cache/pin to memory, Secondary model copies, Geo-replicas, Incremental/Dedicated Refreshes” are still on the roadmap – cf whitepaper – so in most use cases when customers have large volume of data that cannot fit in a 1GB file (compressed), Power BI Premium alone is not enough.
Really helpful blogpost! Maybe you could do a comparison from a cost perspective also? I think that is a big factor when choosing which path on a specific solution.
Thank you! In fact I’m doing a revised version of this post to take into accounts recent announcements on the Power BI roadmap so I’ll include such a comparison.
Solid article – the issue that our organization is having is that it seems AAS Tabular and VS are rapidly becoming out of data compared to the speed of evolution of Power BI.
We have had issues with adding roles in VS (need integrated workspace mode enabled), and more importantly, a lack of DATA CONNECTORS for APIS (the data source definition protocols just do not exist in AAS / VS, where they do in Power BI for web services). We have researched on the web and gone into the JSON file that defines the model for VS and have not been able to find or add the data source definition for the web.content M function in order to query our own API. This has been talked about (migrating data connectors to VS from PBI), but it doesn’t appear to be happening.
MSFT seems to be suddenly tight lipped about what is happening with support and new features (catching up to PBI) for AAS.
We are considering forgoing AAS altogether because of these issues (and other bugs and glitches which seem to plague VS working with AAS).
So, what is the word? What is happening with AAS? Why aren’t we seeing the features and web connectors being ported over timely?
It appears MSFT is setting up PBI Premium to replace AAS. With the CDS, and new data flows capability, etc., PBI Services and Premium are starting to look like the new enterprise model/architecture.
Any thoughts here?
Nice Article Mike
If the use case is mostly do with big Data and Power BI to visualise the data, I don’t see a point in bringing Azure AS for big data anyways you more like to use the direct query mode for it. Even for standard and defined report why cannot we use a dedicated workspace with more operational control on who can author and deploy enterpise level MI or BI reports.
It would be interesting for you to reapproach this question now, 4 years later, with the new Premium Gen 2 changes and the availability of the XMLA endpoint.
This was my immediate list to do by looks like my colleagues in the Power BI product team have beat me to it?
Let me know if you think there’s still outstanding questions on this?