Forum Discussion
Power BI Create Dataflow with Big Size Data
The sql query itself usually takes 1.5h to finish runningMake it run (much, much) faster. Indexes, statistics etc. There is no reason for such a small amount of rows to take 1.5 hours.
then in Power Query join/combine the tables to get the final output data,Do not do that. Merges are extremely expensive. Load the data independently and the combine it in the Semantic Model data model.
Initially we need the previous 90 days data, so Ive also tried to slice the dataset into 15 days each batch, but still took too long to load and failed.Read about incremental refresh, and especially about bootstrapping (preparing partitions without filling them right away).
Hi robotbi ,
I’d like to acknowledge the valuable input provided by lbendlin . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue
Incremental Refresh and Real-Time Data for Semantic Models in Power BI provides an effective way to handle dynamic data and improve model refresh performance. By automating partition creation and management, incremental refresh reduces the amount of data that needs to be refreshed and allows the inclusion of real-time data.
After applying filters and loading a subset of data into the model, an incremental refresh strategy can be defined for the table. After publishing the model to the service, the service will use the policy to create and manage table partitions and perform refresh operations. To define a policy, specify the required and optional settings using the Incremental Refresh and Live Data dialogue box.
More details can be found in the documentation:
Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The sql query itself usually takes 1.5h to finish runningMake it run (much, much) faster. Indexes, statistics etc. There is no reason for such a small amount of rows to take 1.5 hours.
then in Power Query join/combine the tables to get the final output data,Do not do that. Merges are extremely expensive. Load the data independently and the combine it in the Semantic Model data model.
Initially we need the previous 90 days data, so Ive also tried to slice the dataset into 15 days each batch, but still took too long to load and failed.Read about incremental refresh, and especially about bootstrapping (preparing partitions without filling them right away).