Table Relationships

One of PowerPivot for Excel’s current limitations is that there is no direct means of creating relationships between tables based on multiple columns. You can use DAX expressions as a way of crafting a workaround.

Understanding the Problem

One of the limitations in the initial release of PowerPivot for Excel concerns relationships between tables. At this time, PowerPivot for Excel only supports one-to-one and one-to-many relationships between tables.

A one-to-one relationship exists where a given row in one table is related to at most one row in a lookup table. Figure 4-9 illustrates a one-to-one relationship between a table of employees and a table of spouses. Each employee row would have at most one related row in the spouse table.

Figure 4-9. One-to-one relationship

Usually, these one-to-one relationships become columns in the original table. Given the aggregating nature of PowerPivot, it is possible to have a one-to-one relationship exist between two different sources of data, and combining the attributes into a single table may not be practical. However, PowerPivot for Excel supports one to one relationships both in table definitions, through inclusion of the attributes into a single table and in the creation of PowerPivot Table relationships.

A one-to-many relationship exists where a row in the lookup table may be related to many rows in the Primary Table. The context from which the lookup is occurring is vitally important. PowerPivot for Excel supports one-to-many relationships between tables. For example, consider a primary table of product-level retail sales records and a lookup table containing the entire catalog of products sold by the company. Each row in the sales table would be related to exactly one product row. However, as most products have many sales transactions associated with them, a product would be related to many sales rows. This is illustrated in Figure 4-10. Several rows in the Sales Transaction Table contain a reference to a single row of the Product Table, for Product ID = 100, as indicated by the arrows.

Figure 4-10. One to Many Relationship of Sales and Product

Another common example of the one-to-many relationships can occur in databases that store address information. Given a table of customers with addresses including a two-character state abbreviation and a lookup table containing the same abbreviation and the long state name, it is possible to construct a similar one-to-many relationship. Each customer address would be related to exactly one state, yet each state may have many customers.

A many-to-many relationship exists where a row in the primary table is related to many rows in the lookup table. Additionally, the lookup table is related to many rows in the primary table. An example to consider for this relationship type is in banking, specifically the relationships between customers and accounts. An account may have many owners, via marriage or business partnership. Additionally, a customer may have many accounts. This release of PowerPivot for Excel cannot directly support many-to-many relationships. However, your organization may already have databases, both operational and analytical data stores, that directly support and account for this relationship type.

Illustrating Relationships with Football

Yes, you read that correctly. I had the good fortune to spend my youth in the southeastern United States. For a young man who enjoys sports, that means there are only two seasons, college football and college football recruiting. This example is intended to convey, with real data, the use of a DAX calculated column to relate PowerPivot tables and uses data readily available from the National Collegiate Athletics Association (NCAA) web site.

The NCAA provides updated statistics (at http://web1.ncaa.org/mfb/download.jsp) for players, including the offensive and defensive game-level statistics for each player’s performance. The relevant files, as of the end of the 2010 regular season games, are available from this book’s example download as NCAA.xlsx. This example is intended to illustrate how DAX is used to create columns to relate the Roster and Offense tables. As you will see in Figure 4-11, there is a Player ID column in the Roster table. This would seem to be an ideal column to relate the offensive statistics to a player in the Roster table. However, the Player ID column does not exist in the Offense table shown in Figure 4-12. As will often be the case with related data, a number of columns must be used to relate a row in the Offense table to a row in the Player table. The multiple columns would not be an issue except, PowerPivot for Excel, in the initial release does not support multiple column keys for relationships.

Figure 4-11. Roster table

Figure 4-12. Offense table

Relating Tables with DAX

The solution to this relationship issue is easily accomplished with a little DAX. Because both tables contain Institution ID, Uniform Number, and Last Name columns, we can manufacture a key column to relate Offense to Roster.

First, from the PowerPivot window, move to the empty row immediately below the Add Column column header in the Roster table. Enter the following in the formula entry area directly below the Office Ribbon:

When you’re finished, your formula edit area should look similar to Figure 4-13.

Figure 4-13. Roster PlayerID DAX

What we have used the concatenation operator (the ampersand) and literals (the dashes inside quotation marks) to build a single-column unique key from the three columns required to uniquely identify a row in the Roster table. It is important to delimit the data values within the new compound key column to ensure an unintended relationship is not formed. For example, what would happen if the data contained an institution ID of 100 and a uniform number of 2 for a last name of Smith and another row with an institution ID of 10 and player ID of 02 (these are text data types, not integers) and a last name of Smith? The two keys would be the same. This example is illustrated in Table 4-1. As a matter of housekeeping, we prefer to rename calculated columns from the PowerPivot generated name to a meaningful name. In this case, we will use PlayerKey.

To relate the Roster and Offense tables, we will duplicate the calculation of the player key, using the column names in the Offense table. It is unnecessary to name the columns that comprise the key identically. It is necessary for the player key DAX formula to employ the same column order. To create the same key value calculation in the Offense table, do the following:

1. In the PowerPivot window, move to the column of the Offense table named Add Column. In the formula editor, enter the same formula used in the Roster table, using the corresponding columns in the Offense table. Recall the original Player Key formula was =[Institution ID]&”-“&[Uniform Number]&”-“&[Last Name].
2. Rename the new column in the Offense table to PlayerKey by right-clicking the column heading and choosing the Rename Column option.
3. The final step in this solution is to create a relationship from the Offense table to the Roster table. The important concept to keep in mind here is the uniqueness of the two tables. Although PowerPivot for Excel supports one-to-many relationships, it is critical to build the relationship correctly. That is from the table with many rows, to the table containing a single, distinct row for each PlayerKey (the one side of the relationship). In this example, the Offense table contains many rows for each PlayerKey, because it is a summary, by game date, of the offensive performance of a PlayerKey. Conversely, there is one row for each distinct player in the Roster table. A PlayerKey uniquely identifies a single row in the Roster table. Figure 4-14 illustrates the correct formation of the Offense to Roster tables’ relationship.

Figure 4-14. Offense-to-Roster relationship

Note that if both tables for which a relationship is being created contain multiple records for each key, PowerPivot for Excel will not allow the relationship to be created. Instead, PowerPivot for Excel will respond with an error dialog similar to the one shown in Figure 4-15.

Figure 4-15. Duplicate key values