Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tessahurr
Microsoft Employee
Microsoft Employee

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

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

534 REPLIES 534

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

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.

 

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".

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....☹️

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
Not applicable

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 ?

Cruz3r86_0-1610020191062.png

 

Thx!

Michael_Mertens
Resolver I
Resolver I

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... 😂 

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

ericw
Frequent Visitor

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?

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
Frequent Visitor

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

thanks for your detailed feedback, really appreciated!

ShellBell
Regular Visitor

'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.

interesting - you did not change anything else?

Anonymous
Not applicable

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

that is a current limitation.

Anonymous
Not applicable

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.

thanks for the feedback!

Anonymous
Not applicable

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. 

The RLS rules in the original source will be applied.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.