Best Power BI Architecture for Multiple Reports with Dynamic RLS and Different Refresh Frequencies

[PK950309]

Scenario:

• Need to build 3 Power BI reports that share common dimensions (Geo, User mapping) but have different fact tables.

• One report has a very large dataset and requires bi-monthly refresh.

• Two reports require daily refresh.

Security Requirements:

• User Group A (restricted users) → RLS based on USERPRINCIPALNAME, limited to their assigned geography.

• User Group B (central users) → should select a user from Group A via a slicer and view data in that user’s security context.

• Slicer behavior and security need to be consistent across all reports.

Architecture Challenge:

• Single semantic model:

  • ✅ Easier to implement shared RLS and synced slicers

  • ❌ Hard to manage different refresh frequencies and performance for large datasets

• Multiple semantic models:

  • ✅ Easier refresh management

  • ❌ RLS does not flow across models

  • ❌ Slicers cannot be synced across models

Scalability Requirement:

• Frequent enhancements and new fact tables are expected.

• Want to avoid maintaining multiple copies of reports/models, to reduce complexity and maintenance overhead.

Question:
What is the recommended Power BI architecture or modeling strategy to support:

• Shared dimensions and security logic

• Dynamic RLS for User Group B

• Different refresh frequencies for large and small fact tables

• Scalable design without duplicating reports or models

• Consistent slicer behavior across all reports

[v-nmadadi-msft]

Hi @PK950309 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @PK950309 

I wanted to check if you had the opportunity to review the valuable information provided by @Tutu_in_YYC , @GilbertQ . Please feel free to contact us if you have any further questions.

Thank you.

[Tutu_in_YYC · Accepted solution]

For single semantic approach, you can run notebook that triggers targeted-table refresh. This way, only tables that need daily refresh, will be refreshed daily. And tables that need bimonthly refresh will only be refreshed bimonthly.

Here is a snippet of my code that targets just a table in the semantic model

 https://github.com/microsoft/semantic-link-labs/wiki/Code-Examples#refresh-a-semantic-model

[GilbertQ]

Hi @PK950309 

The way I would approach it is if you can do incremental refresh on your large data set, then you can everything in one single semantic model. Meaning you have only one semantic model to manage which you can then apply your valuable security across and not having to maintain multiple models.

Just also remember that the size of the model might impact your capacity or your licensing requirements, so have a look at Power BI Pro or Power BI Premium per user as a potential option as quite often Power BI Premium per user gives you up to 100 gigabytes of in memory model capacity versus going with a fabric capacity which costs significantly more per month.

[PK950309]

Thanks for the response.
We are using a Premium capacity workspace with an F64 SKU. Regarding the suggested approach, the PBIX contains 3 pages/reports. Two of these reports require daily refreshes with the full dataset, while the third report is refreshed twice a month, also with the full dataset.

The challenge with refreshing the tables for the third report on a daily basis is the large data volume involved. Additionally, we have SLA commitments for data refresh that must be maintained while considering the refresh requirements of the other reports in the same workspace.