Define relationships in data models for data warehousing in Microsoft Fabric
Applies to: ✅ SQL analytics endpoint, Warehouse, and Mirrored Database in Microsoft Fabric
A well-defined data model is instrumental in driving your analytics and reporting workloads. In a Warehouse in Microsoft Fabric, you can easily build and change your data model with a few simple steps in our visual editor. You need to have at least a small sample of data loaded before you can explore these concepts further; tables can be empty, but the schemas (their structures) need to be defined.
Warehouse modeling
Modeling the warehouse is possible by setting primary and foreign key constraints and setting identity columns on the model layouts within the data warehouse user interface. After you navigate the model layouts, you can do this in a visual entity relationship diagram that allows a user to drag and drop tables to infer how the objects relate to one another. Lines visually connecting the entities infer the type of physical relationships that exist.
How to model data and define relationships
To model your data:
- Open a warehouse in your Fabric workspace.
- Navigate to Model layouts in the ribbon.
In the model layouts, users can model their warehouse and the canonical autogenerated default Power BI semantic model. We recommend modeling your data warehouse using traditional Kimball methodologies, using a star schema, wherever possible. There are two types of modeling possible:
- Warehouse modeling - the physical relationships expressed as primary and foreign keys and constraints
- Default Power BI semantic model modeling - the logical relationships expressed between entities
Modeling automatically keeps these definitions in sync, enabling powerful warehouse and semantic layer development simultaneously.
Define physical and logical relationships
To create a logical relationship between entities in a warehouse and the resulting primary and foreign key constraints, select the Model layouts and select your warehouse, then drag the column from one table to the column on the other table to initiate the relationship. In the window that appears, configure the relationship properties.
Select the Confirm button when your relationship is complete to save the relationship information. The relationship set will effectively:
- Set the physical relationships - primary and foreign key constraints in the database
- Set the logical relationships - primary and foreign key constraints in the default Power BI semantic model
Edit relationships using different methods
Using drag and drop and the associated Edit relationships dialog is a more guided experience for editing relationships in Power BI.
In contrast, editing relationships in the Properties pane is a streamlined approach to editing relationships:
You only see the table names and columns from which you can choose, you aren't presented with a data preview, and the relationship choices you make are only validated when you select Apply changes. Using the Properties pane and its streamlined approach reduces the number of queries generated when editing a relationship, which can be important for big data scenarios, especially when using DirectQuery connections. Relationships created using the Properties pane can also use multi-select relationships in the Model view diagram layouts. Pressing the Ctrl key and select more than one line to select multiple relationships. Common properties can be edited in the Properties pane and Apply changes processes the changes in one transaction.
Single or multi-selected relationships can also be deleted by pressing Delete on your keyboard. You can't undo the delete action, so a dialog prompts you to confirm deleting the relationships.
Use model layouts
During the session, users can create multiple tabs in the model layouts to depict multiple data warehouse schemas or further assist with database design.
Currently, the model layouts are only persisted in session. However the database changes are persisted. Users can use the auto-layout whenever a new tab is created to visually inspect the database design and understand the modeling.