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

[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

[jeroenterheerdt]

is the preview feature enabled? Also, re-reading your original post - your dataset that is live connected to SSAS will not work.

[steen_p]

I'm actually having the same problem. I have tried to connect both to a SSAS tabular model and then also a Power BI dataset from a file I have published. None of these connections gives me the option to connect to another data source.

Now when I see that you say it won't work when connecting to a (on-prem) SSAS model, can you then say when this will be possible?

We are running everything on-prem but I have a Power BI Pro license and it was the dataset of one of the models I have published here I tried to connect to. I get the connection to the dataset and see the model, but I'm not able to add any additional data.

I have checked that the preview fetaure have been enabled and also restarted Power BI.

[jeroenterheerdt]

On-premises SSAS is probably coming in the next major release of SQL Server. Unfortunately I don't know when that is going to be.

Regarding the dataset: please make sure the dataset is not in "My Workspace".

[steen_p]

Ok - connecting to a dataset that is not in "My workspace" actually made that part work.

Unfortunately, it's not that useful when I'm not able to connect to a local SSAS instance, so we'll have to wait until the SQL Server upgrade comes out....☹️

[jeroenterheerdt]

ok, "my workspace" is not supported (see the blog). SSAS support is something we are looking into for next major version of SQL Server.

[Anonymous]

Hi all,

I'm really glad with the introduction of this very nice feature!

This will definately allow us to declutter some datasets 🙂

I already did some tests building a new dataset and up til now very happy with the performance.

I did however stumble into an issue related to Calculation Groups.

I can create a calculation group in the parent dataset, but this doesn't apply the calculation item to measures defined in the child dataset.

So I thought, let's create a calculation group in the child data set, but there I get following error 😕

Is this an error or still to be implemented ?

Thx!

[jeroenterheerdt]

calculation groups are not supported yet. See the blog post: DirectQuery for Power BI datasets and Azure Analysis Services (preview) | Microsoft Power BI Blog | ...

[Michael_Mertens]

Generally, this is a great feature, thank you, Microsoft. Unfortunately this is not working for AS Multidimensional and the blog post says "Support for SSAS Multidimensional models is not planned.".

But what about making just the first part of the feauture available for Multidimensional, not the whole? That means to allow Direct Query for AS Multidimensional, not the part regarding chaining. This should reduce the complexity drastically.

Maybe someone wants to support this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=2c704cc7-f74c-eb11-8fed-281878ddff46

And, what if this is not done? Then other tools are still better visualization tools for AS Multidimensional than Power BI (at least in this regard ), see the screenshot here:  Hype about DirectQuery for Power BI datasets and Azure Analysis Services – Michael Mertens (mmertens... 😂 

[jeroenterheerdt]

Correct, SSAS Multidimensional support is not planned. Our focus is on tabular models first.

[ericw]

Hi there, I'm trying to use this feature when connecting to a Power BI dataset sourcing data from an Excel file. The Excel file contains data sourced from a database using Power Query and loaded to a pivot table. I then publish the Excel file to the Power BI server making its underlying data available as a Power BI dataset. I've updated my Power BI desktop to the latest version and enabled this previous feature. However, when I connect to this Power BI dataset, the only option that is available under the "Get data" section is "Power BI template Apps". I am also not allowed to "Make changes to this model" as your screenshot showed. This is a great feature and we'd love to use it in our future reporting. But I'm not sure if I've missed anything here. Any suggestions?

[jeroenterheerdt]

Hi, you mentioned 'Power BI Server', are you referring to Power BI Report Server? This feature is not available there. Also, please make sure your dataset that access the Excel file can successfully be refreshed. Does anything change if you import the Excel file into the dataset?