Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

[tessahurr]

Hit Reply and let us know what you think of the DirectQuery for Power BI datasets and Azure Analysis Services.  To learn more about this feature, please visit this blog post or our documentation.

Here are some areas that we'd like to hear about in particular:

• Performance
• Query editor experience--the remote model query doesn't show up in the query editor and only in the data source settings dialog. What are your thoughts?
• Navigator experience
• Thoughts around governance and permissions for models that leverage this feature
• Nesting models, i.e. building a composite model on top of a composite model
• Automatic page refresh for live connect in composite models

Thanks and we look forward to hearing your feedback!

- The Power BI Modeling Team

[ludovicBI]

Hello,
We have build some Datasets with the Fact tables as import and the Dimensions as Direct Query from another PBI Dataset (DQ over AS).
The performance as very bad when we build data visualisation.
We would like to understand the performance of DQ over AS and we do not find a lot of documentation. Can we assume that the performance should be is equal to DQ "classic" or equal to Import (as it is also a pbi table).
For now it is more equal to a classic DQ and therefore bad performance for dimensions with more than a few hundreds of lines..
I am wondering if it will be possible when we use DQ over AS to have the option to get a "local" copy of meta data *AND* also data in the model to get better perfiormance .
something similar that broadcasting a table in databricks for exemple.
That would really be awesome for performance as the data of the table will be part of the model and the copy/synchronisation will be handle by power bi. In our hand we need the dimension to be updated only in the remote dataset.

Any thoughs on this ?
Any links for performance for DQ over AS will be great

[RajeshSethi]

We published the pbix file after adding another 'power bi dataset' (from within the same workspace) using the Home> Datahub Items > Power BI datasets. The report seems to work fine in the services browser. However, the report does not seem to load(and blank page is showing) in the organizational portal where it is embedded (using service principal). This required a preview feature to be enabled: DirectQuery for PBI Datasets and AS.

[Ruchita]

Hi Rajesh,

Are you connecting to the dataset with Live connection or DirectQuery? With DirectQuery, there is a problem in embedding the report. 

[Ruchita]

The embedded Power BI is currently not supporting the DirectQuery to Power BI datasets. So I have to use the Import method or the Live Connection. But for some reports, I need to make a composite model for which DirectQuery is required. It would be great if that can be supported in future releases.

Thanks,

Ruchita Gupta

[jeroenterheerdt]

⏰This feature was officially marked as generally available yesterday! We want to thank everyone who provided feedback during the preview time, as it shaped the feature to what it is today. Thank you all. Please read the blog post here: https://powerbi.microsoft.com/blog/announcing-general-availability-for-composite-models-on-power-bi-...

[Jainam]

I am using DirectQuery in Combination with Power BI dataset.
I have defined the directqueries in power query. Now when i get PowerBI dataset, im facing this issue

Feedback Type:
Frown (Error)

Error Message:
Value cannot be null.
Parameter name: Data Source

Stack Trace:
System.ArgumentNullException
at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
at System.Windows.Forms.WindowsFormsSynchronizationContext.Send(SendOrPostCallback d, Object state)
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.AllowModalDialogs(Action action)
at Microsoft.PowerBI.Client.Windows.LiveConnectHelpers.LiveConnectionPropertiesValidator.TryGetValidLiveConnectionProperties(IPowerBIWindowService windowService, ILiveConnectionProperties connectionProperties, Report report, ILiveConnectionCompleter liveConnectionPropertiesCompleter, ILiveConnectionProperties& validLiveConnectionProperties, Boolean& resultIsSameConnection, ValidationStatus& validationStatus)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.AreAnalysisServicesConnectionPropertiesValid(IPowerBIWindowService windowService, Report report, ILiveConnectionProperties connectionProperties, AnalysisServicesConnectionMode connectionMode, ILiveConnectionCompleter completer, ILiveConnectionProperties& validConnectionProperties)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.ValidateAndCreateAnalysisServicesConnection(ILiveConnectionProperties connectionProperties, Boolean addToFormulaHistory, Boolean isExternalDataset)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.<>c__DisplayClass24_0.<<AddPowerBIDatahubSource>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__1.MoveNext()

