Working with huge data(Pro license)

[Anonymous]

Hi Experts,

What optimization methods does power bi have when dealing with huge data? When I connect to Azure DB, the running speed of SQL script is very slow. When SQL completed, it will also be very slow to load data into PBI. How to solve this issue(Pro license)?

[BA_Pete]

Hi @Anonymous ,

When dealing with huge data the (only) way to go is Direct Query Composite model with aggregation tables:

https://radacad.com/power-bi-fast-and-furious-with-aggregations 

Pete

[Anonymous]

Hi @BA_Pete ,

Thanks for your reply,Now my SQL script runs very slowly. When the preview table is loaded, the apply query often causes the PBI to freeze. Do you have any good suggestions?

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Really difficult for me to give detailed help on this as there's hundreds of things that could be causing a slow query/acquisition process.

I guess the first places to start would be:

1) Optimise your SQL. I'm afraid that this suggestion is proportionally as vague as the description of the problem.

2) Ensure that query folding is enabled for your native query:

Value.NativeQuery(
    previousStep,
    "select * from table etc...",
    null,
    [EnableFolding=true]    // add this argument if it isn't currently present
)

3) Aggregate the data in the SQL as highly as possible for the expected use.

4) Remove any columns from the query that are not absolutely necessary. Recreate them in DAX if required.

5) Fully prepare the data at source via views then pass straight to the data model with Direct Query.

If you can provide more detail around your SQL query, the number of tables and their structures/row counts, your subsequent Power Query transformations, and your end use-case I may be able to provide more detail in my suggestions.

Pete