Forum Discussion
tessahurr
Microsoft Employee
Microsoft EmployeeShare 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. ...
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.
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 BIAs I'm testing more I just encountered another error message:
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 BIHappy to provide additional detials.
- megank
Power BI Team
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.
- megank
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!
- jeroenterheerdt
thanks, are you using a model that contains one of more measure-only tables by any chance?