Stack Trace Message:
Value cannot be null.
Parameter name: Data Source

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.PowerBI.Client.PowerBIUnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.PowerBI.Client.Windows.Utilities.PowerBIFormUnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__1.MoveNext()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
at System.Threading.Tasks.AwaitTaskContinuation.RunCallback(ContextCallback callback, Object state, Task& currentTask)
at System.Threading.Tasks.Task.FinishContinuations()
at System.Threading.Tasks.Task.Finish(Boolean bUserDelegateExecuted)
at System.Threading.Tasks.Task`1.TrySetException(Object exceptionObject)
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.SetException(Exception exception)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.<>c__DisplayClass24_0.<<AddPowerBIDatahubSource>b__0>d.MoveNext()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
at System.Threading.Tasks.AwaitTaskContinuation.RunCallback(ContextCallback callback, Object state, Task& currentTask)
at System.Threading.Tasks.Task.FinishContinuations()
at System.Threading.Tasks.Task`1.TrySetResult(TResult result)
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.SetResult(TResult result)
at Microsoft.PowerBI.Client.Windows.LiveConnectHelpers.PowerBIService.PowerBIServiceConnector.<SelectDatahubArtifactFromPowerBIService>d__14.MoveNext()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
at System.Threading.Tasks.AwaitTaskContinuation.RunCallback(ContextCallback callback, Object state, Task& currentTask)
at System.Threading.Tasks.Task.FinishContinuations()
at System.Threading.Tasks.Task`1.TrySetResult(TResult result)
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.SetResult(TResult result)
at Microsoft.PowerBI.Client.Windows.PowerBIService.Flows.AuthenticatedWebRequestor.<Execute>d__5`1.MoveNext()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_1.<Main>b__4()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.114.864.0","ModuleName":"Microsoft.PowerBI.Client.Windows.dll","Component":"Microsoft.PowerBI.Client.Windows.Services.UIBlockingService","Error":"System.ArgumentNullException","MethodDef":"AllowModalDialogs","ErrorOffset":"43"}

Snapshot Trace Logs:
C:\Users\bnxt_win_power_bi\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShotf7784200-4ceb-4e8c-8649-cf17490bfe45.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\bnxt_win_power_bi\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_compositeModelsOverAS
PBI_enableWebView2
PBI_sparklines
PBI_scorecardVisual
PBI_fieldParametersSuperSwitch
PBI_horizontalFusion
PBI_relationshipEditPane
PBI_setLabelOnExportPdf

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_NlToDax
PBI_optimizeTabRibbon
PBI_angularRls

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBIUserFeedbackServices_IsReported:
True

Formulas:

section Section1;

shared card = let
Source = Odbc.Query("dsn=prod_db", "select * from public.power_bi_card_dump() where updated_date>='2023-03-15 00:00:00.000';")
in
Source;

shared user = let
Source = Odbc.Query("dsn=prod_db", "select * from public.power_bi_user_dump() where updated_date >='2023-03-15 00:00:00.000';")
in
Source;

shared transaction = let
Source = Odbc.Query("dsn=prod_db", "select * from public.power_bi_transaction_dump() where created_date between '" & Text.From(RangeStart) & "' and '" & Text.From(RangeEnd) & "';")
in
Source;

shared RangeStart = #datetime(2023, 4, 1, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true];

shared RangeEnd = #datetime(2023, 4, 11, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true];

shared #"Sample File" = let
Source = Folder.Files("C:\Users\bnxt_win_power_bi\Documents\product integration\History data\card history"),
Navigation1 = Source{0}[Content]
in
Navigation1;

shared Parameter1 = #"Sample File" meta [IsParameterQuery=true, BinaryIdentifier=#"Sample File", Type="Binary", IsParameterQueryRequired=true];

