Planning, forecasting & simulation are some of our key focus areas. Recently, we did some benchmarks what the optimal configuration (Azure SQL Table / Azure SQL Table in Power BI / SSAS connected to Azure SQL with Direct Query) for typical planning scenarios in Excel is. Acterys (a solution by one of our group companies) enables users to write back directly from Excel Pivot Tables (Acterys Excel Add-in) and in Power BI (Acterys Matrix custom visual). In this kind of planning and forecasting users require immediate feedback on the results of changes in a multi-dimensional data model, typically aggregating over multiple hierarchies.
For this test we used a typical financial data model that Acterys automatically generates with data from cloud-based accounting systems (In this case using Xero.com):
The data model is configured as a relational star schema with dimension tables linked via ID columns. Typically, the most effective setup for this requirement. The data is stored in an Azure SQL database. The main fact table as 1,600 records, none of the dimension tables more than 200 records.
The three configurations in Excel that are using this same data model were:
• Azure Analysis Services (B1) connected to Azure SQL in Direct Query
• Power BI with an Azure SQL Direct Query Connection using “Analyse in Excel” (Pro license) and
• Excel Data Model (PowerPivot) (O365)
This was our test report:
The scenario is assuming a challenging setting with the database in an US Azure data center and the client in Australia.
The results were quite staggering:
SSAS and Power BI perform nearly equally well. The Excel data model (Power Pivot) shows a massive lag: the first recalc after opening the relevant workbook takes 10x as long as in the Analysis Services based models (SSAS and PBI are using both Analysis Services). The gap for recalculations after the initial opening, is even worse at more than 20x slower.
In conclusion: An Excel data model (Power Pivot) based approach is currently not suitable for real-time update requirements. One of the factors here is that Power Pivot does not support Direct Query and has to load the entire table in the local workbook model. Microsoft: any plans for supporting SQL DQ in Excel?