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

[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?

[Lumalytiq_TonyD]

Outside of the feedback below the measures not flowing through and being able to delete tables/columns in the remote datasets are the biggest need for me. There's a need to have measures in the parent model which supports our enterprise reporting and governance around having a single source of truth/calculation for different measures across the organization. Right now they all pull in as errors so you'd have to remove them from the parent or leave them with errors in the remote dataset. There would then be rework to replicate measures in all remote datasets and maintenance to make sure everything is aligned going forward. Second thing would be to allow users to delete tables/columns in the remote dataset. The use case is for self-service users we want to provide a dataset that might have some sensitive tables/columns that exist in the parent, this would be easier/cleaner than having to come up with some sort of RLS/CLS/workspace access combination to achieve. 

•  Performance
  • Minimal testing on small to medium sized datasets seems fine. I'm sure it's slower than the parent dataset but that's to be expected
• 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?
  • Most of the query editor steps should probably be pushed back to the parent dataset rather than only existing in the remote dataset, though there's probably plenty of use cases for being able to use PQ editor in both. 
• Navigator experience
  • Works fine
• Thoughts around governance and permissions for models that leverage this feature
  • There's a use case for having the parent dataset permissions push to the remote datasets as well as the remote datasets having their own set of RLS/permissions. We'll use this to have the parent dataset support enterprise reporting with remote datasets filtered down to different subsets of business groups that have their own security needs controlled by admins
• Nesting models, i.e. building a composite model on top of a composite model
  • Haven't come up with a use case where this is really needed or wouldn't be able to be handled in another way, though I'm sure the need exists

[jeroenterheerdt]

thanks for your detailed feedback, really appreciated!

[ShellBell]

'very excited about this functionality. I have not used it yet, but I enabled the preview checkbox. I am noticing instability of my existing live connect to Azure Analysis services. My visuals all turn blank and the error reads "can't display visual." It eventually returns to display the visuals.

[jeroenterheerdt]

interesting - you did not change anything else?

[chris_moore_pf]

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

[jeroenterheerdt]

that is a current limitation.

[Anonymous]

Hello!

Great feature unlocking a lot of new possibilities, BUT PLEASE, think of the governance aspect as well. Ability to turn this on/off per workspace / dataset would be very beneficial.

[jeroenterheerdt]

thanks for the feedback!

[Anonymous]

This is really a step towards real "self service BI", but with a big bottle neck... "data governance". With RLS not allowed in feature, data usage goverance is lost. People can create there own data-sets, and share with others without "owner" of data-set having no visibliity visibility left upon with whom data is shared. And, bigger treat is IF we have external B2B tenant option enabled, person can share content to external unauthorized users.  
Please consider enabling RLS (effective user within data-set connection) for this feature, it has a huge use case in Power BI "self  service" community.. and with RLS it will really become a jewel of feature. 

[jeroenterheerdt]

The RLS rules in the original source will be applied.

[Anonymous]

What if OpenAccess Power user A creates a composite model, saves in 'My Workspace' and shares with person B who has 'restricted access' to parent sales. Now, user B can see all data as RLS defined in parent model is not carried on to composite model created by user A. This is big governance beach ache and I am not able to solve it. 
Any advise.

[jeroenterheerdt]

RLS is not being applied _at the moment_ as we are still in preview. It will be applied soon. RLS rules on central model will be applied even for B

[MBonnett]

@jeroenterheerdt @tessahurr 

I wanted to let you know that I posted additional details pertaining to my team's errors here. 

@Zarek @frostystoo : I believe these are the same errors you are also seeing:

https://community.powerbi.com/t5/Issues/DirectQuery-for-Power-BI-datasets-and-Analysis-Services/idc-...