Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi everybody,
I wanted to create an ETL process using Data Flows in a Premium per User workspace. I tried to stick to Best Practices, so I created one Extraction Dataflow per Data Source and Transformation Dataflows using Linked Tables. Since we have plenty of sources, I tried to create an easy to understand model (Please ignore that "Extract - Data Part 2" has two sources, it is just my Demo data)
My final Data Flow - Transform and Load - is a merge of the two previous Dataflows.
When I refresh my two Extraction Data Flows at the same time, they try both to refresh the downstream data flows. One of them will eventually throw an Error "Cannot acquire lock for model...".
My ideas to avoid this:
Do you have any ideas, links etc. about best practices for ETL in this case?
Thanks in advance
Patrick
Solved! Go to Solution.
Hi @Anonymous,
The error ‘Cannot acquire lock for model… because it is currently in use’ occurs when the dataflow has been blocked which is caused by the refresh logic of linked dataflows. For refresh logic of linked dataflows, you could refer to Link entities between dataflows - Power Query | Microsoft Docs. Here is an example for state the cause. You create a dataflow A. Then you create a dataflow B which connects to dataflow A. When you refresh dataflow B, dataflow A is not allowed to be changed or refreshed. If we change or refresh dataflow A when refreshing dataflow B, this will cause dataflow B to get inconsistent data. So to make sure the consistent, Power BI will block dataflow A from being refreshed or changed when dataflow B is refreshing.
To avoid such kind of issue, you could consider schedule the refresh of the dataflows per the refresh logic.
1 Put source dataflows and destination dataflows are in different workspaces, then source dataflows will be treated as the external data sources of destination dataflows. Then, you could set scheduled refresh for them separately. But you need make sure the refresh of source dataflows will be completed before destination dataflows starts to refresh.
2 If you put source dataflows and destination dataflows in the same workspace, only set scheduled refresh on source dataflows since it will event automatically trigger a refresh process for dependent entities in all destination dataflows in the same workspace.
Once you get this issue, you could check workspace lineage view then check if there’s overlap between dataflow and linked dataflow. If there’s no refresh overlap and dataflow refreshed failed consistently, please consider raising support ticket.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Hi @Anonymous,
The error ‘Cannot acquire lock for model… because it is currently in use’ occurs when the dataflow has been blocked which is caused by the refresh logic of linked dataflows. For refresh logic of linked dataflows, you could refer to Link entities between dataflows - Power Query | Microsoft Docs. Here is an example for state the cause. You create a dataflow A. Then you create a dataflow B which connects to dataflow A. When you refresh dataflow B, dataflow A is not allowed to be changed or refreshed. If we change or refresh dataflow A when refreshing dataflow B, this will cause dataflow B to get inconsistent data. So to make sure the consistent, Power BI will block dataflow A from being refreshed or changed when dataflow B is refreshing.
To avoid such kind of issue, you could consider schedule the refresh of the dataflows per the refresh logic.
1 Put source dataflows and destination dataflows are in different workspaces, then source dataflows will be treated as the external data sources of destination dataflows. Then, you could set scheduled refresh for them separately. But you need make sure the refresh of source dataflows will be completed before destination dataflows starts to refresh.
2 If you put source dataflows and destination dataflows in the same workspace, only set scheduled refresh on source dataflows since it will event automatically trigger a refresh process for dependent entities in all destination dataflows in the same workspace.
Once you get this issue, you could check workspace lineage view then check if there’s overlap between dataflow and linked dataflow. If there’s no refresh overlap and dataflow refreshed failed consistently, please consider raising support ticket.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.