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

[jane01]

Thanks @jeroenterheerdt ! Ticket raised: 121011326000013

[shawne]

What is the plan/timeline to make this feature GA? 

[jeroenterheerdt]

depends on the preview :). Looking back at other features we have seen that most will go GA after spending a couple of months in public preview. This is a big feature however.

[pieterf]

I made a connection to 2 tabular models, both to a specific defined perspective (not the whole model). However, both tabular models where fully (whole model) loaded in Power BI, perspectives where ignored. 

Have you planned to integrate also the perspective view functionality?

[jeroenterheerdt]

Hi Pieter - the perspective should not have been ignored. What should have happened is that all tables were loaded and the tables / columns that are not part of the perspective should have loaded as hidden. If you are seeing anything else then that is not as designed.

[pieterf]

Hi Jeroen, that's interesting. Both models are fully loaded and tables/columns that are not part of the perspective are not hidden. 

[jeroenterheerdt]

that is not what it should be. Could you paste some screenshots or send them to me in direct message?

[Eliasbsj]

I have a question. Imagine a scenario where I have a connection to a dataset using a direct query. The only change I made to the second node is some calculated columns and calculated tables, with no data sources other than the original dataset. My question is: are these tables and calculated columns updated automatically when the original dataset is updated? Or do I need to configure an update interval? It may seem like a trivial question, but I know that calculated columns and tables are evaluated at load time and not at execution time, like measures. In summary, don't I need to worry about updates on sources like SSAS and the original Power B.I. dataset?

[jeroenterheerdt]

Responded to this question on the announcement blog post. Please refrain from cross-posting the same question.

[Eliasbsj]

Sorry about that. The discussion on the announcement blog post is no longer available. I will post your answer to the question here to help others in the community:

"Thanks - your tables / calculated columns in your section are not updated automatically, you will need to refresh it. You are correct the calculated columns are evaluated at load time."

[RadekG]

Hi Guys,

This feature is superb.
Unfortunately it is resetting all defined sorting in the model.
So if you are using "Sort by colmun" in your model and you will refresh the model locally with "DirectQuery.." feature on, your "Sort by column" will be resetted to default.
I hope you will patch it up asap 🙂
Best
Radek

[jeroenterheerdt]

Hi Radek,

thanks - this is a known issue and will be fixed shortly.

[Seb69]

Great new feature that enables many new possibilities. I ran into an issue when sharing a report that uses mixed storage (DirectQuery for a Power BI dataset and an imported excel file). 

When I design the report at first with a Live connection to the Power BI dataset and publish it, the visuals perform well for both myself as owner of the report and also for another user with whom I shared the report.

But things change when I add an import file to the model and Power BI Desktop adds a local model and transforms the Live connection a DirectQuery connection with the related Power BI dataset. After publishing and sharing, the owner of the report can still view all data in the visuals, but the user with whom I’ve shared the report find the visuals unable to load the data from the Power BI dataset. 

The error message reads:

Activiteits-id: 130bad53-793d-4ef9-b998-41c68325a794

Aanvraag-id: f1fc14b5-fa7e-c3a9-42c3-b298370d78ed

Correlatie-id: faaa88ce-c448-d493-65c7-1c5065cde202

Tijd: Sat Jan 09 2021 12:22:30 GMT+0100 (Midden-Europese standaardtijd)

Serviceversie: 13.0.15022.68

Versie van client: 2012.2.04142-train

URI cluster: https://wabi-west-europe-d-primary-redirect.analysis.windows.net/

And the screen looks like this:


Any suggestions what goes wrong here?

[jeroenterheerdt]

Although I was tempted to respond to your post in Dutch, I'll stick to English. I am not sure what is going on here, this should work fine. Are you sure the user can dataset? Also, is the Excel file accessible (through at Gateway)?

[Seb69]

Thanks Jeroen; yes new features are also being discovered in Nijmegen 🙂 
I seem to have solved the issue in one scenario, but I am not sure what made the difference. 
I changed two aspects:
1. I upgraded the workspace from classic to new
2. I added the user with whom I shared the report as member of the workspace. 
That's not directly the situation I aim fo as members can access all reports in a workspace. 
Can you shed some light on the conditions of a workspace and the conditions that go for users with whom a report is being shared, to make such a composite model work fine?
And in response to your questions: yes I shared the underlying Power BI Dataset also with the same user. No issue. And the excelfile is accessible from a Onedrive location. Apparently granting the user with whom I shared the report a membership to the workspace resolved the issue.

[jeroenterheerdt]

yeah, users need to be part of the workspace and need build permissions to read the report and create a new one (we are investigating changing the permissions needed to read the report to "read" instead of build).

[mattyj2009]

Do you have an ETA as to when the permissions changes will be implemented.  We are sharing the report with the users and don't want to give them access to the entire workspace as either a viewer or member.  We want to be able to share with any pro user within our company. 

We are having users get the issue of visuals not being able to be displayed when just the report is shared with them. 

[Seb69]

I see. And I can understand the logic behind it when I read about the idea behind this new feature to enable the use of standardized corporate datasets in combination with personal or local datasets. 
My usecase is completely different. In my situation the Power Query part of my report is starting to become so large and complex that the actual performance within Power BI Desktop is uncomfortable. And a refresh within Power BI Service takes already over an hour and the limit for that is 120 minutes. So I cut my model in several pieces. One part that needs to be refreshed every other day and another part that only needs to be refreshed every quarter of the year when CBS brings out new data. This improves the ease of the development process within Power BI Desktop and reduces the refreshing effort within Power BI Service. And in this situation there are no other members in the particular workspace. Only myself as administrator. And a few dozen selected colleagues in our organization with whom I just share the report without wanting them to have access to the workspace.
 
Perhaps it's interesting and relevant to be aware of these kind of usecases in developing these features in the near future.
Thanks so far.

[Anonymous]

Hello,

Does ths Power BI dataset to start with, needs to be a direct query model only ? 

I tried two different ways, one with a Power BI dataset which is using the import mode to a SQL server database and the other where Power BI dataset is using a live connection to a SSAS tabular model. Both ways do not let me make changes to the model. Am i missing something ? Pleae advise!

Thanks,

Neha

[jeroenterheerdt]

The Power BI dataset can be anything - does not need to be directquery. You will have to upgrade to a DirectQuery connection before you can make any changes - you can either choose 'make changes to this model' or add some more data. Were you able to do that?

[Anonymous]

Nope, the top ribbon is greyed out and also the right hand bottom side does not give the option to make changes to the model. Please see images below. 

I have updated my Power BI desktop to the latest December, 2020 release as well.

Thanks,

Neha