P1.2: Relationships and Data Models in PowerPivot- Excel 2013

This post describes how to establish relationships between tables, and create a data model in Excel 2013 using the PivotTable workbook in Chapter 1 of the Microsoft Excel 2013 book.

VLOOKUP and Relationships

From the previous post, you created a PivotTable displaying the Sales of Product Categories by Sales Manager, by Year.

Fig. 1: PivotTable of Sales of Product, by Sales Manager by Year

The SalesManager field was added to the Sales sheet via VLOOKUP from the SalesManagers sheet. However, you might have many additional fields (such Office) from the same sheet (of SalesManagers) or others you might like to add to the source sheet (Sales), but doing so with VLOOKUP would be inefficient. Using a relationship to link the target sheet (SalesManagers) to the source sheet (Sales) is the optimal way to enable analysis of multiple sheets of data in a PivotTable.

Creating a Relationship

To create a relationship, you click on the Data tab in Excel and then on the Relationships button as depicted in Figure 2.

Fig. 2: Data tab with Relationships button highlighted

For a new Relationship connecting the SalesManagers sheet to the Sales sheet, you select New.. on the right of the Relationship Manager, to create a new Relationship. Next you select the tables and the field name within the sheets you want to link as shown in Figure 3.

Fig. 3: Creating the Relationship

The table with the information is in the Sales table, and the column the tables have similar is ProductCategory. Now the related table you want to link is in the SalesManager table which has the related column of Category, which corresponds to the elements in ProductCategory in the Sales table. By clicking OK, you have created a relationship between the Sales and SalesManagers tables with the elements in ProductCategory/Category fields, respectively.

Fig. 4: Relationship Manager with new Relationship

The Data Model

A set of tables is a data model when the tables have a relationship linking them together. Otherwise, they are just tables.

Now that two tables have an established relationship, together, the two tables Sales and SalesManagers are considered a data model. To analyze the SalesManagers and Sales tables in a single PivotTable, go to the PivotTable you created, which only has one table's fields to choose from, and in the PivotTable Fields, scroll down and click on More Tables (Figure 5).

Fig. 5: Data Model via More Tables

The dialogue box that appears alludes to the properties of a data model, notifying you that if you want to analyze multiple tables, Excel will create a new PivotTable from the data model. You have two tables connected by a relationship, which forms the data model.

Fig. 6: Creating a New PivotTable Dialogue Box

After you click Yes, the new PivotTable, with two tables in the PivotTable Fields, is created from the data model. When you add the Office field from the SalesManagers table in the rows of the PivotTable , they display properly: in accordance to the office selling the specific product, even though the Office field was not added by VLOOKUP to the Sales table. They are linked via the relationship existing between the two tables by the Product Category.

Fig. 7: Sales of Sales Manager by Office by Year

In Figure 7, the Sales and SalesManagers tables can be seen in the Fields. The two tables can be expanded to reveal the fields, like the SalesManager table, or collasped to hide the fields, shown by the Sales table. You can select the fields from either table and drag it down to be analyzed in the PivotTable.


Thanks for reading,
Wayne