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

479 REPLIES 479
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

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!

hi, thanks for the detailed feedback and explanation. Unfortunately we are not planning improvements in this area in the short term as this is a hard problem to solve. We simply don't have a good design yet for it that satisfies all requirements. That being said, thanks again for the feedback. This is very valuable input that helps us prioritize this. 

WaleyWang
Helper I
Helper I

Hi, all,

I have one report using data imported from databases. And another team developed a report which gets data from hundreads of excel files, I need the same data and don't want to bother myself to import the data again, so I tried to connect their PBI dataset instead. Which created a "composite model" and I failed with this solution due to the following 2 problems encountered:

(1) I couldn't set refresh schedule for my dataset due to the credential setting error:

快照44.jpg Seems that this is a known issue, but when can it be fixed? (https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s... )

 

(2) When I shared the report to other users, they couldn't see the visuals with data from the direct query dataset.

I have figured out the root cause: When we share report to another user, a "link" will be created and added into the backend dataset. But with composite mode, the link is only added to the "local" dataset, not to the direct query dataset. 

I tired "Live connet" mode and found that PBI can add the link to the source dataset when the report is shared.

So I guess here PBI has a bug. Could you please help to fix it?

 

By the way, seems that PBI currently doesn't support live connect to multiple datasets, is it possible to add this feature? It will be helpful to consolidate reports (just put them together, no data exchange between datasets which we know direct query is needed).

domputer
Frequent Visitor

Lost table relationships when converting Live Connection to Direct Query.

Premium Workspace Dataset from SQL Server Analysis Services database.

Get Data > Power BI Dataset > Table Relationships exist

Make Changes to the Model > Table Relationships disappear except one.

that is not supposed to happen! Can you tell me more about the relationships? Maybe look at the properties over in SSAS to see if any of them have special flags on them?

Please provide additional information regarding finding special flags ?

 

Tried accessing data base directly, same issue
Get Data > SQL Server Analysis Services database > Table Relationships exist
Make Changes to the Model > Table Relationships disappear except one.

you can use any SSAS client tool such as management studio or others to connect to the database. Please check for anything related to private on tables. we have had an issue before where if a table was private it would still be loaded and we have hence fixed it, but wondering if you are experiencing another issue related to it.

Can Microsoft confirm if this is the expected behaviour ?
Workaround after ongoing troubleshooting with user.
Issue related to table key columns being hidden due to Object Level Security being set in Azure SQL Server Analysis Services Database.
Relationships remained after converting from Live Connection to Direct Query when allocated Confidential role access in Azure.
So if a key column has Object level security then all relationships that use that column would be deleted.
Can Microsoft confirm if this is the expected behaviour ?

gabcrep
Regular Visitor

Hi,

 

As mentionned in the limitations of the feature, only users that have Build permission on the datasets can see the report/visuals (4th bullet point):

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

 

The build permission is not something we necessarly want to grant to any user who wants to consult the «composite» report ... is there any workaround for this limitation?

 

Thanks,

 

Gabriel

 

 

 

 

thanks for the feedback, this is something we're planning to change: just 'read' permissions will be required when we make this feature generally available.

Yes! Weird to give changing priviledge to someone would be only reading the dataset outside the original report - a Power BI thin report or in Excel.

hstance
Helper I
Helper I

hstance_1-1658841929777.png

Getting this error in the service after days of developing in desktop with no issue/warning in local refreshes. Very discouraging and wouldn't recommend to anyone to develop in desktop using this feature. I have to now import the datasets I am using instead. I have learned not to use any features in preview going forward if I want to manage expectations well. Preview???? NO THANKS.

I understand that this is annoying - we don't actively stop you from doing this in Desktop as we don't know if you are going to publish to the Service. So instead of "breaking" Desktop for those that never publish to the Service, we allow you to do it and then it breaks when you publish to the Service. If we warned for everything that is not going to work in the Service that would be a very jarring experience and get in the way a lot while it might not even be relevant for you. Please see https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s... for a list of limitations in this preview, your exact scenario is listed as well.

RickScanlon
Frequent Visitor

It would be great if there were an officially sanctioned and clear approach (or more than one) to managing large datasets (+30GB) using directquery (or not).  This is especially the case because it seems like directquery is currently the best way to deal with huge datasets in PBI. Haven't been able to load huge tables to dataflows, datamart, or blob without timeout errors, etc.  I am fully in the MS ecosystem, so I connect to Azure SQL Database using directquery.  The tables in the DB are indexed (columnstore) and I'm using auto aggregation.  I'm pretty sure that's all I can do (or mostly all), but I'm not entirely sure.  Because it's a little bit of witchcraft.  Would love a video or post like this: dealing with huge datasets from SQL Server et al to PBI. 

This thread isn't related to large models (import or direct query). This is related to connecting to Analysis Services in a Direct Query mode rather than a Live Connection mode.

 

It looks like there are a number of posts under the desktop section of the sight that maybe helpful:

 

As a Consultant myself working with large models (primarily against Snowflake), the advice is to import the data into the model in the first place. with large data this requires implementing incremental refresh. (Note that a P1 capacity has a model size limit of 25GB Capacity and SKUs in Power BI embedded analytics - Power BI | Microsoft Docs.)

 

If you can't scale up the capacity, or reduce data size through optimisation and rationalisation, then utilise direct query with aggregations; auto-aggregations (which may be helpful dependant on the repetitive nature of the queries) or manual aggregations.

 

There is plenty of material on these topics if you Bing search. GuyInACube has some good material as a starting point:

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.