Microsoft PowerPivot for Excel 2010 – Creating Relationships Among Data

Now we have imported data into PowerPivot, we can create relationships between tables.  A relationship establishes how the data from different sources should be correlated. You can create relationships between data sources by joining columns that contain same data.

You can manually create relationships or let PowerPivot to automatically detects existing relationships when importing data into a PowerPivot workbook.

This demonstration continues from the previous blog; Microsoft PowerPivot for Excel 2010 – Importing Data.  So if you haven’t followed the last blog, you should go back and read it now.  You can downloaded sample data from Codeplex Web site.

Why Create Relationships?

We have used VLookup to link data from different tables together so that we can do our in depth analysis.  You can consider relationship is another form of VLookUp.

Relationships allow you to:

  • Filter data in one table by selecting data from related tables.
  • Integrate data from multiple tables into a PivotTable or PivotChart.
  • Easily look up values in related tables using Data Analysis Expressions (DAX) formulas.

Existing Data

You already have data from three different sources in your PowerPivot workbook:

  • Sales and product data.
  • Product category data.
  • Store information. 

Create New Relationships between the Stores and Entities

  • Click the Stores tab.
  • Right-click the EntityId column header and select Create Relationship.

image

  • The Table field and the Column field are auto-populated in he Create Relationship dialogue.

image

  • In the Related Lookup Table field, select dbo_DimEntity.
  • In the Related Lookup Column field, select EntityKey.

image

  • Click Create.
  • You would find an icon displays at the top of the column which indicates the existence of a relationship.

image

Create New Relationships between the Stores and Sales

  • Click the dbo_FactSales tab.
  • Select the StoreId column.
  • On the Design tab, click Create Relationship.
  • The Table field and the Column field are auto-populated.
  • In the Related Lookup Table field, select Stores.
  • In the Related Lookup Column field, select StoreKey.
  • Click Create.

Create New Relationships between the Products and Sales

  • Click the dbo_FactSales tab.
  • Right-click the ProductKey column header and select Create Relationship.
  • In the Related Lookup Table drop down, select dbo_DimProduct.
  • In the Related Lookup Column drop down, select ProductKey.
  • Click Create.

Repeat the steps for the following relationships.

Foreign Table Foreign Column Primary Table Primary Key
dbo_FactSales DateKey dbo_DimDate Datekey
dbo_FactSales ChannelKey DimChannel ChannelKey
dbo_DimProduct ProductSubcategoryKey Dbo_DimProductSubcategory ProductSubcategoryKey
dbo_DimProductSubcategory ProductCategoryKey ProductCategory ProductCategoryKey
  • Click Manage Relationships on the Design tab.

image

  • You can review all the relationships that have been created.

image

As long as you understand the data and the relationships among them, you don’t need an IT administrator to build the relationships.  PowerPivot is a DIY tool!

 

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

9 Responses to Microsoft PowerPivot for Excel 2010 – Creating Relationships Among Data

  1. Pingback: Microsoft PowerPivot for Excel 2010 – Importing Data | Technologies and your business

  2. Pingback: Why Microsoft PowerPivot? | Technologies and your business

  3. Pingback: Microsoft PowerPivot for Excel 2010 – Introduction | Technologies and your business

  4. Pingback: Excel 2010 – PowerPivot | Technologies and your business

  5. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Slicer | Technologies and your business

  6. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Pivot Chart | Technologies and your business

  7. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Pivot Table | Technologies and your business

  8. Pingback: Microsoft PowerPivot for Excel 2010 – Data Analysis Expressions (DAX) | Technologies and your business

  9. this is indeed very useful and neat post.. helped me to start with powerpivot things..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: