Table Relationships in Fabric Warehouses: Impact on the Semantic Model

Blog Contents

Overview

Relationships are tricky at the best of times…but working with relationships between tables in Fabric Warehouses and Semantic Models has some nuances that I’d like to share. We’ll first look at what I call the symbiotic relationship between the Fabric Warehouse and the Default Semantic Model because the ways in which relationships are created can affect both the Warehouse and the model. We’ll also look at what happens with custom semantic models and also working within Power BI Desktop.

Semantic Models – Default & Custom

When certain items within Fabric are created such as a Warehouse, a Lakehouse, a SQL Database, a default semantic model is also created which allows us to browse the underlying data, work with a limited set of semantic modelling features, and create reports. Currently we cannot switch this off so will exist for these items. I only use the default semantic model for basic data exploration and I rarely actually use it for modelling purposes, I may create a handful of explicit measures but that really is about it. I always recommend using a custom semantic model. However, using any type of semantic model with multiple tables, we will need to create relationships between these tables to work with the data in any meaningful way.

Scenarios

We’ve got different options when creating relationships, we can do that in the Fabric Warehouse using T-SQL or the Semantic Model itself (both default and custom). Therefore, a set of scenarios we have are:

No Relationships: Pretty obvious, no relationships exist
Create Relationships in Default Semantic Model
- Using T-SQL in the Warehouse between tables
- In the Semantic Model itself between tables
Create Relationships in Custom Semantic Model
- Using T-SQL in the Warehouse between tables
- In the Semantic Model itself between tables

Example Data Warehouse

You can go grab the code from my GitHub here and run on a new Fabric Warehouse. This will create Dimension and Fact tables and load with example data. The file is 01 Create And Load Sample Fabric Warehouse.sql

fabricwarehousesqltools/queryinsights at main · datahai/fabricwarehousesqltools

Creating Relationships in Default Semantic Model

Let’s look at creating table relationships that affect the default semantic model, we’ll do this by running T-SQL statements and also in the Fabric GUI itself. I’d like to point out that when creating relationships using T-SQL, this is done via Foreign Keys using the NOT ENFORCED keyword… that’s a little disconcerting, right? That’s because it isn’t actually enforceable in the Fabric SQL Warehouse engine (a trait shared by many cloud data warehouse platforms) therefore without a robust ETL/ELT process, a value could exist in a foreign key column that doesn’t actually exist in its referenced primary key table/column.

So why even create relationships in T-SQL? Well, there are some performance benefits Microsoft state, I’d like to analyse this in a future blog post.

Create using T-SQL

Run the T-SQL code below, this will add primary keys to 2 dimension tables, then create foreign keys from the Fact table to the 2 dimension tables. Note, we cannot create primary and foreign keys during initial table creation, it must be done using ALTER TABLE.

--create primary keys on dimension tables
ALTER TABLE dbo.DimDate ADD CONSTRAINT PK_DimDate PRIMARY KEY NONCLUSTERED (DateID) NOT ENFORCED;

ALTER TABLE dbo.DimGeography ADD CONSTRAINT PK_DimGeography PRIMARY KEY NONCLUSTERED (GeographyID) NOT ENFORCED;

--create foreign keys on fact table
ALTER TABLE dbo.FactTrip ADD CONSTRAINT FK_DimDate FOREIGN KEY (DateID) REFERENCES dbo.DimDate (DateID) NOT ENFORCED;

ALTER TABLE dbo.FactTrip ADD CONSTRAINT FK_DimGeography_Pickup FOREIGN KEY (PickupGeographyID) REFERENCES dbo.DimGeography (GeographyID) NOT ENFORCED;

Once the constraints have been created, you can run the following T-SQL to see the relationships in the underlying Warehouse.

select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
    '>-' as rel,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
    fk_cols.constraint_column_id as no, 
    fk_col.name as fk_column_name,
    ' = ' as [join],
    pk_col.name as pk_column_name,
    fk.name as fk_constraint_name
from sys.foreign_keys fk
    inner join sys.tables fk_tab
        on fk_tab.object_id = fk.parent_object_id
    inner join sys.tables pk_tab
        on pk_tab.object_id = fk.referenced_object_id
    inner join sys.foreign_key_columns fk_cols
        on fk_cols.constraint_object_id = fk.object_id
    inner join sys.columns fk_col
        on fk_col.column_id = fk_cols.parent_column_id
        and fk_col.object_id = fk_tab.object_id
    inner join sys.columns pk_col
        on pk_col.column_id = fk_cols.referenced_column_id
        and pk_col.object_id = pk_tab.object_id
order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name, 
    fk_cols.constraint_column_id

If we switch to the model layouts view and check the relationships between the tables, we can see that they exist in the default semantic model. Now, if we try and create a relationship in the model here between the same columns we’ll get a message stating “relationships already exist”, which is a good thing!

We can delete relationships in the Warehouse using T-SQL.

--drop foreign key constraints
ALTER TABLE dbo.FactTrip DROP CONSTRAINT FK_DimDate;

ALTER TABLE dbo.FactTrip DROP CONSTRAINT FK_DimGeography_Pickup;

Here are the results of modifying the relationships:

If we delete the FKs using T-SQL in the Warehouse, the relationships still exist in the default semantic model. You will need to delete in the default semantic model as well to remove all relationships.
Note that if the relationships are re-added via T-SQL but have not been removed from the default semantic model, it does not affect the default semantic model and the relationships will still exist.
If we delete the relationships in the default semantic model instead of using T-SQL in the Warehouse, then the underlying relationships in the Warehouse will also be deleted.
If we create the relationships in the default semantic model, they are also created in the underlying Warehouse, but are given auto-generated names.

Issues

If there are no relationships in the Warehouse and relationships are created in the default semantic model, then created using T-SQL in the Warehouse between the same tables, you’ll get duplications and an error.

We also now see those duplicates in the DSM. This does not seem to affect the operation of the model in terms of building reports, but it doesn’t look very good! We can delete the relationships here, but we have no idea which relationship has been created by T-SQL or in the model view.

Just a note that this does seem to sort itself out if you do a model change. It actually deletes the model version of the relationships so if you then query the Warehouse constraints, you’ll see those have been deleted and the T-SQL created relationships will exist.

Creating Relationships in the Custom Semantic Model

As I said towards the beginning of this blog post, I would advocate using a default semantic model for very basic data exploration. As soon as any actual modelling needs to take place, create a custom semantic model. The custom semantic model is a separate item and does not inherit any existing relationships creating in the underlying Fabric Warehouse. It doesn’t matter if a custom semantic model is created either before or after relationships have been created in the Warehouse, these relationships will not be present in the model.

If you create relationships in the custom semantic model, these stay with the model itself and do not get created in the underlying Warehouse itself, like the default semantic model.

Using Power BI Desktop

When using Power BI Desktop with the Fabric Warehouse, we have 2 ways of connecting to the Warehouse:

Connect (to default semantic model)
- We can see the relationships if they have been created in the Warehouse or the default semantic model
Connect to SQL Endpoint
- We can select tables and see existing relationships creating using T-SQL in the Warehouse – both Import and Direct Query

To inherit the relationships from the Warehouse, Power BI has options to import any pre-existing relationships it finds in the underlying data source (this isn’t a Fabric Warehouse specific option). If we were to unselect these options, then the pre-existing relationships in the Warehouse would not be imported.

We can work with these inherited relationships in Power BI Desktop without any changes being pushed back to the Warehouse as in the default semantic model, they exist now as model attributes and can be managed as such.

References

Tags: Semantic Model, Warehouse