Is PowerBI sending the SQL Queries with nolock?

[mikro87]

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

[mikro87]

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

[nitishsh91]

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

[nitishsh91]

Hi @mikro87 

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.