Exploring Data with Powerpivot

PowerPivot is an easy to use Data Analysis tool that can be used from within Excel. You can use PowerPivot to access and mashup data from virtually any data source. You can create your own fascinating reports with PowerPivot.

You can access the PowerPivot commands from PowerPivot tab on the Ribbon. Cpck the PowerPivot tab on the Ribbon. The PowerPivot commands will be displayed on the Ribbon. You can observe that the commands related to Data Model also appear here.

Adding Tables to Data Model

If you have imported tables, they are added to the Data Model. You can manage the Data Model from PowerPivot Ribbon. You can add tables to Data Model with PowerPivot as follows −

Insert a new worksheet in your workbook.

Copy data from the web page and paste it on the worksheet.

Create a table with the pasted data.

Name the table as Hosts.

Rename the worksheet as Hosts.

Cpck the table Hosts.

Cpck the POWERPIVOT tab on the Ribbon.

Cpck Add to Data Model in the Tables group.

The table is added to the Data Model. The PowerPivot window appears. You will find the table Hosts in the Data Model tables.

Viewing Tables in the Data Model

Cpck on the POWERPIVOT tab on the Ribbon.

Cpck on Manage in the Data Model group.

PowerPivot window appears, in Data View.

PowerPivot has two views −

Data View − It displays all the tables in the Data Model with fields displayed in columns and data as records in the rows, with a calculation area below each table. The table tabs look similar to the Excel worksheet tabs with names. You can move from table to table by cpcking on the tabs.

Diagram View − It displays all the tables as boxes with table name as caption and the fields psted in the box. You can drag the tables to apgn them, resize them to make all the fields visible, and create relationships by just cpcking on the fields and connecting them with pnes.

You will understand the Diagram View and the Relationships in detail in the later sections.

Here, observe that all the tables in the Data Model are visible in the PowerPivot window, irrespective of whether they are present as worksheets in the workbook or not.

Viewing Relationships between Tables

You can use the data from different tables for analysis and reporting only when relationships exist among them.

You can view the relationships between tables from the diagram view in the PowerPivot window.

Cpck Diagram View in the View group.

Resize the diagram using the scroll bar so that you can see all the tables in the Data Model in the diagram.

All the tables in the Data Model appear with their fields psts. The relationships among the tables are denoted by the pnes connecting them.

Creating Relationships between Tables

You might want to create a relationship between the tables – Medals and Events. In order to do this, there should be a field that is common in both the tables and contains unique values in one of the tables. First, you need to verify this.

Cpck the Data View in the View group.

Cpck the Events tab to view the Events table.

You can observe that the field DiscippneEvent in the Events table has unique values (no duppcate values).

Cpck the Medals tab to view the Medals table. The field DiscippneEvent is available in the Medals table also. Hence, you can create a relationship using the field DiscippneEvent as follows −

Cpck Diagram View in the View group.

Rearrange the tables in the view by dragging them so that Events table and Medals table are close to each other.

Resize the tables so that all the fields are visible.

Cpck the field DiscippneEvent in the Events table and Drag to the field DiscippneEvent in the Medals table.

A pne appears between the Events table and the Medals table, indicating that a relationship has been estabpshed.

Viewing the Field defining a Relationship

You can view the field that is used to create the relationship between two tables.

Cpck the relationship pne connecting the two tables. The relationship pne and the field defining the relationship between the two tables get highpghted.