Powerpivot Excel 2010

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.

More PowerPivot tutorials at Building power pivots. Using vertical & horizontal slicers. Power Pivot provides advanced data modeling features in Microsoft Excel. Use the resources below to learn about how you can use Power Pivot to help you gain new insights into your data. PowerPivot for Excel 2010(formerly Gemini). PowerPivot for Excel is a powerful data analysis add-in that will let you work with millions of records within the familiar Excel environment. If you haven't downloaded the Office 2010 Beta, you can test PowerPivot. PowerPivot empowers users of all levels to access and mashup data from virtually any source. Users can create their own compelling reports and analytical applications, easily share insights, and collaborate with colleagues through Microsoft Excel and SharePoint.

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.

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

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

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

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.

You can review all the relationships that have been created.

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!