DirectQuery Security Governance

[mmossel]

Situation:

A company wants every employee to be able to use DirectQuery and understands well how technically they should build the architecture in such a way that the performance is good. It will be Postgres Hyperscale DWH that connects to PowerBI. The concern is regarding security governance because there is sensitive data.  

Problem:

1. Power BI doesn’t support direct query via OData (however OData protocol allows for it)
2. On direct query Power BI doesn’t cache the data in the user instance of the report, so every time a user clicks on a chart it generates a new query.
3. If we don’t use direct query, Power BI cache the data in the report for all users and hence we need to replicate the security model.

Questions:

1. Is there a way we can configure Power BI Reports to only cache data for the instance of the user and not the report regardless if we use direct query?
2. Is there a way for Power BI to do direct queries via OData but then cache the data for the user for that instance to reduce load on the DB?
3. Is there a solution to inject our security model before we query the database without creating the user on the database for multiple reporting solutions?
4. Is there a feature in Azure to easily create OData API’s on a database and still inject security?

[v-stephen-msft · Accepted solution]

Hi @mmossel ,

Answers:

1. No, there're three connection modes in Power BI. There is no connection mode that meets this condition.

    You can refer to DirectQuery, Live Connection or Import Data? Tough Decision!

2. OData does not support Direct Query. You find it in Power BI data sources.

For question 3 and 4, what's the meaning of injecting security?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.