PowerPivot Relationships Explained

The main PowerPivot Window contains a list of tables with the data that you selected during import set up process. Each table is stored as its own separate PowerPivot tab. For instance, if you imported three tables, you will see three tabs in the Excel file.

The power of PowerPivot comes from its ability to connect together different data sources and establish relationships between tables with the data. You can bring data from SQL Server, Access and Excel file into one PowerPivot Window and perform any Business Analysis on this data with ease.

There are ways of setting up relationships with the help of primary keys within PowerPivot. There are instances when primary keys are not recognized during conversion. For example, Excel does not have concept of Primary Key and often time data, which you bring from Excel file, is translated into double data type. In this case, you need to convert decimals into integers. There is one limitation with the PowerPivot when it comes to keys. It does not support composite keys and you may need to use DAX to combine multiple columns.

The PowerPivot relationships are identified by little icons on the right side of the column within table. Two little windows situated next to each other means that this field is a primary key. Two little windows with a magnifying glass icon identify this field as foreign key field.

The PowerPivot relationships are important if you trying to create a chart that draws data from more than one table. Actually, PowerPivot will try to find relationships automatically and will establish them for you. However, it is advisable to set up those relationships manually. Hence, it is important to understand concepts of relational data in order to set those relationships properly.

In order to start managing relationships, you need to navigate to the Design Ribbon and select “Manage Relationships” icon within Relationship group.

 

Some description
Pic 1: Manage Relationships

You can see from the Pic 1. above parent table on the left and related lookup table on the right side with relationship key that ties them together identified within brackets. Key on the left side is called “Primary Key” and Key on the right side is called "Foreign Key".

However, it will be required to create relationships manually often times. It is even more so when you try to bring data from multiple data sources. In order to create such relationships, you need to click on Create button position on the left top side of the screen. This action will open up another window called “Create Relationship”. You will be able to choose two tables available in PowerPivot Window and select column that is used to tie these two tables together.

Featured pages

Sharepoint

Learn SharePoint integration with PowerPivot and start utilizing enterprise grade software for comp…

Excel

Learn how to use PowerPivot in Excel and gain insights into your data. PowerPivot for Excel helps …

SQL

Learn about little known PowerPivot tips and tricks and make your Excel PowerPivot Business Intell…

Intelligence

Learn PowerPivot Business Intelligence capabilities and perform BI tasks on large data sets using …