RLS and issues with RLS

[ddfreedie]

Hi,

We are in a situation and I would assume many of us here, will have similar challenges.

We have the concept of building Common dataset which is expected to be used by business for their reports. A particular set of data is owner by some team eg., lets say finance who do not want to share their entire data to others. They build their own dataset and share that dataset to others so the other teams combine Finance dataset and their own dataset (composite model).

The problem now comes when there is huge data. This hampers the performance and also with a default limit of 1 million records in direct query in tenant.

What options do we have

1. RLS in dataflow for tables owned by finance and share that -> RLS in dataflow is not available

2. Increase the Direct query rows to max -> poosibly poor performance

3. Ask business to aggregate the data in the dataset -> business is reluctant they want more granular data

4. Add finance data to each dataset but rules needs to be applied on all datasets and maintained and even if one change in rules, all dataset needs to be changed

How can this be tackled. Any advice? TIA.

[v-yueyunzh-msft]

Hi, @ddfreedie 

From what you describe, you have a common Finance dataset in your company that requires different teams to combine with their own composite data models depending on the situation, right?

For the four options you mentioned, according to my understanding, I recommend the fourth option, let each different team connect to the common Finance dataset according to their own needs and do data cleaning and transformation and Use it in association with your own dataset for the following reasons:

1. I think that each different team has different requirements for using the data in the Finance dataset. On the connection, their Finance dataset can write the context through the measurement value, and calculate the data based on the category of their own team, which can better avoid the data. Performance degradation caused by excessive volume
2. Although the changes to the RLS rules require each team to be uniformly applied to their datasets, in fact, I think the role of the RLS is divided for each team. You can consider letting each team change their corresponding RLS roles instead of all of them. change, of course depending on your specific needs.
3. The relationship between tables can also be defined by each team according to their specific needs, which contributes to a better report development and data display experience.

For the official introduction and step operation of the DQ mode connection of the PBI dataset, you can check this document:DirectQuery_forPowerBI

Best Regards,

Aniya Zhang

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

[ddfreedie]

Thanks @v-yueyunzh-msft for the response.

If i understood your response correctly, you recommendation is that individual teams can import Finance tables from the database directly to their model/dataset and use it however they want but make sure they have the required RLS (defined by Finance for their data + their own RLS as needed) so that the data is not misused.

But the problem here is, Finance team do not want the other teams (developers as well) to see their data without rules being applied. So unless there is a central team (IT team who can see all global data) to develop the models/dataset, this is also difficult. 

Even if there is a central team to develop data models for business the complexity comes that if the Finance tables are in 50 different datasets and Finance rules are changed, all 50 dataset RLS for Finance needs to be updated as well. 

Not sure how this can be tackled as a general issue and not specific for my case. I am sure there may be similar challenges for others as well. 

P.S. This is a specific issue with Finance team but I am sure there may be more scenarios depending on the data owner.