In this step by step PowerPivot Tutorial I will show you how to using PowerPivot create relationships. For the purpose of this tutorial I will use PowerPivot in Excel 2013.
Other Versions of Excel:
- PowePivot Excel 2013 (v3)
- This blog post has been written using PowerPivt in Excel 2013
- PowerPivot Excel 2010 (v2) - SQL Server 2012
- The examples we used should work in Excel 2010 with PowerPivot SQL Server 2012 (v2)
- PowerPivot Excel 2010 (v1) - SQL Server 2008 R2
- There are two ways now to create relationships and v1 has only one which is manage relationship dialog box and does not have diagram view which allows to create relationships as well.
Who is it for?
- People who would like to learn basics of creating PowerPivot relationships
Prior knowledge. You should have PowerPivot with imported data. You should also have basic understanding of relationships between tables and idea of what is a Primary Key and Foreign Key.
- What is a database - This is introduction to databases and should give you basic understanding of databases, relationships, primary keys and foreign keys.
PowerPivot one to many relationship
One to Many relationship is one of the most common relationships and it means that you have a parent table that contains unique identifiers for each rows and child table that uses parent table values to relate to tables. So one parent key may have many children has one to many relationship.
There are two ways of creating PowerPivot relationships in Excel 2013 however in Excel 2010 PowerPivot version 1 there is only one method which I will use first.
NOTE: Excel 2010 has two version of PowerPivot v1 and v2. If you use v1 I strongly recommend to upgrade to v2 as there some important improvements that remove certain limitations and make the tool more valuable.
PowerPivot Manage Relationships
The first method I will describe is creating new relationship using manage relationship window.
In my example I will create relationship between visits and Country. My Parent table is Country (one unique country per row) and my Child table is Visits (one country has many visitors).
In PowerPivot you will have Table (Child table) and Related Lookup Table (Parent Table). Now that we covered the basics let's create the relationship.
In PowerPivot window I go to Design tab and click Manage Relationships button.
Manage Relationship window appears and I click Create button
Create Relationship window appears and I choose Visits as Table (Child Table) in Column I select CountryID (foreign key) and in Related Lookup Table I select Country table (Parent Table) and in Related Lookup Column I select ID (Primary Key, in data warehouse also known as Surrogate Key due to special function).
And I Click Create button
You can see that new relationship has been created
- PowerPivot Relationship Direction
- In my case I used Visits (child table) as table and Country (parent table) as related lookup table. Note that this direction is required and if you go the other way than PowerPovit in Excel 2013 will reverse it (I'm not sure about previous version behaviour
- PowerPivot Multiple Columns Relationships
- Note that PowerPivot does not support multiple columns relationships so you can use only single field on both sides of tables to create relationship. In order to overcome this limication you can creaete a calculated field that combined two (or more) fields together and then use the combined fields to create a relationship.
PowerPivot Create Relationships in Diagram View
Second method I will show is to create relationships in Diagram View.
In PowerPivot window in Home Tab I click Diagram View
NOTE: If you don't see Diagram View then check this article PowerPivot Diagram View Missing
You can see that one of the relationships between Visits and Country already exists which is the one we created previously and now I will create Relationship between Visits PageID field and Page table ID field.
To do that I click PageID and drag and drop it into Page table ID field. Relationship direction in this case doesn't matter so doing it the other way will also work in Excel 2013.
I added two remaining relationships and the final diagram view is below
Star Schema, Snowflake or flat table?
My diagram presents Star Schema which is used in Data Warehouses and I would recommend it. Design is important so if you want to build long term solution ensure you make the right choices. Two other designs are Snowflake which may cause some confusion when users use the model in Excel. Flat table is design where everything is put into one big table but this may be limited in use as certain fields (dimension attributes) may not be re-used for different tables so getting useful insight might be harder. I recommend to look into Star Schema Design for long term solutions.
I hope this tutorial will help you to create relationships and give direction for design choices.