Modeling for Multiple Granularities and 1000+ Customers (OneLake layer)

[jaryszek]

Hi all,

I’m designing a solution in Fabric (OneLake, Direct Lake only – no Power Query, no Warehouse) and need advice on how to model for multiple granularities across 1000+ customers.

Context:
- I’ll have 1000+ customers.

- A baseline semantic model should hold the common schema and measures.

- Each customer may need different levels of detail:

- Monthly (e.g. year 2024 by months)

- Daily / Last 7 days vs Previous 7 days

- I’m unsure how many Lakehouses should be created:

- One per customer?

- Or one large Lakehouse with partitioning/RLS/shortcuts?

My main question is:
👉 What are the possible modeling approaches for handling multiple granularities?
- Do people usually build a star schema with multiple fact tables (daily, monthly, rolling 7d)?

- Or do they keep just one detailed fact table and aggregate on the fly?

- Are there other approaches I should consider?

I’d also appreciate any advice on:

1. How to design the baseline semantic model so it scales across 1000 customers.

2. Best practices for balancing performance, maintainability, and storage in this kind of multi-customer / multi-granularity setup.

Thanks,
Jacek

[AmiraBedh]

Hello !

Thank you for posting on Fabric community.

For 1k+ customers in Fabric (Direct Lake only), you may choose small number of shared Lakehouses by domain over one per customer where you store all tenants in shared delta tables partitioned by tenant_id and date and keep it with OPTIMIZE/Z-ORDER.

You can split into separate lakehouses and very large tenants.

You can use multi-grain facts where you keep a detailed daily or transaction fact with materialized daily and monthly aggregates and refresh them with Fabric notebooks or data engineering pipelines.

You can publish one baseline semantic model with conformed dimensions and apply dynamic RLS over a security_user_customer mapping so each user only sees their tenants and keep the reports with live connection so you deploy and manage the dataset once.

[jaryszek]

thank you. 

Can you elaborate this? 

"shared Lakehouses by domain over one per customer where you store all tenants in shared delta tables partitioned by tenant_id and date and keep it with OPTIMIZE/Z-ORDER."

what doest it mean shared lakehouses which store tenants inside? 

[v-saisrao-msft]

Hi @jaryszek,

Thank you @AmiraBedh, for your insights.

Use a single shared Lakehouse to store detailed data, partitioned by tenant_id and date. To query performance, include lightweight pre-aggregated tables, like monthly or rolling 7-day summaries. Improve efficiency by clustering on tenant_id, partitioning by date, and running OPTIMIZE regularly. For very large customers, consider separate lakehouses. This strategy ensures scalability, efficiency, and simpler management.

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Direct Lake overview - Microsoft Fabric | Microsoft Learn

Thank you.

[jaryszek]

"Use a single shared Lakehouse to store detailed data, partitioned by tenant_id and date. "

Please describe exactly how files names should look like? Can you please provide patterns? 

[v-saisrao-msft · Accepted solution]

Hi @jaryszek,

When organizing data in a shared Lakehouse by tenant_id and date, files are stored in a nested folder structure like:
/tenant_id=<tenant_id>/year=<yyyy>/month=<mm>/day=<dd>/.
Fabric automatically manages file names and partitions when using Spark or Pipelines. You can view the folder structure by selecting “View Files” in the Lakehouse UI. This method enables scalable and efficient management of data across multiple customers and granularities.

Load data to Lakehouse using partition - Microsoft Fabric | Microsoft Learn

Thank you.