Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tessahurr
Microsoft Employee
Microsoft Employee

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

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

534 REPLIES 534

Hi @Anonymous , 

 

I am having the same issue. Any help appreciated, @tessahurr . 

 

Regards

Daniel

ChoiJunghoon
Helper III
Helper III

"Replace Values" function cannot be used in power query after Dec'20 update.

"The step result does not supported in direct query"... 

 

There was no problem until the November version, but they say they don't support the function if I update it to December.

Do you have any plans to have the power query re-use " Replace Values" or "Renameed Columns" function ? 

 

I am not sure what you mean, this topic is about DQ for PBI datasets and AAS. Is this related?

AnonymousPerson
Advocate V
Advocate V

@tessahurr Congrats on your "insane amazing" Guy in a Cube debut! 😀

I hope to use this feature to break up/modularize my models.

 

I have a large model in the Service, with multiple fact tables that do not have the same refresh frequency requirement. Currently, I'm forced to refresh all fact tables every 10 minutes even though some only need to be refreshed daily, because the PBI XMLA endpoint won't support automating that with SSIS or SQL Agent. That costs unnecessary time and resources.

 

With this new feature, I will just make each fact table a single-table "model" that will be refreshed on its own schedule. I will have a "main" model that is just the smaller dimensions, refreshing like every 10 minutes.  Then I can just plug and play with the different fact tables as needed.  My POC on this idea worked fine today.  I just hope it performs when I scale up to 500 million rows.

 

Either way, I'm not planning on putting this in production until out of preview... Too many limitations outstanding ( I use "ALLSELECTED" a lot, and not being able to delete the objects once added is kind of annoying).  But thanks for letting us try it, and for continually making me actually look smart to all of my customers and bosses!  GA cant get here soon enough.  In the meantime. Happy holidays!

In your scenario I would definitely get the refresh issue fixed instead of making a workaround using this new feature. I forseas you will get into performance problems when you start joining tables from so many different models. The general performance implications when using DQ also applies when using it with Analysis Services: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#performance-impli...

 

Do you have the latest MSOLAP and ADOMD.NET client libraries installed on your SSIS machine?

ncmiller
Frequent Visitor

I've been having difficulty with this feature, which is a bummer because I've been so excited to use it. I've successfully connected small data models published to the service. However, whenever I try to connect a larger data model >~200 mb I get this error, except for on 550mb model with only 9 tables. When I hit cancel it pops up again and again and I need to close the program from task manager. All of the models are import only and are hosted/accessed with a pro license. 

ncmiller_0-1608172477667.png

 

Below is a copy of the report when I select 'Report this issue'

Feedback Type:
Frown (Error)

Timestamp:
2020-12-17T02:25:03.7471645Z

Local Time:
2020-12-16T21:25:03.7471645-05:00

Session ID:
3d361669-bfb6-41ee-a337-7d929976c0fc

Release:
December 2020

Product Version:
2.88.621.0 (20.12) (x64)

