Data Modelling In Power BI Or Azure Analysis Services

When to use Azure Analysis Services (AAS) instead of Power BI’s inbuilt data modelling capability?

One of the powerful features of Power BI, making it superior to other BI tools, is its data modelling capability. You can create semantic layer by creating relationship between different tables, building hierarchies, creating calculated fields with ease. Great thing is all this comes free of cost with Power BI Desktop.

It has some limitations though which prompts to use an external data modelling environment like SSAS or Azure Analysis Service. Let us look at the scenarios when you would like to use these services:

1. Your data size is larger than 1 GB (10 GB for Power BI Premium) – Power BI Pro has a limitation of file size of 1 GB. This limit goes up to 10 GB in case of Power BI Premium but anything beyond that requires data modelling to be handled externally.
2. You want to use your enterprise data models to visualize data with non-Microsoft tools like Tableau – one of the great features of Power BI service is that you can create dataset for your enterprise data models created in Power BI and use these datasets to visualize data in different Power BI reports or even in Excel. But these datasets cannot be used by non-Microsoft services like Tableau. For this you need to use Azure Analysis Service.
3. You do not want to be limited for numbers of refreshes in a day -Power BI Pro account offers 8 refreshes in a day while Power BI Premium offers 48 refreshes in a day. What if you want more refreshes? Azure Analysis Service is the option. You can set up automatic refreshes as many times as you want. You just have to write few lines of code and pay the charges for using the service.
4. You want to separate the Data Modelling and Reporting responsibilities – In some cases you want to separate these two responsibilities e.g., to maintain Row Level Security at enterprise level. You will have to use Azure Analysis Services for such scenarios.

Everything is great then why not use AAS for all data models? There are some cons to this approach which makes Power BI as a preferred choice for data modelling for business users.

1. You have to use Visual Studio environment to create the data model for AAS. This can be daunting for business users.
2. Additional cost – while you get data modelling capability free of charge with Power BI Desktop, you must pay for Azure Analysis Services based on the service tier. It starts with $0.43 per hour for basic tier. Refer this for latest pricing.