Book Image

Instant Creating Data Models with PowerPivot How-to

By : Taehyung Lee
Book Image

Instant Creating Data Models with PowerPivot How-to

By: Taehyung Lee

Overview of this book

Microsoft PowerPivot is a free software designed to enhance Microsoft Excel. It allows the user to make extensive use of his/her computer's powers to draw data from various sources, do analysis across millions of rows of data, and present results in a very unique format. Instant Creating Data Models with PowerPivot How-to is a concise and to-the-point guide that helps you get a jump start on using this strong business intelligence tool, while still working in an environment similar to Excel. You will begin with data generation and manipulation, learning a new feature at each step by building onto the old file, and ultimately creating a comprehensive file. Instant Creating Data Models with PowerPivot How-to will guide the user through database installation, importing data from various sources, creating pivot charts and tables, utilizing a unique feature of PowerPivot called slicers,adding custom columns, and setting custom relationships between data to generate the ultimate customized dataset for analysis. By the end of the book and all the sections of Microsoft PowerPivot for Excel, the reader will be fully experienced and ready to utilize this powerful software.
Table of Contents (7 chapters)

Managing data relationships (Simple)

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.

How to do it...

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:

  1. Make a copy of the DatabaseData_v3.xlsx file and save as DatabaseData_v3_test.xlsx. Copy the sheet PivotTable and rename the sheet as Relationships.

  2. 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.

  3. 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.

  4. 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

  5. As evidenced, one data links to another table, whose value links to another table.

  6. Then, create the relationships in the mentioned order. For instance, the first relationship created would be as follows:

  7. 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.

  8. Now, create two more relationships:

    • Link BusinessEntityID between the tables BusinessEntityAddress and PersonPhone

    • Link PhoneNumberTypeID between the tables PersonPhone and PhoneNumberType

  9. 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:

  10. 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.

  11. If we were to produce the same pivot table in the original DatabaseData_v3.xlsx file, 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.

How it works...

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's more...

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).

Creating custom tables instead of connecting relationships

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.