Excel Power Pivot - Managing Data Model

Power Pivot empowers us to unify and analyze data from multiple Excel tables, creating interconnected data models that reveal insights unattainable with standard PivotTables. By linking datasets like Orders, Products, and Returns, we can perform tasks such as calculating total sales by region or tracking inventory returns across departments.

Prerequisites

• Power Pivot Enabled: Ensure Power Pivot is enabled (File > Options > Add-ins > COM Add-ins > Microsoft Office Power Pivot).
• Table Format: Power Pivot requires data in Excel tables (not raw ranges).
• Dataset Requirements: Tables must have common columns (e.g., Product_ID, Order_ID) for relationships.

1. Managing Data Model

When data is formed by combining various sources, then those types of data are formed by Data Models.

It is created by an existing relationship between two or more tables. It is created automatically when we import two or more tables from a database. Let's understand how to Manage Data Models using Excel Power Pivot with the help of an example.

Step 1: Open MS-Excel and save it as any name, let's save it as "Managing Data Model". 

Then we need to take any dataset. Let's say we take three different datasets whose names are Orders, Products, and Return respectively. And our task is to manage the whole data in Power Pivot and find the Sum of Sales in each Region.  

2. Dataset of Order

3. Dataset of Products

4. Dataset of Return

Step 2: Now, before going to power pivot we need to convert this dataset into tables because Power Pivot is usable for Tables only.  

So, convert this dataset into tables by simply selecting any cell of the Order data set -> then pressing ctrl + t -> then selecting OK. 

Similarly, repeat these steps to convert them into Tables for the Product dataset and Return dataset.  When you convert your dataset into tables then it will show like images mentioned below : 

5. Conversion of Order Dataset into the table

6. Conversion of Product Dataset into the Table

7. Conversion of the Return Dataset into the Table

Step 3: Now, we need to understand that, we aim to find the Sum of Sales in each region. So the region column is in the first dataset i.e.: Orders. And Sales Column is in the second dataset i.e.; Products so, here PowerPivot came in the role to bring them in a single comparison. To do that, first rename all the table names From Table1, Table2, and Table3 to Orders, Products, and Return respectively. 

To rename a table name just click on any cell of that particular table -> go to Table design -> Rename table name at the top left corner.

8. Rename Table 1 to Orders

9. Rename Table 2 to the Product

10. Rename Table 3 to Return

Step 4: Then go to Option Slide -> go to Power Pivot -> Choose "Add to Data Model" -> then go back to Excel -> Now, Go to Products slide -> then repeat the same step for Product slide also -> then repeat the same step for return slide. 

The common column between the Order dataset and the product dataset is product_id and the common column between the ordered dataset and return dataset is Order_id.  

Open the previous Power Pivot Slide of Order tab -> go to the design tab -> Create Relationship -> Choose Product_id from Order Dataset and Product dataset.

Choosing Product_id from the Ordered Dataset

Choosing Product_id from the Product Dataset

Similarly, Choose Order_id from the Order dataset and Return dataset. 

Choosing Order_id from the Ordered Dataset

Step 5: You can check Your relationship by going to manage the relationship.

Here, you can see that both relationships are active. 

Step 6: At last go to the Home tab -> Choose Pivot Table -> it will show you a pop-up -> press ok -> it will bring you to a new slide inside your previous Excel file.

It will bring you to a new slide inside your Excel file.

From Pivot Table Fields, Drag "Region" from the Order dataset to Rows.

Then, From the product dataset, Drag Sales to Values.

So by doing this way, we managed our data using Power Pivot. In the last above-mentioned image we can see that it is showing the sales done in each region, which is impossible to do without using Power Pivot because Region and sales columns are in two different datasets, if we try to do it with a normal Pivot table then it not be happening, a normal pivot table can be used if these two columns of regions and sales are present in any single dataset. 

Managing Relationship 

Users can edit or delete an existing relationship in the data model.

Steps to see all the Existing Relationships in the Data Model

Step 1: Click the design tab in the Power pivot window.

Step 2: In the Relationship group, click on Manage the Relationship.  

A dialog box appears with all the existing relationships in the data model.

Steps to Edit a Relationship

Step 1: Select the relationship you want to edit.

Step 2: Select the edit button. A dialog box appears for the edit relationship.

Step 3: Now you can make the required changes. 

Step 4: The required changes will reflect on your data after clicking "OK".  

Steps to Delete the Relationship

Step 1: Select the relationship you want to delete.

Step 2: Select the delete button. 

Note: A alert message will pop up showing how the affected tables will affect the reports after deleting the relationship.

Step 3: Click Ok if you surely want to delete that relationship. The relationship is deleted.