Maximum performance with large datasets on Azure

[Anonymous]

Hi all,

I am trying to achieve the best performance with large datasets in reports. For example: I have reports that use some visuals with aggregated data, but my users also want to get the details. These details are coming from datasets containing 100M+ rows.

Importing this into PowerBI is ofcourse not an option, so I came up with the following ideas:

1. Use Azure Analysis Services
   Putting the datasets in tabular models will consume a lot of memory however, this requires spending a lot on a higher ranged tier (S2 or above, €2,500 per month or higher)
   
   
2. Or use Azure SQL Data Warehouse with DirectQuery
   Did some testing with the basic tier DW100c (€900 per month) and I achieved a performance of 10 seconds searching a 100M rows table. The report only contained a search box and a table. The dataset in SQLDWH was hash distributed and had a columnstore index.
   What I noticed however is that the query PowerBI creates is far more complex than a simple 'SELECT xx,xx FROM WHERE xx = xx' (this executes in 1 second by the way on the server itself). Power BI for example creates some joining, grouping, ordering etc, which are not really necessary for my use-case. Is there a way to optimize this?
3. Or use a Databricks cluster with Spark and DirectQuery
   Also an option but more complex and a continuous running cluster will also cost at least €730 per month.
   
   

I am wondering what the best option is, does anyone have any experience with these options already? Other Azure options are also welcome!

I tend to use SQL DWH though, but can I further optimize this without scaling up?

Thanks!

[parry2k]

@Anonymous did you explored composite model?