Work with Relationships in PivotTables
PivotTables have traditionally been constructed using OLAP cubes and other complex data sources that already have rich connections between tables. However, in a PowerPivot workbook, you are free to add data to existing data sources, or build your own connections between tables. While this flexibility is powerful, it also makes it easy to bring together data that is not related, leading to strange results.
This section explains how to work with relationships in the context of a PivotTable and connect data so that you can get the reports you want.
Unrelated Data in PivotTables: Problem and Solution
Have you ever created a PivotTable like this? You intended to create a breakdown of purchases by region, and so you dropped a purchase amount field into the Values area, and dropped a sales region field into the Column Labels area. But the results are wrong.
.gif "bad pivot table")
How can you fix this?
The problem is that the fields you have added to the PivotTable might be in the same workbook, but the tables that contain each column are not related. For example, you might have a table that lists each sales region, and another table that lists purchases for all regions. To create the PivotTable and get the correct results, you need to create a relationship between the two tables.
After you create the relationship, the PivotTable combines the data from the purchases table with the list of regions correctly, and the results look like this:
.gif "good pivot table")
PowerPivot for Excel contains technology developed by Microsoft Research (MSR) for automatically detecting and fixing relationship problems like this one.
Using Automatic Detection
Automatic detection checks new fields that you add to a workbook that contains a PivotTable. If the new field is unrelated to the column and row headers of the PivotTable, a messageappears in the notification area at the top of the PivotTable letting you know that a relationship may be needed. PowerPivot will also analyze the new data to find potential relationships.
You can continue to ignore the message and work with the PivotTable; however, if you click Create, the algorithm goes to work and analyzes your data. Depending on the values in the new data and the size and complexity of the PivotTable, and the relationships that you have already created, this process can take up to several minutes.
The process consists of two phases:
Detection of relationships. You can review the list of suggested relationships when analysis is complete. If you do not cancel, PowerPivot will automatically proceed to the next step of creating the relationships.
Creation of relationships. After the relationships have been applied, a confirmation dialog appears, and you can click the Details link to see a list of the relationships that have been created.
You can cancel the detection process, but you cannot cancel the creation process.
The MSR algorithm searches for the “best possible” set of relationships to connect the tables in your model. The algorithm detects all possible relationships for the new data, taking into consideration column names, the data types of columns, the values within columns, and the columns that are in PivotTables.
PowerPivot then chooses the relationship with the highest ‘quality’ score, as determined by internal heuristics. For more information, see Understanding Relationships and Troubleshoot Relationships.
Manual Editing of Relationships
If automatic detection does not give you the correct results, you can edit relationships, delete them, or create new ones manually. For more information, see Create a Relationship Between Two Tables.
Note
Note that, if you are building a PivotTable and decide you need to add relationships or new data, you must first return to the PowerPivot window. After you have added relationships or changed the data you need, you can switch back to the Excel workbook. However, if any of the changes affect the PivotTable, the workbook will need to be recalculated.
Blank Rows in Pivot Tables (Unknown Member)
Because a PivotTable brings together related data tables, if any table contains data that cannot be related by a key or by a matching value, that data must be handled somehow. In multidimensional databases, the way to handle mismatched data is by assigning all the rows that have no matching value to the Unknown member. In a PivotTable, the unknown member shows up as a blank heading.
For example, if you create a Pivot Table that is supposed to group sales by store, but some records in the sales table do not have a store name listed, all records without a valid store name are grouped together.
For more information, including examples of formulas that count or exclude mismatched values, see Referential Integrity, PowerPivot Relationships, and the Unknown Member.