cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tessahurr
Microsoft
Microsoft

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

495 REPLIES 495
florenti
New Member

I made a connexion with DirectQuery for Power BI datasets.

It's fantastic, very useful and powerful.

But, and it's a big BUT, once the connexion is made, there is no way to remove it!

Please, could you solve that ?

Hi @florenti thank you very much for you feedback and for trying this out! I am glad you like it! Regarding your question, I assume you have been looking to find the connection in the Power Query / Data Transformation window. It is by design that you will not find the connection there - as written in our documentation. However, you will be able to delete the connection using the Data Source connection dialog (Transform Data --> Data Source settings), in which you can also update the connection info if required.

Please let me know if this helps or if you are seeing anything else.

Thank you for this answer.
It works fine.

bensycamore
Frequent Visitor

This has been in preview for 2 years now.  @tessahurr: What are we waiting for to make it GA?

Exactly my question! This was first published as a biggest thing in Power BI world at the time but now I hear nothing about this and not even a best guess for GA. It is annoying to know, that this feature is available but only in theory. There were so many bugs when testing this, that I am waiting for GA to continue any work with this.

Five days from the two-year anniversary of "a milestone in business intelligence." 

 

I doubt that the mainstream media will recognize the importance of such innovation. But users that create and consume reports every day will immediately realize the impact of this change.

...

It is here, and it just works.

😫

I would like to chime in by saying I support Microsoft in not making a feature GA until it is truly ready for prime time. There has been bugs yes but to their credit they have not released to GA prematurely and they are throwing fixes at the bugs quite quickly. 

Secondly, this is a very difficult thing to implement technically.

The feature looks to be largely implemented via extensions to the DAX language itself and implements relationships between the local and remote model by placing large lists of data inside DAX statements in dynamic sets.

Finally, if you are an enterprise model designer, it's important to understand how this feature works under the hood to help you decide whether or not you should use it in your particular scenario, taking into account its features and limitations - and other factors such as the size of your model. In some cases, extending a single, base model - is going to provide a far superior solution than composite modelling.

There is a setting DiscourageCompositeModels that can be used to control whether composite modelling is permitted at a dataset level.

https://learn.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.model.discouragecomp... 

thanks all, we are not quite ready yet for prime time (or making this generally available). Yes, it takes a long time (longer than any of us want) but we are dependent on infrastructure changes that are taking longer than expected before we can declare GA.

Domantas_G
New Member

On the wesite it states that:
"If you refresh your data sources, and there are errors with conflicting field or table names, Power BI resolves the errors for you"

 

Not sure what is the exact definition of "field", but it doesn't resolve names of measures being idential. In my scenario I connect to multiple tables from different Power BI datasets (Direct query). Then if more than one of the sources gets new measure with the same name and they both hit my "combined model" at the same time, I get an error that "name is already used, and no refresh is working".

 

Feel free to contact me for a demo in case of any questions. 

I found the same when the same fully qualified DAX name for the measure in both models? i.e key measures[measure] is in both models. The refresh error implied it was getting duplicates when it was expecting a single item... I think the workaround I had was to subtlety change the table name.

 

But this does need to be resolved 

correct, this is on our radar and part of our de-duplication efforts.

pedroamaro90
Frequent Visitor

Hi @jeroenterheerdt,

 

We are developing reports using the Direct Query connection for PBI dataset feature and now we are having the some use cases to them in Power BI Embedded, which is currently not supported as described on the documentation.

 

Is there any planned release to allows this kind of use cases? This is critical for us.

 

Thank you

what issues are you seeing?

This is the issue:

pedroamaro90_1-1674461779790.png

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-...

 

Any plans to allow to use Power BI Embedded for reports with DirectQuery to PBI Datasets?

 

Thank you



There are no plans to enable this in the near future - it's a security problem that we still have to resolve. 

than688
Frequent Visitor

Hi, this is a great feature - but after months developing for my org using the feature, I am suddenly getting errors when refreshing which is jeopardising my entire project:

than688_0-1668634574387.png