Stack Trace:
at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
at Microsoft.PowerBI.Modeling.Engine.DataModel.Utilities.DataModelExtensions.GetLastKnownQueryName(IQueryableObject obj, Boolean isV3FeatureSwitchEnabled)
at Microsoft.PowerBI.Modeling.Engine.DataModel.Utilities.DataModelExtensions.GetLinkedQuery(IQueryableObject obj, TwoWayDictionary`2 queryOriginalNameToRuntimeIdMap, ModelingMashupDocument mashupDocument, Boolean isV3FeatureSwitchEnabled)
at Microsoft.PowerBI.Modeling.Engine.Loader.QueryStalenessEvaluator.<GetQueryableModelItemsWithoutQuery>d__5.MoveNext()
at System.Linq.Enumerable.Any[TSource](IEnumerable`1 source)
at Microsoft.PowerBI.Modeling.Engine.Loader.QueryStalenessEvaluator.GetStalenessReason(ModelingMashupDocument mashupDocument, IModelingSession modelingSession, IModelingQueryServices modelingQueryServices, IDataModel dataModel, Boolean isModelV3Enabled, Boolean isV3FeatureSwitchEnabled, Boolean includePendingUpgrades, Dictionary`2& queryIdToStalenessReasonMap)
at Microsoft.PowerBI.Client.Windows.PowerBIQueryStalenessEvaluator.IsLoadRequired(Report report, Boolean includePendingUpgrades)
at Microsoft.PowerBI.Client.Windows.Services.ReportFactory.CreatePowerBIPackageFromReport(Report report, DataModelSaveMode abfSaveState, Queries overrideQueries, IReadOnlyDictionary`2 parameterExpressionsToUpdate, Boolean packageWillHaveLabel)
at Microsoft.PowerBI.Client.Windows.Services.PowerBIPackagingService.Save(Report report, FileStream fileStream, DataModelSaveMode dataModelSaveMode, Queries overrideQueries, IReadOnlyDictionary`2 parameterExpressionsToUpdate)
at Microsoft.PowerBI.Client.Windows.Services.AutoRecoveryManager.<>c__DisplayClass82_1.<PerformAutoSave>b__3()
at Microsoft.PowerBI.Client.Windows.Services.AutoRecoveryManager.<>c__DisplayClass85_0.<TryIOActionAndLogException>b__0()
at Microsoft.PowerBI.Client.Windows.Services.AutoRecoveryManager.TryIOActionAndLogException[T](Func`1 action, String description)
at Microsoft.PowerBI.Client.Windows.Services.AutoRecoveryManager.<>c__DisplayClass82_0.<<PerformAutoSave>b__1>d.MoveNext()

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.88.621.0","ModuleName":"Microsoft.PowerBI.Modeling.Engine.dll","Component":"Microsoft.PowerBI.Modeling.Engine.DataModel.Utilities.DataModelExtensions","Error":"System.InvalidOperationException","MethodDef":"GetLastKnownQueryName - PowerBIPackager.Save","ErrorOffset":"92"}

OS Version:
Microsoft Windows NT 10.0.18362.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528040]

Peak Virtual Memory:
39.2 GB

Private Memory:
745 MB

Peak Working Set:
897 MB

IE Version:
11.1139.18362.0

User ID:
d3d15e6c-c879-408e-bc45-d21c1a816328

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Model Default Mode:
DirectQuery

Model Version:
PowerBI_V3

Is Report V3 Models Enabled:
True

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_NewWebTableInference
PBI_v3ModelsPreview
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_narrativeTextBox
PBI_newFieldList
PBI_cartesianMultiplesAuthoring

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
PBI_JsonTableInference
PBI_ImportTextByExample
PBI_ExcelTableInference
PBI_qnaLiveConnect
PBI_eimInformationProtectionForDesktop
PBI_azureMapVisual
PBI_dynamicParameters
PBI_anomalyDetection

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
125%

Supported Services:
Power BI

As I'm testing more I just encountered another error message: 

ncmiller_1-1608173208407.png

This was after I had live connected to a model on the service and was converting over to bring the model into the file when connecting to another data source. 

Feedback Type:
Frown (Error)

Timestamp:
2020-12-17T02:47:33.4852072Z

Local Time:
2020-12-16T21:47:33.4852072-05:00

Session ID:
afb01825-2553-4afc-9255-6bfa94d8b141

Release:
December 2020

Product Version:
2.88.621.0 (20.12) (x64)

