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

[Anonymous]

So I didnt do any thing special. I just connect to TWO powerbi Dataset and add excel file.
Create relationships. But when i publish im getting the error of 

Also RELATED formula is not working. even i have a Many is to 1 relationship.

[jeroenterheerdt]

the relationship that RELATED is not working on is that between source groups or within the same source group?

[Anonymous]

Team,

I am having same exact issue today

Jim

[jpierzchala]

I'm having the exactly same issue on my end. I believe it is related to problems with build permission. The publish work correctly when you connnect to datasets located in a workspace you are member of. The above exception occurs when you try to publish a report which utilizes Direct Query connection to a dataset you only have build permission to but are not a member of a workspace it resides in. 

I have a support ticket opened on this but it is progressing painfully slow.

[jeroenterheerdt]

you need build permissions on the full chain at the moment.

[jpierzchala]

I'm not entirely sure what do you mean by that. 

My experience is that if you have a build permission to a dataset you can build reports on top of this dataset (using live connection mode) and publish to service even if you're not a member of the workspace, the dataset resides in.

However, the moment you change to DirectQuery mode (the "make changes to this model" option) publishing no longer works due to the exception above

[Anonymous]

I added dataset as datasource to an existing file with other datasources as well. now I see the dataset in the Fields pane on the right and in the Model view, but when I hit Transform data the dataset does not appear in the Power Query editor...

For exampke I wanr to merge existing data source with data from dataset - how can it be done?

thanks 

[Anonymous]

Yes, I am having the same issue. The concern for me is that most report developers are not going to have build / member permissions on centrally managed datasets produced by IT. We should be able to take a  Power BI dataset (read only) via live connection and combine it with local data (spreadsheet).  I cannot do that in the preview version.

[jeroenterheerdt]

this is something we're looking at to change, it is not easy though. 

[dmarkle]

@tessahurr A big thank you and congratulations to the team for making this a reality.  It's hard to overstate the impact of this.

We've had some of the same issues that others have reported, but some signouts/sign-ins on PBI desktop seem to have gotten us around that.

One of the big things that come to mind which I'm glad you've mentioned is governance.  This issue is basically still there with any live-connected Power BI model, but composite models make this more important.  Basically, how do we best insulate ourselves from breaking changes when connected to a live connection or derived model?  Is the long-term vision to have customers rely only on naming conventions, or can we have something more robust?

Use case: 
My Power BI model, connected to an Azure AS data source, has a measure called [Total Sales].  Later on, someone determines that this is a very useful measure to have in the base Azure AS model, so [Total Sales] is brought into the base model.  The original Power BI model then breaks because there's now a duplicate measure defined.  So, we tell our users to use a naming convention to prevent this.  Maybe we put out an edict to our users to always prefix their own measures with u -- we tell them not to create [Total Sales], but instead [uTotal Sales].  Then, of course, they're renaming all of their columns in all of their visuals from our naming-convention based measure name to a more human readable measure name.  Yuck.  

Coming from the programming world, we use namespaces to solve this sort of problem.  If we could give a namespaces to our base model(s) and reference those in our derived models, that could be a great solution.  Sounds like it'd be a pretty huge change for the parser/engine, but it could be quite useful.  

How this would work in practice is, I admit, non-trivial, especially when trying to balance the needs of casual and line-of-business users.  Making that experience user-friendly and developer-friendly could be somewhat of a challenge, but for those of us who run "big, centralized model" shops, it's a question we've been actively thinking about.

[jeroenterheerdt]

we are looking at the lineage view and impact analysis to cover this, but yes, there is more to done in this area.

[dmarkle]

Yeah, I think my follow-up on this is whether or not the team plans to do work in truly preventing derived model breaks, or if the thinking is just about detection.  Granted the former is a heavy lift, but I think it's worth doing, or at least talking about.   Actually scoping measure names to the tables they're assigned to could also work; an upstream model could reserve a measure table solely for its own use....

[MBonnett]

Hi @tessahurr ,

First off, thank you and the team for the work on this! This is something my team has been waiting for, and is super excited about - we can't wait to try it out... but are currently running into errors. 

We are trying to connect to datasets in our workspace using this new functionality and are encountering the below error when trying to add a local model after establishing the live connection:

An error occurred while loading the model. Verify that the connection information is correct and that you have permissions to access the data source.

When we make the local model first and then try to add the live dataset connection we then get this error:

Cannot load model

We couldn't connect to your Analysis Services database. Double check that your server and database names are correct, and make sure you have permission to access them.

Bad Request

Technical Details:
RootActivityId: 30c344e7-833a-42c8-b974-4a8b2202f1ad
Date (UTC): 12/17/2020 3:13:34 PM

We should have access to the datasets so I don't know why we would be getting these errors. A single live connection works fine, but when we try to add the local model to create the composite model we get the errors.

Does anyone have any thoughts on what we are missing?

Thank you for any help, and once again - thank you for the work on this feature!!

[jeroenterheerdt]

can you give us more details on the type of workspace (pro/premium) and the model? Are you connecting to a model in "My Workspace"? Does the model have a measure-only table by any chance?

[MBonnett]

@jeroenterheerdt thank you for the response!

This is in a Premium Capacity Workspace and I am trying to connect to a very basic model with no measure tables (I have tested many different scenarios to try to get around this error), just to see if I can get it to work. My co-workers are having the same issue as well. Thanks again for any assistance!

[jeroenterheerdt]

Interesting. You might be victim of deployment delays. Try again early next week. If it still does not work by then, please report back. Thanks, thanks.

[MBonnett]

Hi @jeroenterheerdt ,

Can you confirm if it is a requirement to be on the latest Gateway version for this capability to work? 

[jeroenterheerdt]

I don't think you need to update your gateway. Bear in mind that SSAS is not supported.

[MBonnett]

Will do - thank you!

[sca4TVD]

This is a great addition to Power BI.

As a lot of my customers uses Power BI Reporting Services, I would like to ask you if it is planned to add this to PBI-RS?

If yes, when?

Regards

Salvatore

[jeroenterheerdt]

We are not planning to bring this to PBIRS any time soon, but please vote for that idea on ideas.powerbi.com!