Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

PowerBI Direct Query to SSAS

Hello everyone! Maybe a stupid question but here goes: I have quite a big model in SSAS, around 7 Tables, 3 of them FactTables where each table can reach around 700M rows and counting, having S...
Need Help
DChase's avatar
DChase
New Member
8 years ago

I have the same question.

 

We have a (3) Billion Row fact table attached to 1 million Products.  We have been testing 3 modes:

 

1) SSAS Tabular feeding PowerBI Desktop.  DAX is authored in SSDT (Tabular) enviroment.  Feeds Excel and PowerBI fairly well.  Response times are as quick as 2 seconds for simple sums.  5 seconds for Time Variance e.g. Prior Month Calculations.

 

2) SQL Data Warehouse in Azure.   Using PowerBI Desktop with Direct Query.  This appears Slower, on the order of a minute(s).  The DAX has been written in  PowerBI desktop as required.

 

3) Azure Analysis Services.  Using PowerBI Desktop.  The DAX has been written in PowerBI Desktop.  The queries are running but slower than Option #1. 

 

We haven't tested Option #4 - Writing the DAX in Azure Analysis Services and feeding the DAX measures directly to PowerBI and/or Excel.  The theory is that this should be faster than #1.

 

Does anyone have experience on the speed of writing DAX in the Analysis Service Engine vs. writing them directly in PowerBI desktop and then calling the results from the Analysis Services engine?