Forum Discussion
Is PowerBI sending the SQL Queries with nolock?
Hi everybody,
I would like to know if PowerBI is using nolocks for it's SQL queries?
Here is how one of the queries looks that PowerBI is sending to a SQL database:
select [_].[FactOrderId],
[_].[DocumentFooter],
[_].[Discount],
[_].[GrossPrice],
[_].[GrossDocumentFooter],
[_].[GrossDiscount],
[_].[GrossPriceForeignCurrency],
[_].[GrossDocumentFooterForeignCurrency],
[_].[DiscountForeignCurrency],
[_].[GrossDiscountForeignCurrency],
[_].[DocumentFooterForeignCurrency],
[_].[NetPrice],
[_].[NetPriceForeignCurrency],
[_].[Currency],
[_].[ProcessId],
[_].[DimCompanyId],
[_].[TransactionDateDimCalendarId],
[_].[TransactionDateDimTimeId],
[_].[DocumentNo],
[_].[ReferenceDocumentId],
[_].[DimItemId],
[_].[InvoiceDimCountryId],
[_].[DeliveryDimCountryId],
[_].[CustomerID],
[_].[Quantity],
[_].[DocumentItemPosition],
[_].[DimReasonForRejectionId],
[_].[MEK_WE],
[_].[MEK_Hedging],
[_].[MEK_Plan],
[_].[DimOrderReasonId],
[_].[DimStorageLocationId],
[_].[DimItemGroupId],
[_].[DimDistributionChannelId],
[_].[LastUpdate],
[_].[DimCarrierId],
[_].[DimTransactionTypeId],
[_].[is_valid_ProcessId],
[_].[is_valid_ProcessPath],
[_].[CustomerReference]
from [sales].[tFactOrder] as [_]
where ([_].[LastUpdate] > convert(datetime2, '2021-01-01 00:00:00') and [_].[LastUpdate] <= convert(datetime2, '2021-12-11 00:00:00')) and [_].[TransactionDateDimCalendarId] >= 20211001
3 Replies
- nitishsh91Solution Supplier
Hi Anonymous
I saw this as an idea submitted but I don't think anything has been done on it yet:
https://ideas.powerbi.com/ideas/idea/?ideaid=3498799e-d40a-40e8-9b56-e47ff33653ec
Also it will be better to create a view in the system and using nolock over there and then use it in your report or you write the sql query yourself specifying nolock; its better to use nolock in your queries then letting PowerB drive it.
- AnonymousNot applicable
Hi nitishsh91,
thanks for the info.
My issue with writing my query myself in PowerBI is that the incremental load isn't working then. Is there a solution for this?
Thanks
mikro
- nitishsh91Solution Supplier
Sorry didn't understand incremental load thing in this regard.
IF you write the query and specify the date till which you need the data then it will be refreshed everyday or if you have a direct query then it will fetch the live results too.
Why do you need incremental refresh?
If that's a necessity then its possible to achieve incremental refresh too, check out below link:
https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview