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

[DanRider]

Set up RLS on each individual model and everything will function as you are expecting.

[Anonymous]

I don't get the option to apply RLS on country table (coming from remote model/dataset) in my composite model. The table name doesn't appear when defining RLS rules in Power BI desktop. I'm not sure if I'm missing anything?

[jeroenterheerdt]

RLS does not flow from the remote to your local model. This is by design. Thanks for providing the feedback, though.

[Anonymous]

Thanks for the prompt reply. So in a use case like this, I assume using dataflows might be the next best option? Also, are there plans of introducing RLS from remote models to local in the pipeline?

[jeroenterheerdt]

we are looking at the RLS to local thing, but it's a lot of work and to be honest, not a lot of people have asked for it. Please vote on it on ideas if you want this to happen. Dataflows could be a option for you.

[Anonymous]

After a lot of trial and error, I realized what exactly was the reason for some users not being able to see the report with the composite model. Turns out that only the users for whom "Allow XMLA endpoints and Analyze in Excel with on-premises datasets" is enabled, can see the full report without problems. Others get an error. ("Cant display this visual")

This is a problem for my organization because I can't grant this right to all users and makes the feature completely useless for us.  Very sad because we were very excited about the opportunity.

[jeroenterheerdt]

I am sorry to hear that, but it's clearly listed on the blog and docs that you need to enable XMLA / Analyze in Excel. This feature depends on it and cannot work without it.

[Anonymous]

I understand. Just surprised and sad that it is also needed for the viewers... Makes it unusable in our case...

Our situation is as follows: we have a big dataset with all the data and all the reports are based on it. Some people are only allowed to see volume columns, others can also see columns with actual $ values. It is our understanding that we cant use RLS or similar to hide the $ columns from people who are not supposed to see them  (Spinning them into another dataset is not an option). If we give everyone in the organization the XMLA / Analyze in Excel option, the people who have access to some reports will be able to access also all the columns in that case, which is not acceptable... Are we thinking correctly? Thanks!

[jeroenterheerdt]

why cant you use RLS? can you not set RLS on the composite model?

[Anonymous]

Because we would need column-level security. We do have row-level security set up. Basically, think of the data like this:

Region	Volume	Profit
Region 1	11	12
Region 2	21	22

We have RLS set up sp that a user can only see data from Region 1. But we also need to be able to say that within that region the user can only see the column Volume, but not the column Profit.

Its ok if we only expose the Volume column in the report they are allowed to see. But once we give everyone access to Analyze in Excel, they will be able to see all the columns (indeed, only for their region but also the columns they arent supposed to see).

Right? 

[jeroenterheerdt]

right, so this is not a question about RLS, but about OLS 🙂 we are still defining the exact interaction with OLS, so stay tuned for that.

[soldous]

Hello,

I don't know if this question was already posted but I'm facing quite an important user unfriendliness with the nesting of the datasets.

When I use more datasets from different workspaces to create a new one and publish it to the app it's not enough to grant access to the users through the final app but I need to also grant build permission manually to the original datasets. 

Is it going to be solved to grant build permission automatically for all users from the final app?

Thank you for your answer and for your work.

[hornsjd]

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)

[jeroenterheerdt]

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

[KeithCoxAuto]

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. 

[jeroenterheerdt]

which error do you get?

[KeithCoxAuto]

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

[jeroenterheerdt]

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?

[KeithCoxAuto]

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.

[KeithCoxAuto]

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?

[KeithCoxAuto]

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