Stack Trace:
   at System.ThrowHelper.ThrowKeyNotFoundException()
   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at Microsoft.PowerBI.Client.Windows.Modeling.RelationshipLoader.TableInputRelationshipVisitor..ctor(Report report, IDataModel dataModel, IEnumerable`1 tableInputs, HashSet`1 existingTableInputs, List`1 tableInputsToProcess)
   at Microsoft.PowerBI.Client.Windows.Modeling.RelationshipLoader.TryImportRelationships(CancellationToken cancellationToken, IEnumerable`1 tableInputs, IDataModel dataModel, HashSet`1 existingTableInputs, IReadOnlyDictionary`2 dqResourceInfoMap, TableInputToQueryMap tableInputToQueryMap)
   at Microsoft.PowerBI.Client.Windows.Modeling.RelationshipLoader.TryDetectAndImportRelationships(IExtendedModelChangeScope modelChangeScope, CancellationToken cancellationToken, IEnumerable`1 tableInputs, List`1 newTableColumnPairs, HashSet`1 existingTableInputs, TableInputToQueryMap tableInputToQueryMap)
   at Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.CreateRelationships(IRelationshipLoader relationshipLoader, IExtendedModelChangeScope modelChangeScope, List`1 newTableColumnPairs, HashSet`1 existingTableInputs)
   at Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.ProcessLoadInputs(IRelationshipLoader relationshipLoader, IExtendedModelChangeScope modelChangeScope, Action`1 beforeSchemaSyncCallback, Action`1 afterSchemaSyncCallback)
   at Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.<>c__DisplayClass19_0.<PerformLoadInternal>b__0(IExtendedModelChangeScope modelChangeScope)
   at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.<>c__DisplayClass14_0.<RunExtendedModelChangeAsync>b__0()
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.PowerBI.Modeling.Common.TaskExtensions.WaitAndUnpackException(Task task)
   at Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.PerformLoadInternal(IRelationshipLoader relationshipLoader, IModelAuthoringChangeScopeProvider authoringChangeScopeProvider, IModelingQueryServices queryServices, Action`1 beforeSchemaSyncCallback, Action`1 afterSchemaSyncCallback, IRefreshPolicyUpdater refreshPolicyUpdater, Boolean skipImpactAnalysisWarning)
   at Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.<>c__DisplayClass18_0.<PerformLoadAsync>b__1()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- 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.Modeling.Hosting.ModelingTelemetryService.<>c__DisplayClass8_0`1.<<RunInAsyncActivity>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.Modeling.Hosting.ModelingTelemetryService.<RunInAsyncActivity>d__7.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.Modeling.Hosting.ModelingTelemetryService.<RunInAsyncActivity>d__8`1.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.Modeling.Engine.Loader.LoadToReportFlow.<PerformLoadToAnalysisServices>d__37.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.Modeling.Engine.Loader.LoadToReportFlow.<ExecuteInternal>d__32.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.Modeling.Engine.Loader.LoadToReportFlow.<Execute>d__31.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.Modeling.Engine.ModelingEngine.<LoadModelAsyncInternal>d__10.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.Modeling.Hosting.ModelingTelemetryService.<>c__DisplayClass8_0`1.<<RunInAsyncActivity>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.Modeling.Hosting.ModelingTelemetryService.<RunInAsyncActivity>d__7.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.Modeling.Hosting.ModelingTelemetryService.<RunInAsyncActivity>d__8`1.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.Modeling.Hosting.ModelingService.<LoadToModel>d__16.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.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass35_0.<<StartLoadToReportFlow>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()

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.88.621.0","ModuleName":"Microsoft.PowerBI.Client.Windows.dll","Component":"Microsoft.PowerBI.Client.Windows.Modeling.RelationshipLoader+TableInputRelationshipVisitor","Error":"System.Collections.Generic.KeyNotFoundException","MethodDef":".ctor","ErrorOffset":"194"}

OS Version:
Microsoft Windows NT 10.0.18362.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528040]

Peak Virtual Memory:
38.1 GB

Private Memory:
457 MB

Peak Working Set:
672 MB

IE Version:
11.1139.18362.0

User ID:
d3d15e6c-c879-408e-bc45-d21c1a816328

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

AS Live Connection:
True

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_NewWebTableInference
PBI_v3ModelsPreview
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_narrativeTextBox
PBI_newFieldList
PBI_cartesianMultiplesAuthoring

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
PBI_JsonTableInference
PBI_ImportTextByExample
PBI_ExcelTableInference
PBI_qnaLiveConnect
PBI_eimInformationProtectionForDesktop
PBI_azureMapVisual
PBI_dynamicParameters
PBI_anomalyDetection

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
125%

Supported Services:
Power BI

Happy to provide additional detials.  

thanks, are you using a model that contains one of more measure-only tables by any chance?

@ncmiller The second error you mentioned, "The given key was not present in the dictionary", is a known issue. It's related to measure tables in the source, so you should not see this if you try to connect to a dataset that does not have any measure tables. We're already working on the fix and it will be available soon. 

 

The first error, "Sequence contains more than one matching element", is a new one. It looks like it might be happening during an auto-save. Do you remember if you got the error while connecting to a dataset in live-connect mode, or during conversion to DirectQuery (or you already had data in the report when you connected forcing the DQ mode)? 

Thanks for the response, I look forward to the fix with the measure table. I'd rather not go back through all my models and switch them over. Question, does that error apply even if the table is a fact table, but all columns are hidden leaving only measures? This looks like a measure table, but still has relationships with other tables. 

 

For the 'Sequence contains more than one matching element' I can get that either when twitching to DQ mode from live-connect, or adding a second DQ model. I've been testing with usage report data models and failed to load a second usage report into the same data model today, though yesterday I was successful. 

@ncmiller @st_oleg @illeke612 @pr2000 @MCornish 

The fix for the error "The given key was not present in the dictionary" when trying to convert a live connect report or add a new DirectQuery AS source that has a measure-only table is fixed in the latest December 2020 build (version 2.88.702.0) which is available in the Download Center and Microsoft Store (might need to manually check for updates). Please let us know if you're still having issues with these data models! 

 

it works.

great fast job! thanks!

Incredible! What a fast turn around. It works like a charm! 

It should only impact measure-only tables (no columns at all). If the columns are just hidden then we don't expect to see this error. 

 

We'll look into the other issue today. I'll reach out if I need more info!

Anonymous
Not applicable

@megank 

Here's the error about "RELATED" formula not working.

1.PNG2.PNG

Thanks for the screenshot, @Anonymous ! I confirmed with the team that this is by-design. Basically RELATED is not supported for weak/limited relationships, which includes cross-island relationships. This is the same behavior you would see if you were to have a composite model with two SQL DirectQuery sources (because they're in different islands), you can do this for import tables because they're all in the same (local) island.

 

You can find more info about this on the RELATED function docs here: RELATED function (DAX) - DAX | Microsoft Docs and the section on Limited relationships her: Model relationships in Power BI Desktop - Power BI | Microsoft Docs

mim
Advocate V
Advocate V

first feedback so far.

1- it will be nice to have description from the source model supported thinking of using it as a way to document the measure formula, so downstream users can see the formula used in a measure.

2-is it possible to deleted tables and measures, in some cases, all I want is getting data using directQuery from 1 Table.

3- if 2 is not possible, can we have a folder to group all the tables and measures from 1 model, otherwise it may become very hard to navigate big models.

 

very great works

Hi Mim, for 1: we have for now decided not too, since there might be intellectual property in the model or other reasons not to expose the definition. Regarding 2: not sure what you mean here.

Anonymous
Not applicable

Very excited for this!  However, we create our data models in SSAS, so the only way we can leverage this is to connect to SSAS, publish the model to Power BI Service, then connect a new *.pbix to it.
Does that cause a degredation of performance to essentially use the Service as a passthrough to SSAS?

Hi I tried that. You do not get the option to switch to direct query mode although the get data button is not disabled.. I think Power BI knows that SQl Server Analysis Services is behind the dataset. If you can get that to work though it would be helpful.

I don't think that would be a great solution, too many hops and you are really trying to make something work that is not supported.

drewlarson
Frequent Visitor

This feature could be incredibly powerful. I have a core dataset that requires slight enrichment by each customer... which currently would result in many datasets. I'm excited at the possibility of reusing the core dataset and then enriching it. 

 

One issue I noticed right away is that it doesn't seem like date heirachy from the Power BI dataset i'm connecting to is supported. Any chance that can get fixed? 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors