In previous recipes, the data relationships were auto-generated by PowerPivot. This relationship in PowerPivot is similar to how a database manages data from various tables using keys. We will practice building relationships to reproduce our data and incorporate different types of data together.
As the tables contain different types of data, PowerPivot may not comprehend that one data is related to another, which may be obvious to us. We will gain exposure to managing data relationships in this recipe.
The following are the steps to manage data relationships by creating a copy of the DatabaseData_v3.xlsx file, which results in the creation of the Relationships sheet:
Make a copy of the
DatabaseData_v3.xlsxfile and save asDatabaseData_v3_test.xlsx. Copy the sheetPivotTableand rename the sheet asRelationships.Firstly, click on the PowerPivot Window tab and then under the Design tab, click on Manage Relationships and remove all relationships. Return to Excel, refresh, and confirm that the results are all out of place as they are not related to one another anymore. Excel will have a Create button that says "Relationships may be needed" in the PowerPivot Field List dashboard. However, instead of having it automatically generate data, we will manually reproduce it.
First, start by studying where the data are. Number of clients is from the BusinessEntityAddress | BusinessEntityID column, Country Name is from the CountryRegion | Name column, State Name is from StateProvince | Name, and City Name is from Address | City. Then, we can observe that there are common data between the tables, which is how they are linked. The same key act as a link in saying that these data are related.
Observe the following connections:
In the BusinessEntityAddress table, there is a single AddressID value for each BusinessEntityID
In the Address table, there is a single City value and a single StateProvinceID value for each AddressID
In the StateProvince table, there is a single Name value and a single CountryRegionCode value for each StateProvinceID
In the CountryRegion table, there is a single Name value for each CountryRegionCode
As evidenced, one data links to another table, whose value links to another table.
Then, create the relationships in the mentioned order. For instance, the first relationship created would be as follows:
After creating the third relationship, return to Excel, refresh, and check the results. Congratulations! We have managed to link all the relevant data together to reproduce our previous settings.
Now, create two more relationships:
Link BusinessEntityID between the tables BusinessEntityAddress and PersonPhone
Link PhoneNumberTypeID between the tables PersonPhone and PhoneNumberType
We have successfully linked the phone number of each client, and then linked the phone type of each phone number. Return to Excel, refresh, and add PhoneNumberType | Name into Slicers Horizontal. The result would appear as shown in the following screenshot:
We can now sort the clients by their phone number types, and can reach conclusions such as that no one from Australia, France, and Germany has provided their work phone numbers, and that some of the phone numbers are not categorized under Home, Work, or Cell.
If we were to produce the same pivot table in the original
DatabaseData_v3.xlsxfile, the phone number types will not show in the pivot table, and PowerPivot will not be able to detect it automatically because its relationship has been set up in another manner.
As mentioned, the Key acts as a bridge between various tables, enabling PowerPivot to link the data together. In most cases, the auto-detection feature of PowerPivot is sufficient as it auto-detects and utilizes relationships between tables to compile various data together. In this recipe, we have studied the tables independently, analyzed the links between them, and manually reproduced the links between the tables to generate the same pivot table as before by connecting various tables together.
There are some tables that cannot be linked despite having links. An example would be using BusinessEntityID as a link between BusinessEntityAddress and BusinessEntityContact. As these tables cannot be linked, the tables linked with BusinessEntityAddress (that is, Address, Person, EmailAddress, and PhoneNumber) cannot be used in conjunction with the tables linked with BusinessEntityContact (that is, ContactType).
As an alternative to building relationships between various tables, we can just add all the relevant items into one table. This is normally called View in a database, where the aggregated data from many tables are shown as a single table. This will be useful for the previously mentioned case, by merging the tables BusinessEntityAddress and BusinessEntityContact together. We will discuss this method in the following Adding new custom columns (Intermediate) recipe, where we learn how to modify PowerPivot data tables using formulas.