Forum Discussion
Microsoft EmployeeShare your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)
It would be great if there were an officially sanctioned and clear approach (or more than one) to managing large datasets (+30GB) using directquery (or not). This is especially the case because it seems like directquery is currently the best way to deal with huge datasets in PBI. Haven't been able to load huge tables to dataflows, datamart, or blob without timeout errors, etc. I am fully in the MS ecosystem, so I connect to Azure SQL Database using directquery. The tables in the DB are indexed (columnstore) and I'm using auto aggregation. I'm pretty sure that's all I can do (or mostly all), but I'm not entirely sure. Because it's a little bit of witchcraft. Would love a video or post like this: dealing with huge datasets from SQL Server et al to PBI.
This thread isn't related to large models (import or direct query). This is related to connecting to Analysis Services in a Direct Query mode rather than a Live Connection mode.
It looks like there are a number of posts under the desktop section of the sight that maybe helpful:
As a Consultant myself working with large models (primarily against Snowflake), the advice is to import the data into the model in the first place. with large data this requires implementing incremental refresh. (Note that a P1 capacity has a model size limit of 25GB Capacity and SKUs in Power BI embedded analytics - Power BI | Microsoft Docs.)
If you can't scale up the capacity, or reduce data size through optimisation and rationalisation, then utilise direct query with aggregations; auto-aggregations (which may be helpful dependant on the repetitive nature of the queries) or manual aggregations.
There is plenty of material on these topics if you Bing search. GuyInACube has some good material as a starting point:
- https://www.youtube.com/watch?v=s0j6d3UAw9U&list=PLv2BtOtLblH3lwpQ5NBq6kD6fsVziO9sE
- https://www.youtube.com/watch?v=EhGF372t0sU&list=PLv2BtOtLblH0cQ7rWV2SVLGoplKdy0LtD