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!
