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:PerformanceQuery 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 experienceThoughts around governance and permissions for models that leverage this featureNesting models, i.e. building a composite model on top of a composite modelAutomatic page refresh for live connect in composite modelsThanks and we look forward to hearing your feedback! - The Power BI Modeling Team

Very disappointed SQL Svr Analysis Services is not yet support. Can you clarify what release of SQL Svr will be needed and when we can expect it?

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?

It appears you can't undo the connection. Can you add a way to remove added datasets?

Ok, since this thread was for feedback I would say I don't find this feature useful at all unless viewers of workspaces/datasets can utilize these types of dataset datamodels. We can't have viewers have permissions to be contributors/members and with PBI premium we cannot license everyone for pro to just view reports.

Any idea when it will be declared generally available? I can't see it on the roadmap. We don't allow build permissions so until we can use this with read only access, we can't use it.

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

533 Replies

WillHopkins
Advocate I
5 years ago
Very disappointed SQL Svr Analysis Services is not yet support. Can you clarify what release of SQL Svr will be needed and when we can expect it?
- jeroenterheerdt
  Microsoft Employee
  5 years ago
  We are looking at the next major release of SQL Server. The feature name is "...Azure Analysis Services" on purpose 🙂
  - WillHopkins
    Advocate I
    5 years ago
    Thanks that is helpful information. We will keep and eye out for the feature.
Anonymous
Not applicable
5 years ago
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?
- WillHopkins
  Advocate I
  5 years ago
  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.
  - jeroenterheerdt
    Microsoft Employee
    5 years ago
    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.
Anonymous
Not applicable
5 years ago
It appears you can't undo the connection. Can you add a way to remove added datasets?
- jeroenterheerdt
  Microsoft Employee
  5 years ago
  that is a current limitation.
MLechner
Frequent Visitor
5 years ago
Hi team,
we had the following scenario:
We created a "golden dataset" that we use in some dashboards. If we now connect an additional data source to the dashboard an extra dataset is created for it, which makes sense, yet we have a strange phenomenon. We have set the permission for both datasets to "Read". But the users can't see the data of the "Golden Dataset" in the new dashboard. Only when we set the permission of the users in the Golden Dataset to "Build", they can see the data. Of course, this is not desired because we want to prevent users from creating their own dashboards with the dataset.

Are you aware of the problem ?

Greetings
Markus
- hornsjd
  Advocate I
  5 years ago
  This is a known issue, please upvote this idea to help prioritize it:
  Microsoft Idea · Directquery for datasets only needs read permissions on all datasets (powerbi.com)
- jbj85
  Frequent Visitor
  5 years ago
  Hi MLechner,
  
  We have the same behaviour and to my knowledge there isn't another way around it at the moment, which is frustrating...
- jeroenterheerdt
  Microsoft Employee
  5 years ago
  this is a known issue and we're investigating a fix.
  - Anonymous
    Not applicable
    4 years ago
    Jeroen — this is the main issue blocking our use of this feature in production. We have MANY datasets and analysts want to add their own lists but build permission is not an option.
psolli
Advocate II
5 years ago
tessahurr We are currently building models and testing it. So far it looks fantastic. This is the new Holy Grail of Business Intelligence. Power ON will report back with all findings shortly. We will be making some very exciting anouncements soon for the Power BI Community as well. Just need to make sure it is all there.

Must be a very excting day for the Product Team. Congratulations!

Per
teosantoro
Regular Visitor
5 years ago
Hi,
I mixed a PBI Dataset and an Excel table.
In PBI Desktop all worked correctly, but after i published the pbix to services i received a "There is no available gateway" error when I opened the report.
The orginal PBI Dataset works correctly. The orginal PBI Dataset is in the same workspace of the mixed Excel dataset.
When Excel is imported no gateway is needed, anyways I tried also to install a gateway for the Excel but Excel doesn't find it and the error remain.
- Anonymous
  Not applicable
  5 years ago
  Im having a similar issue
- Anonymous
  Not applicable
  4 years ago
  @teosantoro try publishing the excel to Sharepoint / Onedrive and access it from Excel Desktop, click File - Info - Copy Path and use the URL in a new Web connector query (removing ?web=1 from the URL).
  
  You won't need a gateway if you use this pattern.
dpatocka
Frequent Visitor
5 years ago
Hello team,
I'm fasing issue with Scheduled PBI data refresh in workspace with error: Single sign-on (SSO) credential is not provided which is required to execute the current request.. A connection could not be made to the data source with ..... I created new table with function SUMMARIZECOLUMNS from table which is DirectQuery from PBI dataset. Refresh in PBI Desktop works but if I put this report to the workspace then refresh failed by mentioned error. Any suggestion?
- Anonymous
  Not applicable
  5 years ago
  There is a thread further down on this same issue and Microsoft have stated "Creating calculated tables over a remote dataset is not supported although it is not currently blocked in the interface."
n8ball
Advocate I
5 years ago
EDIT: I see sort by column is not currently supported in the documentation. Sorry posting before seeing that.
Loving it so far. One possible bug I have found is that converting from Live Connection to DirectQuery for PBI datasets breaks sort by column.
ggaudfroy
Advocate III
5 years ago
C'est la nouveauté de 2020!
Joli cadeau de Noel!!

ncmiller

Frequent Visitor

5 years ago

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 BI

As 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 BI

Happy to provide additional detials.

megank
Power BI Team
5 years ago
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)?
- ncmiller
  Frequent Visitor
  5 years ago
  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.
jeroenterheerdt
Microsoft Employee
5 years ago
thanks, are you using a model that contains one of more measure-only tables by any chance?

Forum Discussion

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

533 Replies

Related content

thoughts on design principles

Share your thoughts on the New Power Query experience in Power BI Desktop (Preview)

DirectQuery Semantic Model

Share your thoughts on the new data model editing in the Power BI service feature (preview)

Power BI Desktop Bridge (June 2026 Preview)