Skip to main content

Introduction to PowerPivot Relationships

Rate:  

About This Video:

This video provides an introduction to Relationships in PowerPivot. A relationship is a connection between two tables of data. Relationships in PowerPivot are created either by manually joining tables in the PowerPivot workspace, or by automatically detecting existing relationships when loading data into a PowerPivot workbook.

This video is available with closed captions. To view closed captions, click CC on the video control bar.

Transcript

Welcome to another PowerPivot screen cast from Michele and Ed.

This video is an introduction to PowerPivot Relationships.

A relationship is a connection between two tables of data.

Relationships in PowerPivot are created either by manually joining the tables in the PowerPivot window or by automatically detecting existing relationships while importing data into a PowerPivot workbook.

I can create relationships from previously unrelated data sources by matching columns that contain similar or identical data.

When I define a relationship, I connect a source column, sometimes called a foreign key column, in the first table to a target column, also known as the primary key column, in a second table.

For example, the internetSales table may include a list of books sold at my store.

The table includes customer ID, ISBN and Price.

In this table, the ISBN is a foreign key that joins the internetSales table with the scifi table.

In the scifi table ISBN is a unique primary key column.

There is one ISBN for each science fiction book that I sell.

In the internetSales table ISBN is not unique.

I sell more than one copy of books, so the ISBN column contains duplicate values.

But why are relationships important?

Without relationships my PowerPivot workbook is a collection of unrelated data.

By creating relationships, I join my data together in a cohesive unit sometimes referred to as a Model or a PowerPivot Application.

I can now create reports and BI analysis that slice, filter, aggregate and explore all of my data.

For example, by creating relationships, I can then filter data by using related columns.

I can create relationships between tables that I can use as data in the PivotTable and PivotCharts that I create later.

PowerPivot data storage is already more compact and the use of relationships reduces data redundancy so that I can keep my workbook smaller.

Now that I know what relationships are and why they are important it's time to create some relationships in my PowerPivot workbook.

Thank you for taking the time to view this screen cast. I hope you found it helpful.

 

Presented by: mihart

 

Downloads

Video: WMV(Zip) | MP4 | WMV