Combining Fabric Semantic Views

One of the most interesting things about Fabric semantic views (announced this week at Ignite) is the way you can combine multiple semantic views together, allowing you to reuse semantic modelling and metrics easily. This has been a long-standing ask for Power BI semantic models and while composite models were created to solve this problem, and had their uses, they never solved the problem properly – mostly because by building composite models on top of a single, physical semantic model that semantic model became a bottleneck. The documentation for semantic views talks about reuse and combining but I thought it would be good to show a simple example to help you understand what’s possible.

First of all, I created a lakehouse called Sales containing three tables: two dimension tables called Employee and Product and a fact table called Sales. I then created a semantic view with the following definition:

version: 0.2.0.dev0
name: "SalesSemanticView"
datasets:
- name: Employee
source: dbo.Employee
- name: Product
source: dbo.Product
- name: Sales
source: dbo.Sales
relationships:
- name: EmployeeToSales
from: Sales
to: Employee
from_columns:
- EmployeeID
to_columns:
- EmployeeID
- name: ProductToSales
from: Sales
to: Product
from_columns:
- ProductID
to_columns:
- ProductID
metrics:
- name: SalesAmount
expression:
dialects:
- dialect: DAX
expression: "SUM('Sales'[SalesAmount])"

This resulted in a Direct Lake semantic model that looked like this:

Next I created another lakehouse called Targets containing just one fact table, a table also called Targets. I created a semantic view with the following definition:

version: 0.2.0.dev0
name: "TargetsSemanticView"
datasets:
- name: Targets
source: dbo.Targets
metrics:
- name: SalesTarget
expression:
dialects:
- dialect: DAX
expression: "SUM('Targets'[SalesTarget])"

This resulted in the following Direct Lake semantic model:

Finally, in a third lakehouse, I created shortcuts to all the tables in the Sales and Targets lakehouses, created a new semantic view and added shortcuts to the two semantic views above inside the folder for this new semantic view:

Here’s the definition of the third semantic view (in the SalesAndTargetsSemanticView folder):

version: 0.2.0.dev0
name: "SalesAndTargetsSemanticView"
relationships:
- name: EmployeeToTargets
from: Targets
to: Employee
from_columns:
- EmployeeID
to_columns:
- EmployeeID
metrics:
- name: SalesPctTarget
expression:
dialects:
- dialect: DAX
expression: "DIVIDE([SalesAmount],[SalesTarget])"

Note that this semantic view doesn’t define any new datasets, just a relationship between the Employee dimension table from the first semantic view and the Targets fact table from the second semantic view, both of which have been shortcutted in to this semantic view, as well as a new metric that references the two measures created in these two other semantic views. This resulted in a Direct Lake semantic model that contained all the tables from the Sales and the Targets lakehouses and their associated semantic views, plus that extra relationship between Employee and Targets and the SalesPctTarget measure:

I could then build a report on this third semantic model and analyse data from both the Sales and Targets lakehouses without duplicating any data, semantic modelling metadata or metrics from either of them:

You have always been able to use shortcuts to tables to use the same table in multiple Direct Lake semantic models. This shows how you can now use shortcuts to semantic views to use the same table metadata, relationships and metrics in multiple semantic models, which I think is pretty cool. It’s still very early days for semantic views of course but I’m excited to see how they develop. If you want to know more about semantic views and Microsoft’s strategy with Apache Ossie, watch this session from Ignite.

Leave a Reply