If you’ve ever asked the question, should I build a semantic model in Power BI or in Analysis Services (SSAS) Tabular, I’m here to give you some things to consider when making that decision. To begin, take a look at the image below which is a reference architecture from Microsoft.
This shows a typical data loading and BI pattern in which we have a data warehouse and then a data/semantic model built with Analysis Services and with Power BI used for visualizations on top of that model. You could use Power BI to do that modeling as creating relationships and writing custom calculations in DAX is using the same engine under the covers in Power BI as Analysis Services.
So, why would you choose to use either Power BI vs Analysis Services Tabular?
- First, consider the sheer size of your data.
- In the Standard Power BI versions you have a 1 GB limit for each data set. With Power BI Premium that does jump up to 10 GB.
- With SSAS Tabular there is no hard limit on the data set size; this is bound by the RAM on your server or Azure resources if you’re using a VM on the cloud.
- Note: This size limit is the compressed data size that would be stored in Power BI or Tabular.
- The way security is managed in Power BI and Tabular.
- In Power BI, in general the same people that are building the reports are the same people that are going to be implementing security features such as row level security, so it’s managed with report development.
- Analysis services is managed outside of report development.
- Using Power BI works in some organizations, in others they may separate that work where the database team may do some of that security management work and treat Analysis Services Tabular as a database. So, you’re doing some of that security work from a database perspective and separating that from your report development or visualizations.
- Additional separation of duties or roles in your organization.
- In Power BI the modeling, configurations and visualizations functionality is all being created in one file.
- Larger organizations or those that may have a harder separation of roles within the organization, may have a group focused more on the modeling or writing the custom calculations. And then a different group that is consuming those calculations to create visualizations.
- Shared Development
- This may be a big consideration for some.
- In Power BI you can work in a team environment on one Power BI model, but it can be difficult.
- With SSAS Tabular those models are built with Visual Studio. The idea of doing shared development in a Visual Studio environment is quite common.
- Reporting Tool Usage
- If you have a model built in Power BI, it can be consumed in Power BI or Excel.
- If you’re using other services like SSRS, Tableau or Spotfire for instance, you may want to consider using a Tabular model as those tools will be able to connect to that Tabular model.
- Cost
- Modeling in Power BI is no additional cost. When you pay for Power BI that includes visualizations, modeling, data storage, etc.
- If you use SSAS Tabular there are some additional licensing and infrastructure considerations to factor in.
These are all things to consider when deciding to use Power BI vs Analysis Services Tabular for your semantic models. If you have questions on this topic, Power BI or anything Azure related, we’d be glad to help.
Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or [email protected].
