I haven't been able to find the answers to a couple NOLOCK questions:
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.
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.
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?