Best Approach for User Access Table with Different Refresh Frequencies

Super User

10 months ago

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

Forum Discussion