Best Approach for User Access Table with Different Refresh Frequencies

For the monthly reports:

• Keep the fact table in Import mode (since it only updates monthly).
• Keep the user access table in Direct Query/Direct Lake mode (so permissions are always up-to-date).
• This composite model approach works well, and report interactions will work smoothly if relationships are set correctly.

For Monthly refresh: Use the monthly refresh feature in Data Pipelines (preview) or automate the refresh via a notebook/Power Automate.

Hi bdpr_95, 

Why not just use directlake for both the user access table and the fact tables? This would be the approach I would use. 

This avoids the complexity of maintaining composite models, and if in the future the business decides that these fact tables need to be refreshed more often, then it allows for this without any additional changes. 

This also solves problem 2 as the semantic model will always be up to date with the tables. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

Hi bdpr_95 

I would go ahead with direct lake for both approach keeping daily detailed table separate and monthly aggregated detail in a materialized view and both are to be accessible in direct lake mode.

Direct lake does not need refresh.

Regading your 2nd question on monthly refresh of Power BI report, please check this link and below snapshot.

https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh

In your pipeline also, you can refresh the dataset in notebook or leverage semantic model refresh activity

https://learn.microsoft.com/en-us/fabric/data-factory/semantic-model-refresh-activity?utm_source=chatgpt.com

If this post helps, appreciate your kudos.

Thanks,

Pallavi

Hi   bdpr_95 

Using a composite model with:

User Access Table in Direct Lake

Fact Table in Import Mode

Benefits

Fresh access control: Always reflects latest permissions without needing full model refresh.

Performance: Fact data is cached in memory (VertiPaq), ensuring fast visuals.

Separation of concerns: Each table uses the most appropriate mode.

Limitations

Model complexity: Relationships between Direct Lake and Import tables can introduce latency or query inconsistencies if not modeled carefully.

Unsupported features (as of Sept 2025):

Drillthrough and Analyze in Excel may not work with Direct Lake.

Calculated columns and Power Query transformations are limited in Direct Lake.

Security modeling: Row-level security (RLS) across mixed storage modes requires careful testing.

Recommendation
Use composite model only if:

You need real-time access control

Your user access table is small and stable

You can test interactions thoroughly

Otherwise, consider the alternative below.

 Alternative: Full Import Mode + Notebook Refresh
Keep both tables in Import mode, and use a notebook to refresh only the user access table daily.

Benefits
Simpler model: No mixed storage complexity.

Full feature support: Drillthrough, Excel integration, calculated columns, etc.

Controlled refresh: You decide when and what to refresh.

Regards,

Ritesh

Community Champion

Please mark the answer if helpful so that it can help others

Dance-Sing with Data -BI & Analytics

Hi bdpr_95 ,

Thank you tayloramy , ribisht17 , pallavi_r , Msingh16  for your inputs.

I hope the information provided is helpful. I wanted to check whether you were able to resolve the issue with the provided solutions. Please let us know if you need any further assistance.

Thank you.

Hi bdpr_95 ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.