Data access issue with mixed storage model

[Anonymous]

Hi, all,

I created a power BI report with pure "direct query" mode linking to an existing power BI dataset. It works well and users can use this report even they don't have access to the original report (dataset).

But when I created another report, in which I added some data tables and linked to the same PBI dataset by direct query. This time it became "mixed model" becuase I have a local dataset. Then weird thing happended, users couldn't see the visuals I created. I tried many mehthods to resolve it. Seems that the only way is that I need to grant these users access to that dataset, which is not realistic.

Does anybody has experience to share? Thanks a lot!

(1) If the report is pure "direct query", there is no issue:

(2) With "mixed" storage mode, users can't see the visuals.

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

Yes, this is a known limitation:

//Build permissions are required to view reports built using this feature when one or more datasets in the chain are in Pro workspaces. If the creator a report has left the company or their Build permissions has been revoked since they created the report, Build permissions will also be required on all datasets in the chain.

Using DirectQuery for datasets and Analysis Services (preview) - Power BI | Microsoft Docs

So currently users cannot properly view reports modeled in this mode without build access. Or consider using direct query mode to connect to a data source instead of a dataset and then import the data to create a report.

This feature is still in preview and it would be helpful if you could pass your feedback on to the product team.

Share your thoughts on DirectQuery for Power BI da... - Microsoft Power BI Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

I have figured out the root cause but couldn't find a solution, anyone can help?

The problem is that, Power BI failed to add a link to the direct query dataset under mixed storage mode. (if a report only connects to a direct query dataset, it can create that link once you share report to others)

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

Yes, this is a known limitation:

//Build permissions are required to view reports built using this feature when one or more datasets in the chain are in Pro workspaces. If the creator a report has left the company or their Build permissions has been revoked since they created the report, Build permissions will also be required on all datasets in the chain.

Using DirectQuery for datasets and Analysis Services (preview) - Power BI | Microsoft Docs

So currently users cannot properly view reports modeled in this mode without build access. Or consider using direct query mode to connect to a data source instead of a dataset and then import the data to create a report.

This feature is still in preview and it would be helpful if you could pass your feedback on to the product team.

Share your thoughts on DirectQuery for Power BI da... - Microsoft Power BI Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous · Accepted solution]

Hi, Gao,

Much appreciate your help, the article you shared is very useful!

I also found anothe problem which is I can't set the credential for the "local" dataset with "composite mode" (mixed storage mode), which means even users can see the data from direct query dataset, I won't be able to refresh the import part (local dataset).

I have figured it out this problem has already been addressed in the "Considerations and limitations" section, just above the tip you shared: Using DirectQuery for datasets and Analysis Services (preview) - Power BI | Microsoft Docs

With all these findings, I have to draw a conclusion that mixed storage mode (composite mode) is not mature for real projects.

I'm trying the following 2 approaches instead:

(1) Pure "Direct Query mode" for multiple datasets: I'll create a separated dataset for the "local" part, and then create a report connecting to all these datasets. 

(2) Import data from the existing dataset.

Will share the results soon.

[Anonymous]

Updates:

(1) There is no credential issue for "pure direct query" mode, which the final report only connects to multiple PBI datasets by direct query (not live connect, live connect only supports connecting to 1 dataset).  A "local dataset" will be created under pure direct query mode, all the credentials are OK, and therefore we can set refresh schedules now - but with pure direct query, actually there is no need to refresh the dataset, right?

And with pure direct query, it still doesn't work for just sharing report to others. We have to grant access to these users under the dataset being direct queried.

(2) There is no way to "import" data from PBI dataset - I admit this is reasonable, it makes no sense to do that.

As a summary, direct query for PBI dataset is a useful function, but not mature yet for "composite mode"

[Anonymous]

One more finding: 

In the 4th point shared by Gao, "Build Permission" is required.  

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

Here the "Build permission" must be assigned through "Direct Access" under data set. If you share the report to others and grant them the "Rebuild" access, it will add a link under "Links" of dataset, but it doesn't help. Users still not be able to display the visuals correctly, you have to grant "rebuild" access under "Direct Access". 

[Anonymous]

The error message is：