2

Suppose you have 10 pretty big fact tables (each 50-100 GBs) that should be queried with Power BI. They doesn't fit into Azure Analysis Services RAM (reasonable price). So in order to use tabular model and AAS you have stay with the following schema:

(1) Power BI Desktop -> Azure Analysis Services -> [DirectQuery] -> SQL Database

But as far as I know from this article, AAS tabular model doesn't cache any aggregated results (means won't imply any additional performance optimizations). Moreover, AFAIK, Power BI (PowerPivot) already has embedded AAS.

As alternative, I can query SQL datasource directly from Power BI:

(2) Power BI Desktop -> [DirectQuery] -> SQL Database


Does the 1st schema (using AAS) provide any performance benefits over the 2nd schema (not using AAS)?

P.S. My question isn't about pros and cons of semantic layer, for that see this article. This question isn't the same as this question, because it's asking only about performance aspect of ASS DirectQuery.

VB_
  • 45,112
  • 42
  • 145
  • 293
  • 1
    I'm not here to answer the question since I don't know AAS, but some months ago PowerBI released (aggregations)[https://learn.microsoft.com/en-us/power-bi/desktop-aggregations], which are aggregated tables hidden in the model. Those might help since you have huge tables. – Giovanni Luisotto Feb 06 '20 at 08:32
  • Good point @GiovanniLuisotto! @VB do you really need all the 50 GB of raw information? Can't you perform some meaningful aggregation in the back-end? Then, AAS compression will further reduce your tables size. – Seymour Apr 09 '20 at 10:21
  • 1
    @Seymour unfortunatelly some reports are build on transactional grain data. But we found a solution (not using AAS at all) - with dimensional model it's possible to built report on both: aggregated table and transactional table. Means very few percentage of requests are going to transactional level table. – VB_ Apr 09 '20 at 13:36
  • What tool did you use? – Seymour Apr 09 '20 at 13:38
  • @Seymour Power BI if you meant that. – VB_ Apr 09 '20 at 13:47

1 Answers1

2

The performance benefits will require testing depending on your work load, and other factors.

Caveat (This answer is based on my own and my colleagues experience and testing)

Service Standard: From a service point of view, the main difference will be between Azure Analysis Services (AAS) and the Power BI Service (PBIS), is that AAS is a known set of hardware/performance, where as PBIS is a shared capacity, and can suffer from 'noisy neighbour' issues, if another customer is on the same cluster and using it heavily it will have an impact on you report performance.

Performance: Essentially, PBI and AAS are doing the same thing, translating DAX to a SQL query and then returning the data. From my experience of building PBI and AAS in terms of performance there isn't much difference between the two. The main issue that tends to be the bottleneck is using a gateway to an on-prem SQL and the capacity of the SQL Server either on-prem or in the cloud. For example for better performance you can use Clustered Column Indexes to bring for example the fact tables into memory, and it is easier to increase/decrease the Azure SQL Database DTU's/capacity during business hours.

At the moment AAS doesn't have the Aggregated Mode that PBI does, which can reduce the number of queries being sent back and is a bit quicker, but also has the drawback of they have to be refreshed at some point.

I would recommend testing using for example DAX Studio to see what variability you may get in performance. My own testing has shown differences in the millisecond to 1 second range in favour of AAS.

However the benefits of the semantic layer is a powerful consideration

Connections: AAS supports other connections such as Excel, SSMS, SSRS etc better than Power BI. Excel can connect to Power BI models with an additional plugin.

Maintainability: Maintaining the data model across its life-cycle is a lot easier to do in Visual Studio/SSDT with Azure DevOps, Git etc. than it is in Power BI Desktop. With AAS you can also use Calculation Groups for Time Intelligence calculations, rather than multiple measure or workarounds for YTD, Parallel Period, MTD etc

If there was slightly better performance in a pure Power BI approach I would still use AAS due to the benefits of the none performance factors, it would have to show significantly improved performance before switching.

Hope that helps

Jon
  • 4,593
  • 3
  • 13
  • 33