Create Relationships Between Tables (Tutorial)

Article
04/16/2014

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

In this lesson you will use PowerPivot to view and create relationships between data from different data sources. A relationship is a connection between two tables of data that establishes how the data in the two tables should be correlated. For example, the DimProduct table and the DimProductSubcategory table have a relationship based on the fact that each product belongs to a subcategory. For more information about relationships, see Relationships Overview.

Relationships in PowerPivot are created either by manually joining tables in the PowerPivot window or columns in Diagram View, or automatically if PowerPivot for Excel detects existing relationships when importing data into a PowerPivot workbook. A relationship is created manually by joining columns from different tables that contain similar or identical data. For example, the DimProduct and DimProductSubcategory tables are related by the ProductSubcategoryKey columns that occur in both tables. The columns do not have to have the same name, but they often do.

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For information about prerequisites and the first step in the tutorial, see PowerPivot for Excel Tutorial Introduction.

Why Create Relationships?

In order to perform any meaningful analysis, your data sources must have relationships between them. More specifically, relationships enable you to:

Filter data in one table by columns of data from related tables.
Integrate columns from multiple tables into a PivotTable or PivotChart.
Easily look up values in related tables using Data Analysis Expressions (DAX) formulas.

Review Existing Relationships

You already have data from three different sources in your PowerPivot workbook:

Sales and product data imported from an Access database. Existing relationships were automatically imported for you together with the data.
Product category data imported from an Access database.
Data copied from, and linked to, an Excel spreadsheet that contains store information.

To review existing relationships

In the PowerPivot window, on the Design tab, in the Relationships group, click Manage Relationships.

In the Manage Relationships dialog box, you should see the following relationships, which were created when the first Access database was imported:

Table	Related Lookup Table
DimProduct [ProductSubcategoryKey]	DimProductSubcategory [ProductSubcategoryKey]
FactSales [channelKey]	DimChannel [ChannelKey]
FactSales [DateKey]	DimDate [Datekey]
FactSales [ProductKey]	DimProduct [ProductKey]

Notice that you can create, edit, and delete relationships from this dialog box. Click Close.

Create New Relationships between Data from Separate Sources

Now that you have reviewed the relationships that were created automatically, you will create additional relationships.

To create your first relationship

Click the Stores table tab.
Right-click the GeographyKey column header, and then click Create Relationship.

The Table box and the Column box are automatically populated.
In the Related Lookup Table box, select Geography (the table that you pasted in from an Excel worksheet and then renamed).
In the Related Lookup Column box, make sure GeographyKey is selected.
Click Create.
When the relationship is created, an icon displays at the top of the column. Point to the cell to display the relationship details.

To create more relationships between the data from Access and Excel

Click the Stores tab.
Select the StoreKey column.
On the Design tab, click Create Relationship.

The Table box and the Column box are automatically populated.
In the Related Lookup Table box, select FactSales.
In the Related Lookup Column box, make sure StoreKey is selected.

Notice the information icon next to the Related Lookup Column box. This tells you that this relationship is being created in the wrong order. When you create a relationship, you must select a column with unique values for the Related Lookup Column.
Reverse the order. Select FactSales from the Table box, and then select StoreKey from the Column box. Select Stores as the Related Lookup Table, and then select StoreKey as the Related Lookup Column.
Click Create.

Create Relationships in Diagram View

In Diagram View, you can easily create relationships between columns in separate tables. The relationships appear visually, which enables you to quickly see how all the tables relate to each other. In this step, you will create the last relationship that you will need to complete this tutorial using Diagram view. For more information about Diagram View, see PowerPivot Window: Diagram View.

To navigate Diagram View

In the PowerPivot window, on the Home tab, in the View area, click Diagram View. The Data View spreadsheet layout changes to a visual diagram layout, and the tables are automatically organized, based on their relationships.
To see all the tables on the screen, click the Fit to Screen icon in the top-right corner of Diagram View.
To organize a comfortable view, use the Drag to Zoom control, the Minimap, and drag the tables into the layout that you prefer. You can also use the scroll bars and your mouse wheel to scroll the screen.
Point to a relationship line (black line with an arrow and circle on the ends) to highlight the related tables.

To create a relationship between tables in Diagram View

While still in Diagram View, right-click the DimProductSubcategory table diagram, and then click Create Relationship. The Create Relationship dialog box opens.
In the Column box, select ProductCategoryKey, in the Related Lookup Table box, select ProductCategory, and in the Related Lookup Column box, select ProductCategoryKey.
Click Create.
Check that all relationships have been successfully created by clicking Manage Relationships from the Design tab and reviewing the list.

Next Step

To continue this tutorial, go to the next topic: Create a Calculated Column (Tutorial).