Prepare - Fix performance issues Flashcards
What is performance issues in Power BI?
Report: speed at which reports run (e.g., how quick tables interact or filters work)
Query: speed at which queries run
What is query performance?
How fast a query performs a step (how fast it takes to retrieve and transform tables)
How to optimise performance for each source?
Power BI can query data from different sources and you will need to adopt query performance tuning techniques for each source
What are the two options to increase query performance?
Query folding
Query diagnostics
What are the benefits of query folding?
More efficiency in refreshes: When you import data tables by using query folding, Power BI is better able to allocate resources and refresh the data faster because Power BI does not have to run through each transformation locally.
Automatic compatibility All DirectQuery and Dual storage mode data sources must have the back-end server processing abilities to create a direct connection, which means that query folding is an automatic capability that you can use.
N.b.= Data refresh more efficient in terms of resource utilisation and refresh duration
What relational database query actions can be folded?
If all transformations can be reduced to a single Select statement, then query folding can occur.
How to check if a query is folded?
Check steps to view native queries
While query folding is one option to optimize performance when retrieving, importing, and preparing data, another option is query diagnostics
What is Query diagnostics?
A feature which measures query performance
Why is query diagnostics important?
So you can determine bottlenecks when loading and transforming your data
How to use query diagnostics?
Start diagnostics
Stop diagnostics
View query steps
What are some other techniques to optimise performance?
Process as much data at native data source (prepare it)
Use native SQL queries
Separate data and time if bound together: if tables have column with time and date bound together make sure you separate them
How does query folding work?
The Power Query mashup engine strives to achieve query folding whenever possible for reasons of resource efficiency.
Query folding reduces Power Query mashup engines work as the transformations are pushed to the data source.
Power Query leverages the capabilities of the robust relational database engines, that are built to cope with large volumes of data in the most efficient way.
Cannot be achieved = when query folding cannot be achieved—either partially or fully—the Power Query mashup engine must compensate by processing data transformations itself. This process can involve retrieving source query results, which for large datasets is very resource intensive and slow.
Can be achieved = significantly more efficient in terms of refresh duration and resource utilisation.
How does query folding differ for different?
DirectQuery or Dual = for these storage mode tables Power Query queries MUST have Query Folding
Import = for import tables it MAY be possible to achieve Query Folding
How does query folding impact data modelling?
Import mode: data refresh will take place efficiently for import model tables in terms of resource utilisation and refresh duration (e.g. quicker refresh time)
DirectQuery or Dual: Each DirectQuery model tables MUST be based on a Power Query query that can be folded
Incremental refresh: query folding makes incremental data refresh efficient in terms of resource utilisation and refresh duration. IF query folding can’t be achieved then Power Mashup will have to retrieve all rows to determine incremental changes
What is query performance best practice?
Try to achieve efficiency in your data model designs by ensuring query folding occurs where possible.
Delegate as much processing to the data source as possible = discover which step is preventing query folding
Use a native SQL query =
Prepare and transformation data in the source = (a) use data warehouses with prepared data or (b) make sure you do as much cleaning in the data source (e.g. clean the excel first)