I have only found one thread on this issue, here: https://community.powerbi.com/t5/Desktop/Refresh-Failed-file-pfoledbreader-cpp-line-836-function/td-...

with users positing that MS have removed the ability to create calculated tables on DQ data, or on using certain calculated table functions on DQ data (e.g. I am creating calculated tables on DQ tables using FILTER (with || OR conditions), CALCULATETABLE, and other DAX. All of this worked fine earlier this year, yet it now fails despite the model not changing.

Please could someone confirm what MS have changed to invalidate the methods I've been using - it's very frustrating and currently my only option is to convert all of these data models to 100% import, which will mean a huge amount of time wasted. Thanks!

 



 

Have you raised a support ticket? It may be just a bug in the release. Documentation hasn't been updated to reflect a change in desktop Using DirectQuery for datasets and Analysis Services (preview) - Power BI | Microsoft Learn

 

I understand it isn't helpful for you right now, however, I specifically don't build production solutions using preview features, or am prepared to 'fix it', due to this disclaimer.

MrNobody_0-1668642058571.png

 

pedroamaro90
Frequent Visitor

I'm having an error when I'm trying to use a Durect Query for PBI datasets, which I described in this post.

 

In summary, I'm getting an error when I execute a Power Automate Flow that exports a PBI report developed using a Direct Query for PBI Dataset. The flow is successfully executed, but the PDF file generated diplays several error messages for the visuals and it is not possible to see the message. In the post mentioned above it is well described how to reproduce the error and a screenshot of it.

 

Could you please check if there is anything I should change or is it a bug?

 

Thank you

 

thanks for reporting, I replied in the post chat.

bwarner87
Helper I
Helper I

Team,

 

In short when will some of the RLS contraints be relaxed, specifically the item in red text below from the documentation write up.  

 

  • RLS rules will be applied on the source on which they're defined, but won't be applied to any other datasets in the model. RLS defined in the report won't be applied to remote sources, and RLS set on remote sources won't be applied to other data sources. Also, you can't define RLS on a table from another source group nor can you define RLS on a local table that has a relationship to another source group.

 

From <https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s...>

 

Here's the design challenge where i'm trying to avoid maintaining 2 different copies of the same file. 

 

I have a "Central Source" PowerBI Dataset that has master employee dataincluding sensitive comp information (fact table)  which has RLS [Ops person for Departartment 1 only sees staff in Department 1] in place on a department reference table (dimension table) . I'm trying to build other reports off that power BI dataset that aren't sensitive or restricted so that all staff can see. Most design options I attempt fail due to RLS restrictions associated with the Direct Query on PowerBI Datasets preview feature.

 

  1. Design Option 1 [FAIL] - When I create a new report "All Staff Reports" and  I direct query to the "Central Source" Power BI dataset the RLS upstream is imposed preventing people with read access to "Central Source" and my new  "All Staff Reports" to seeing the data unless I want to add them to RLS to the master employee data restricting them, which I don't want to do. 

  2. Design Option 2 [FAIL] -  I decided to remove the Department reference table and RLS from the "Central Source" Power BI dataset and add it as a local table to the "All Staff Reports" imposing RLS. I created a relationship to the master empoyee data table in "Central Source" on Department reference table in "All Staff Reports" . The relationship works, the RLS works on the department table. However the RLS won't apply upstream to the master employee data table due to the red text highlight above from MS documentation. 

  3. Design Option 3 [Fail] - create another Power BI Dataset just for the Department Reference data and in a new "All Staff Reports" dataset connect to "Central Source" and "Department Reference" datasets. Then create a relationship between the Master Employee Data in the "Central Source" and Department in "Department Reference". I then try to apply RLS on another power BI dataset that I have a direct query connect to but can NOT because that's part of the documented limitations 😞 

 

At this point i've resolved to having to copy the file into two files and maintain the two but that defeats the purpose of what I think you all were going for tied to the preview feature. Posting here in the hopes you all are losening some of the RLS restrictions. 

 

Thank you for taking the time to review!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.