Relationship properties

We use Data model, in which we add and connect several tables, to create reporting dataset. In one of previous recipes we were talking about creating links between tables by dragging & dropping keys. The subject of this post will be manual modifying, changing properties or removing data connection. Also you’ll be able to see properties of tables imported from other data sources, such as MS SQL, MS Access etc.

Data model is open by clicking Manage on PowerPivot ribbon, and you’ll be able to see all its tables. In the bottom on the window all tables are represented by tabs, similar to Excel spreadsheets, and each tab holds a name of the table. In order to see connections between tables within a Model it is necessary to choose Design ribbon, and then Manage Relationships. A dialog box will appear and its central part is a list active and inactive connections and all their features.

By clicking Create button we’ll make a new relationship. A dialog box will appear in which, on the left side, it’s necessary to choose table and a column. We should repeat this process with a table and a column on the right side. By clicking OK we create a relationship between two tables, that are linked by columns i.e. keys. When we create a relationship it’s important to know that columns used for linking tables should be the same data type. This procedure could also be started by using Create Relationship option within the Design ribbon.

In addition to creating, Manage Relationships dialog box has two more options. Option Edit Relationship is used to modify the properties of a previously created links, and Delete can be used to remove the selected relationships.

After we have connected to tables from external data source we can start option Table Properties, within a Design ribbon. It will open a dialog boy in which we can see name of database and a table or a SQL query by which data has been imported to the Model.