PowerBI Direct Query to SSAS

[Anonymous]

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 SQL Server and some flat files as sources.

I have PowerBI connect edthrough DirectQuery to SSAS Tabular.

Is there any difference on performance on where I create the measures ? PowerBI vs the Model directly?

Thanks in advance!

[DChase]

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?