Excel Power Pivot – Managing Data Model

Power Pivot is something that helps us in relating between two different data sets which are in two different worksheets. We can manage and relate any type of data using Power Pivot. It is used for data analysis and creates many different data models. we can collect large data from different sheets and can apply or perform any relation between them. Power Pivot is usable for tables only. If we have any dataset then we need to convert that dataset into tables before using Power Pivot.

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

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.  

Dataset of Order

Dataset of Products

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 : 

Conversion of Order Dataset into the table

Conversion of Product Dataset into the Table

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.

Rename Table 1 to Orders

Rename Table 2 to the Product

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. 

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.

Q1. What is refreshing Power Pivot data?

Answer:

When you make some changes in the Excel table data that is adding, deleting, and changing data in the Excel table. 

The data table will be modified with all the changes in the Excel by following the below steps:

Step 1: Select the linked table tab in the PowerPoint window.

Step 2: Click update all.

Q2: What is Power Pivot in Excel do?

Answer: 

Power pivot allows the user to create Sophisticated data models and powerful data analysis.

Q3: What are the different ways by which users can Create Relationships between tables?

Answer:

Users can create relationships in two ways 

•  From data view.
• From diagram view.

Q4: What are the key features of using Power Pivot over Basic Excel?

Answer: 

• Power Pivot allows you to visualize the data with Pivot charts and Power BI.
• It lets you import data from multiple sources into one Single Source workbook without creating multiple source sheets.
• It helps you to manipulate the import data, analyze it, and draw conclusions without making your computer slow.