shared #"Transform Sample File" = let
Source = Csv.Document(Parameter1,[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers";

[ FunctionQueryBinding = "{""exemplarFormulaName"":""Transform Sample File""}" ]shared #"Transform File" = let
Source = (Parameter1 as binary) => let
Source = Csv.Document(Parameter1,[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
in
Source;

shared #"Sample File (2)" = let
Source = Folder.Files("C:\Users\bnxt_win_power_bi\Documents\product integration\History data\user history"),
Navigation1 = Source{0}[Content]
in
Navigation1;

shared Parameter2 = #"Sample File (2)" meta [IsParameterQuery=true, BinaryIdentifier=#"Sample File (2)", Type="Binary", IsParameterQueryRequired=true];

shared #"Transform Sample File (2)" = let
Source = Csv.Document(Parameter2,[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers";

[ FunctionQueryBinding = "{""exemplarFormulaName"":""Transform Sample File (2)""}" ]shared #"Transform File (2)" = let
Source = (Parameter2 as binary) => let
Source = Csv.Document(Parameter2,[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
in
Source;

shared #"Sample File (3)" = let
Source = Folder.Files("C:\Users\bnxt_win_power_bi\Documents\product integration\History data\txn history"),
Navigation1 = Source{0}[Content]
in
Navigation1;

shared Parameter3 = #"Sample File (3)" meta [IsParameterQuery=true, BinaryIdentifier=#"Sample File (3)", Type="Binary", IsParameterQueryRequired=true];

shared #"Transform Sample File (3)" = let
Source = Csv.Document(Parameter3,[Delimiter=",", Columns=22, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers";

[ FunctionQueryBinding = "{""exemplarFormulaName"":""Transform Sample File (3)""}" ]shared #"Transform File (3)" = let
Source = (Parameter3 as binary) => let
Source = Csv.Document(Parameter3,[Delimiter=",", Columns=22, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
in
Source;

shared #"2021 error" = let
Source = Odbc.Query("dsn=prod_db", "SELECT * FROM public.power_bi_2021_report('" & Text.From(RangeStart) & "','" & Text.From(RangeEnd) & "')"),
#"Removed Duplicates" = Table.Distinct(Source, {"id"}),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Error Reason", each if (Text.Contains([response], "api Request Failed", Comparer.OrdinalIgnoreCase)) then "API Request Failed"
else if (Text.Contains([response], "issuer bank or payment service provider declined the transaction", Comparer.OrdinalIgnoreCase)) then "Issuer Bank Declined" else if (Text.Contains([response], "instrument not found for customer", Comparer.OrdinalIgnoreCase)) then "Instrument Not Found" else if (Text.Contains([response], "card network failed to generate cryptogram for the saved instrument", Comparer.OrdinalIgnoreCase)) then "Failed to Generate Cryptogram" else if (Text.Contains([response], "Transaction not permitted to cardholder", Comparer.OrdinalIgnoreCase)) then "Transaction not permitted to Cardholder" else if (Text.Contains([response], "network infrastructure error", Comparer.OrdinalIgnoreCase)) then "Network Infrastructure Error" else if (Text.Contains([response], "Token bin for the card network token passed in the request could not be identified", Comparer.OrdinalIgnoreCase)) then "Token Bin-Card Bin Mapping Issue" else if (Text.Contains([response], "Do not honour", Comparer.OrdinalIgnoreCase)) then "Do not honour" else if (Text.Contains([response], "Card holder name has invalid characters", Comparer.OrdinalIgnoreCase)) then "Invalid Characters in Card Name" else if (Text.Contains([response], "card_cvv", Comparer.OrdinalIgnoreCase)) then "Wrong CVV format" else if (Text.Contains([response], "Too many requests from your app", Comparer.OrdinalIgnoreCase)) then "Rate limit - Too Many Request from App" else if (Text.Contains([response], "internal Server Error", Comparer.OrdinalIgnoreCase)) then "Internal Server Error" else if (Text.Contains([response], "mode not enabled", Comparer.OrdinalIgnoreCase)) then "Unsupported Payment Method - Mode not enabled" else if (Text.Contains([response], "Payment has failed", Comparer.OrdinalIgnoreCase)) then "Payment has failed - API Error" else if (Text.Contains([response], "s2s_enabled", Comparer.OrdinalIgnoreCase)) then "Order Pay Feature not enabled" else null)
in
#"Added Custom";

[lfp93pbi]

Created a report based on the composite model from a PBI dataset and a Sharepoint import. Once it is published on PBI Service the refresh of the composite dataset loses all the data from the sharepoint connection mantaining only the data from the underlying DirectQuery connection.

If I download the report after the dataset refresh on PBI Service, the data sources show only the live connection to the original dataset, it seems as if the refresh of the composite dataset will revert the feature of DirectQuery for PBI datasets to Live Connection.

Harsh lesson for to learn the day of deployment

[jeroenterheerdt]

Hi @lfp93pbi that is not supposed to happen. Can you please open an issue on issues.powerbi.com or reach out to support in another way so we can collect details from you?

[jpbarnard]

Useful feature, which avoids duplication of data flow and data model parts. However, a big spanner in the works is the lack of support for authentication by service principal.

We use Power BI Embedded in a big way and this shortcoming disables the feature for us. A report based upon this feature fails to display any data, since our web service principal fails to authenticate to the chained data sets.

Please, fix this lack of support for service principal.

[jeroenterheerdt]

Hi @jpbarnard thanks for your feedback. Unfortunately, we are not planning to support service principals, but please feel free to vote for it on ideas.powerbi.com. That will help us prioritize it.

[Jasonx123]

is this feature already suported?

[jpbarnard]

Well, that decision renders this feature useless for a substantial user community. I cannot think of a single, good technical reason why service principal authentication cannot be supported. This seems to be purely a marketing decision, which is regrettable. We are not impressed.

[Jasonx123]

is this feature already supported?

[Christophe93]

Hi dears

Any  GA date to give us  even  is not precise ,   please ? (Already on GA for tableau)  

All our data modeler are in a hurry  for this great feature (create transversal models from master datasets , golden  KPI  are safe & not duplicated  , possibility to create now cross ones , inherit dimensions RLS system etc …)

23/04/2023  still bugs for
-RLS  propagation issue  to composite model  from DQ model(s)

-Some columns format change automatically  during the  switch (Live DS --> DQ to DS) .. like dates format 

-Strangely some fact tables get automatic  dynamic M query parameters  after the switch !! ... or I'm dreaming 

DAX Queries performances sounds good 

[jeroenterheerdt]

I cannot give you a GA date here, but we are getting very close. The RLS "issue" you mentioned is something that we are not planning to add any time soon. If you are having issues with formats changing, please open an support ticket or use issues.powerbi.com to report this. Regarding the fact tables getting automatic dynamic M query parameters, not sure what you are seeing, but that does not ring a bell with me. Please also open a issue for this. thanks.

[Christophe93]

HI jeroenterheerdt

Sorry  no issue with  dynamic query M parameter  

Just with 2 fields  format who change during the swith 

[howellchrisj]

I apologize if this has been asked and answered, or not the correct forum, but recently, I imported a Power BI dataset (streaming dataset) into PBI Desktop.  It was working just fine, but last Thursday, I started to receive an error message that the "database doesn't exist or I don't have permission".  Permissions are not the issue; the error message may be related to "Add to Local Mode".  Did something recently change?

[datamodel]

Got some inside info on this, and it's probably because some of the older workspaces are being 'upgraded'. Try creating a new workspace for the 'source' dataset.

[datamodel]

We are experiencing the same issue. Any insight would be appreciated! Thank you!

[FraukjeC]

Would this help maybe?
https://powerbi.microsoft.com/en-us/blog/announcing-the-retirement-of-streaming-dataflows/