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

438 REPLIES 438

I ran into the same problem and microsoft pointed me to this idea and I am advocate for voting on it.  The feature provides little value to enterprise (PBI Premium) customers.

 

Microsoft Idea  · Directquery for datasets only needs read permissions on all datasets (powerbi.com)

 

thanks - although we are already looking into it, every vote counts!

KeithCoxAuto
Helper I
Helper I

Is there any plan to have one remote dataset filter more than one table in another remote data set?  I get an error that this is not supported currently.

 

I.e. a custom calendar that should have a relationship with both 'Leads' table and 'Accounts' table's "createdon" fields. 

which error do you get?

'The table filters more than one table in the island which is not supported'

ok, so you got the right error message. we are not planning to support this as its not a good practice. you want to limit the number of cross source group relationships. Can you not bring your custom calendar into the dataset that contains the leads and accounts table?

Okay, thanks.

 

I can ask the dataset owner but I can't then connect to another published data set without getting that dataset's owner to also include a copy and then putting in another copy as a master that can link to each dataset

 

I.e. internal sales system and CRM data.  I connect to each as a published dataset - if I could connect to the sources directly, I could bring them in then add a calendar and many as many relationships as needed.  As they are published, I can't access them both and add the calendar once as the local file.

Hi,

 

So we have looked at this - having the calendar in each dataset means we cannot have any other connection between the 2 datasets, which removes the point of combining them in a composite model.

 

So we have a custom calendar, published as a dataset.

We have proprietary sales system data, published as a dataset.

We have Dynamics 365 CRM data, published as a dataset.

 

The CRM data and sales system share only custom account number, so the need is to create a 1:1 relationship between the two on these fields.

 

Using import mode, I could take a custom calendar and create indirect relationships to as many tables as needed, across the sales data and the CRM data.  But as they are published to be used as composite, any attempt to have 1 calendar that affects more than 1 table errors, with the message I gave in my original post.

 

From your suggestion, I have Live connected the custom calendar in the CRM data and the sales data, then published that.  But I can't connect the calendars if the account number is connected (directly or indirectly) because I get an errors that it would introduce ambiguity as due to the connection between the account numbers.

 

If I connect the calendars first, I cannot connect the account numbers, removing the point of having both datasets.

 

Is it impossible to have custom calendars that connect to more than one table across composite reports?

Hi @jeroenterheerdt - could you confirm if this is the case?

sorry for my late reply - I am not sure I understand your use case. Can you send me a DM so we can exchange email addresses?

Are these calendars different between datasets? We had a situation where we connected 2 datasets, and each one had a calendar already with all the relationships in place. What we did in the composite dataset, is that we created a 1 to 1 relationship between the dates of the 2 calendars and hid one of them. This solved our issue.

Hi @Liszet - they are indentical but I get an error trying to create the 1:1 relationship between them seemingly because of the 1:1 relationship between the account numbers across the 2 datasets.  Did you datasets have any other points of connection or was it just through the dates?

That was the only 1:1 relationship between the 2 datasets in our case, yes. But we did have multiple 1:M relationships in a different composite datasets model, but then no 1:1

Ah.  That seems to be the issue that's 'breaking' it.  Thanks for taking the time to try and help, though, I appreciate it.

Liszet
Helper II
Helper II

Liszet_0-1622635204860.jpeg

This error message shows up quite often when working with composite models connected to 2 datasets in power bi desktop - 'OLE DB or ODBC error. The session ID cannot be found. Either the session does not exist or it has already expired.. Is this to be expected, or is there a workaround? The datasets use a combination of DQ and import (but prominently DQ)

I find if you go to transform data, data source settings, and add another table to the composite data source, it refreshes the session and you can continue working in it (just delete the table you added).  

this is not expected - please open an issue on issues.powerbi.com

Liszet
Helper II
Helper II

I noticed that the following is still a limitation

  • Format strings on columns and measures from a remote source are not imported to the composite model. We are looking for your feedback to prioritize this.

 

When is it expected that this will be fixed? It's quite a dealbreaker for us, since the reports show up with wrong formatting! 

 

I am facing the following scenario:

I am connected to 2 datasets in a composite model. These 2 datasets use direct query both of them. In the composite model I create relationships between tables in the 2 models. One thing I notice is that formatting on measures is gone. F.eks, in dataset a measure is formatted as percentage 3%, whereas in composite model same measure shows 0,03 when used in reports. Or in dataset a measure is formatted as currency with no decimals, and in composite model same measure shows without currency and with decimals. Date columns modify their formatting. Columns that should not summarize, all of a sudden sum. Also, descriptions of measures and columns are not there in the composite model either. 

We are holding off using them for exactly this issue

stay tuned, good news should come soon!

it will be fixed in a future update. I am not at liberty to say here which, but we're shipping it soon and the update will be there before this feature becomes generally available.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors