Filtering a semantic model for end-consumer

[SuperFiets_]

Hi everyone,

We're implementing an end-to-end solution using Microsoft Fabric, including Dataflows Gen2, Pipelines, Lakehouses, and Warehouses. However, we’re facing a challenge and need your advice regarding the following.

Our Workflow:
1. Ingesting data with Dataflow Gen2 for multiple clients into a lakehouse.
2. Enrich this data in another lakehouse.
3. Load the completed data into a warehouse.

4. Creating a semantic model with relationships and measures based on the final warehouse.

The Challenge:
We need to extract data for a single client into an Excel template, despite having multiple clients' data in the warehouse. Ideally, we want to connect the Excel template to the semantic model (containing measures and data model) linked to the final Data Warehouse and filter this model to load only the data for the selected client into Excel. We're using the "Analyze in Excel" function with the semantic model or a connection string.

Previous Approach:
Previously, we connected Power BI Desktop to a SQL connection string, filtered client data in Power Query, and published the filtered data to the online service. However, this approach isn't scalable for our growing number of clients. Since we can't update the semantic models when the Power BI Desktop template changes. 

Options Considered:
1. Direct Connection with Slicers: Connecting the Excel template directly to the semantic model and using slicers to filter client data. This approach loads all rows, affecting performance and potentially exposing other client data.
2. Old Approach: Connecting to the data warehouse, filtering in Power Query, and publishing a separate semantic model. This is not ideal as we want a centralized semantic model from the warehouse.
3. VBA or MDX Queries: Exploring VBA or MDX queries on the connection string, but due to limited experience, I couldn't get it to work. Something like SELECT * FROM [Client] WHERE 'Name' = 'FilteredClientName' in the command text would be something that would be enough.

Question:
How can we filter a semantic model to show data for only one client before connecting it to Excel with a connection string? Any guidance or alternative approaches would be appreciated. At this point, open to anything.

Thank you for your help!

[lbendlin]

Now I am confused. Please describe again what you are trying to achieve.

[lbendlin]

Have you considered the "App owns data"  embedded scenario or RLS in general?

[SuperFiets_]

Yes we have, but for our scenario, I am not sure it can work.

Let's say we have 100 clients, and about 5 teams. We'd create RLS for the 5 teams, but that still means they get to see 20 clients each. 

Do you think another approach would be to have 100 clients, each one having it's own O365 group. When adding users to the O365 groups, would they somehow only get to see the data for the specific client? Even when users are added to multiple O365 groups. For example user A would be added to O365 group 1, 2 and 3. Would user A still get to see all items from 1 to 3? 

The final step for us, is connecting the data to Excel. So it's very important that user A only gets to see the data for item 1, when we deploy the Excel templates for item 1.

[lbendlin]

Read about the difference between static RLS and dynamic RLS.  In your case a reference table in combination with USERPRINCIPALNAME may be more efficient.

[SuperFiets_]

I checked the option for Dynamic RLS, but I am afraid it won't work for the scenario.

Say I have a O365 group per client, we have about 20 users. But the same user can be a part of multiple O365 groups. In the end this means that that user can still see all the clients he is connected to, instead of just one client per template we publish..

[SuperFiets_]

I will look into that, thank you!