cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dave Frequent Visitor
Frequent Visitor

Data Connections with NOLOCK

I haven't been able to find the answers to a couple NOLOCK questions:

 

  1. 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? 
  2. 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 5
NickiT Frequent Visitor
Frequent Visitor

Re: Data Connections with NOLOCK

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.

NickiT Frequent Visitor
Frequent Visitor

Re: Data Connections with NOLOCK

I've created a request through the ideas forum. Worth commenting/liking it if you want to see some development on this.

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/12956667-automatic-use-of-nolocks...

robertsbd Member
Member

Re: Data Connections with NOLOCK

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?

Highlighted
dataviznz Regular Visitor
Regular Visitor

Re: Data Connections with NOLOCK

Can anyone confirm the use of NOLOCK solving this issue?

ACT Frequent Visitor
Frequent Visitor

Re: Data Connections with NOLOCK

The comments on this article suggest that PowerBI does use locks...but yeah, I'd like to know more about the specifics as well.