One semantic model for many clients: pivoting key-value data blows up my fact table

Hi robertozsr,

Classic multi-tenant EAV challenge and the good news is: don't pivot at the Lakehouse level. That's what's causing the blowup.

What's happening
When you pivot all key-value pairs across all tenants into columns, every client's unique keys become columns for everyone mostly NULL. That's the sparse wide table anti-pattern, and it gets worse as you add clients.

Recommended pattern: keep EAV, enforce boundaries at the model layer

Keep your data normalized in the Lakehouse :
FactMetrics (tenant_id, metric_key, metric_value, date)
DimMetricKeys (tenant_id, metric_key, display_label)
DimTenants (tenant_id, tenant_name)
Then in your semantic model :

• Apply Row-Level Security (RLS) on tenant_id each client only sees their own rows, so they only see their own keys
• Use DimMetricKeys as a slicer maps raw key names to human-readable labels per tenant
• Write DAX measures using SELECTEDVALUE('DimMetricKeys'[metric_key]) to slice dynamically

This way the "pivot" happens at the report layer, not in storage no blowup, no sparse columns.

If you need a physical wide table (performance reason only)
Create per-tenant SQL views in the Lakehouse SQL endpoint, pivoting only that tenant's keys. Use those views as DirectQuery sources in the semantic model.

As a junior consultant : start with the EAV + RLS approach. It's clean, scales well, and avoids maintaining per-client pivot logic. Only add complexity when you hit a real bottleneck.

More on RLS in Fabric: https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

Hope that helps!