Forum Discussion
Data Connections with NOLOCK
I haven't been able to find the answers to a couple NOLOCK questions:
- SQL Connection: I know that when you establish a SQL connection in PBI Desktop, you can specifiy a query in which you can add NOLOCK. However, if you do not choose to define a specific SQL query, does Power BI use NOLOCK by default when it pulls the data?
- ODATA Connection: Is it possible to set NOLOCK in an ODATA connection?
The reason I'm wondering about this is because I'm looking to pull from a production DB for now (planning on a DW in the future) and don't want to lock some of the bigger tables. Also, I'd actually prefer to use the ODATA connection. From what I can tell, PBI pulls down all of the data with each refresh as it doesn't have any way to identify the rows that were modified since the previous refresh.
Any direction on this would be much appreciated.
5 Replies
In terms of connecting to a SQL table, I'm interested in exactly the same thing regarding the use of NOLOCKS. Even using a Data warehouse, we don't just update it once overnight, so knowing NOLOCKS are being used is really important. It's likely to cause performance issues as we roll Power BI out into the business.
More and more businesses are moving away from a single overnight refresh so I can imagine this becoming more of an issue.
I've created a request through the ideas forum. Worth commenting/liking it if you want to see some development on this.
Hi,
Similar thoughts to yourselves.
I had a situation where Power BI locked up one of our databases. I am assuming that if I preface any of my queries with SELECT * FROM X WITH(NOLOCK) as I bring the data into Power Query before doing whatever else transformations that this should get around this issue?
Can anyone confirm the use of NOLOCK solving this issue?
