Defining entity relationships in DSV

While creating a data source view, you noticed that SSDT automatically detected primary and foreign keys between DimDate and fact tables and established their respective relationships. If our data source does not have referential integrity constraints, we would have to define the logical primary keys and necessary relationships directly within the DSV.

To set up relationships between two objects within a DSV, dimension objects (tables or views) must have a primary key column and fact tables must reference this column. The column names in fact and dimension tables do not need to match, but they must have compatible data types. For example, you cannot create a relationship between an integer and string columns.

How to do it...

Let's get started; the following are the steps to define the entity relation in DSV:

1. To define a logical primary key on an object that does not already have one, right-click on the column that uniquely identifies each record in...