Forum Discussion
Best Practices for RLS with Managed Aggregations (Hybrid Model) + High-Cardinality Dimensio
Hi Community,
I'm working through a complex RLS architecture scenario and would appreciate your insights or experience on this.
Context:
We're building a semantic model with the following constraints:
- Hybrid Aggregation Model: Using Managed Aggregations with a mix of Import and DirectQuery modes on fact tables.
- High-Cardinality RLS: The RLS filtering applies to high-cardinality dimensions (e.g., 5,000+ stores distributed across multiple countries).
- External User Scenario: Users are accessing the semantic model from external tenants, so we can't leverage source-level (Snowflake) row access controls. We're looking at Power BI RLS as the primary enforcement layer.
Questions:
- What are the best architecture patterns for this combination of factors?
- Any performance considerations I should be aware of at this cardinality scale?
- What are the common pitfalls to avoid?
Has anyone dealt with a similar scenario? Any lessons learned or recommendations would be very helpful.
Thanks!
Use hierarchy-based RLS filtering at region or country level rather than flat store-level mapping, and group users into access profiles so the mapping table stays small and the DirectQuery IN clause stays efficient rather than passing 5,000+ store values per query. Ensure your aggregation tables are secured at the same granularity as your RLS filter since agg tables in Import mode can expose aggregated totals for stores a user should not see individually.
For external B2B users, verify that USERPRINCIPALNAME() returns the correct guest UPN format in your tenant and test with a real external account before rollout.
Hi iammilz
Thank you for reaching out to the Microsoft Fabric Forum Community.
Murtaza_Ghafoor cengizhanarslan Thank you so much for the inputs.
If store-level RLS is a hard requirement, then I’d keep the existing star schema and add a dedicated user-to-store access table rather than trying to use dimCountry for security. Apply RLS on that access table, let it filter dimStore, and from there the fact table. If multiple users share the same store access, grouping them into access profiles/security groups instead of maintaining individual user-to-store mappings will usually scale much better and help with performance.
If there are any deviations from your expectation please let us know we are happy to address.
Thanks.
7 Replies
iammilz
You are dealing with the mosttrickiest situiation. I would recommend, if you could apply region in between user and stores such as
Users ---->Region ----> Stores
It will reduce cardinality level and improve the aggregation hit rate and will make your hybird model more simpler.
Howevere you must follow these steps to get the best results.
1-RLS must applied on Dimension Tables.
2-you may include RLS key on aggregation tables.
3-Avoid complex DAX bases row level filters.
4-Try to avoid Bi-Directional Relationship, instead use bridge table.
If this helps, ✓ Mark as Kudos | Help OthersHi Murtaza_Ghafoor ,
Thank you for your guidance.
What if i have dimCountry and dimStore separately and both link to fact as star schema concept.
Do you have any idea in this situation?
Use hierarchy-based RLS filtering at region or country level rather than flat store-level mapping, and group users into access profiles so the mapping table stays small and the DirectQuery IN clause stays efficient rather than passing 5,000+ store values per query. Ensure your aggregation tables are secured at the same granularity as your RLS filter since agg tables in Import mode can expose aggregated totals for stores a user should not see individually.
For external B2B users, verify that USERPRINCIPALNAME() returns the correct guest UPN format in your tenant and test with a real external account before rollout.
Hi cengizhanarslan ,
Thank you for your guidance.
unfortunately store level rls is very neccessary, not sure if you have any suggestion?
thank you
Hi iammilz
Thank you for reaching out to the Microsoft Fabric Forum Community.
Murtaza_Ghafoor cengizhanarslan Thank you so much for the inputs.
If store-level RLS is a hard requirement, then I’d keep the existing star schema and add a dedicated user-to-store access table rather than trying to use dimCountry for security. Apply RLS on that access table, let it filter dimStore, and from there the fact table. If multiple users share the same store access, grouping them into access profiles/security groups instead of maintaining individual user-to-store mappings will usually scale much better and help with performance.
If there are any deviations from your expectation please let us know we are happy to address.
Thanks.
Hi iammilz
Thank you for reaching out to the Microsoft Fabric Forum Community.
I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.
Hi iammilz
Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.
