cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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
Highlighted
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.

Highlighted
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...

Highlighted
Resolver I
Resolver I

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
Helper II
Helper II

Re: Data Connections with NOLOCK

Can anyone confirm the use of NOLOCK solving this issue?

Highlighted
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.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors