Create Relationships between Tables (Tutorial)
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 automatically if PowerPivot for Excel detects existing relationships when importing data into a PowerPivot workbook. A relationship is created between two tables by joining columns 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]
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 tab.
Right-click the GeographyKey column header and select Create Relationship.
The Table field and the Column field are auto-populated.
In the Related Lookup Table field, select Geography.
In the Related Lookup Column field, select GeographyKey.
Click Create.
When the relationship is created, an icon displays at the top of the column. Pause the pointer over the icon 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 field and the Column field are auto-populated.
In the Related Lookup Table field, select FactSales.
In the Related Lookup Column field, select StoreKey.
Notice the icon next to the Related Lookup Column field. 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. Move FactSales to the Table field and select StoreKey as the Column. Select Stores as the Related Lookup Table and select StoreKey as the Column.
Click Create.
Repeat the steps for DimProductSubcategory (as the table) and ProductCategory (as the lookup table), using the ProductCategoryKey column in both tables.
Check that all relationships have been successfully created by clicking Manage Relationships and reviewing the list.