Forum Discussion
data flow performance optimization thanks to ingestion and transformation separation?
Hi
I read some blogs about performance optimisation of data flows.
And one thing which was poping up, was that good practice would be to devide the data ingestion and data transformation into different dataflows. Because like that the compute engine is able to unleash its full power.
But I thought that the power query steps which can be pushed to the data source ("step will be evaulated in data source") anyway are carried out on the data soure i.e. SQL server. Or does the whole querry need to have this status, that it will be pushed to the data source?
So my question would be what are the requirements that a query or part of a query are carried out on the data source and therefore are as efficient as possible?
Thank you!
Best,
Matthias
Hi Anonymous ,
I'm afraid I can't give a better suggestion than the way mentioned in the official documentation.
The first step to improve long refresh durations for dataflows is to build dataflows according to our best practices. Notable patterns include the following:
- Use linked entities for data that can be used later in other transformations
- Use computed entities to cache data, reducing data loading and data ingestion burden on source systems
- Split data into staging dataflows and transformation dataflows, separating the ETL (extract, transform, load) into different dataflows
- Optimize expanding table operations
- Follow guidance for complex dataflows
Next, it can help to evaluate whether you can use incremental refresh.Using incremental refresh can improve performance.
Finally, consider optimizing your environment. You can optimize the Power BI environment by scaling up your capacity, right-sizing data gateways, and reducing network latency .
You may also using the Compute Engine to maximize performance.
For more information，please refer to Understanding and optimizing dataflows refresh
Best Regards,
Community Support Team _ CaitlynIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
I'm afraid I can't give a better suggestion than the way mentioned in the official documentation.
The first step to improve long refresh durations for dataflows is to build dataflows according to our best practices. Notable patterns include the following:
- Use linked entities for data that can be used later in other transformations
- Use computed entities to cache data, reducing data loading and data ingestion burden on source systems
- Split data into staging dataflows and transformation dataflows, separating the ETL (extract, transform, load) into different dataflows
- Optimize expanding table operations
- Follow guidance for complex dataflows
Next, it can help to evaluate whether you can use incremental refresh.Using incremental refresh can improve performance.
Finally, consider optimizing your environment. You can optimize the Power BI environment by scaling up your capacity, right-sizing data gateways, and reducing network latency .
You may also using the Compute Engine to maximize performance.
For more information，please refer to Understanding and optimizing dataflows refresh
Best Regards,
Community Support Team _ CaitlynIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.