The Data Model gives us the ability to work with multiple tables and integrate them with each other using a specified relationship to build a relational database inside Microsoft Excel. It helps to work with larger datasets and provides us with a huge set of interactive features for creating, modifying, and managing datasets simply and easily. All these operations are performed with the help of Power Views in Excel, which enables the user to visualize the different operations that need to be performed on the data model.
In this example, we are going to use Power View with Data Model to create a relational database between different factories and the product. In order to learn, how to enable and use power view please, refer How to Enable and Use Power View in Excel?
Creating Data Model using Power View
Here is how you can create the Data Model from Power View Sheet:
Step 1: You should have a new workbook containing Factory details and Product details in two different worksheets.
Step 2: From the data present in the Factory worksheet, create a table. Select all the data in the table, click on Insert -> Table. A pop-up appears, on that click OK.
Step 3: Similarly, from the data in the Product worksheet, create another table.
Step 4: Browse to the Product worksheet. In the ribbon, click on Insert -> Power View.
A new Power View sheet named Power View1 is created. It contains all the fields present in the Product worksheet. The visualization is in the form of a table. This Power View currently contains no Data Models.
In the Power View Fields, both the tables Factory and Product in our workbook are shown. And the Power View shows only the Product table present in the Active tab.
Step 5: The Power View table shows Factory ID, but what if we want to display the Factory Name instead. For this, we need to make the following changes in the Power View Fields:
- Deselect the Factory ID field in Product table.
- Select the Factory Name field in Factory table.
We get this message as we do not have any Data Model in the workbook, thus no relationship exists between the tables.
Step 6: Click on the CREATE button in the Power View Fields pane, and a Create Relationship dialogue box appears.
Create a Relationship between the tables by picking the following tables and columns:
- Table: Product
- Column (Foreign): Factory ID
- Related Table: Factory
- Related Column (Primary): Factory ID
Thus, we have created a Data Model with the two tables, which contain one Relationship between them. The field Factory Name is now displayed in the Power View table, in place of Factory ID.
Step 7: To change the Visualization of the Power View:
- Select only the Region, Factory Name, and Profit fields and deselect all others.
- Arrange them in the FIELDS section in the above order.
- Drag the Month field to TILE BY section.
- Switch the visualization to Matrix in the Power View ribbon.
In the Matrix visualization, for each of the Regions, the Factory Names and their Profits are displayed, along with the subtotal for each region. The tiles above represent the respective Months for which the following data is displayed. Changing between the Months will show the data for that month.
Step 8: The Data Model of the Power View can be viewed from the Power Pivot window:
- Go to the Power Pivot tab in the ribbon.
- Click on Manage in the Data Model group. The Power Pivot window opens.
- Make sure Diagram View is selected in the View group.
The Data Model along with the Relationship between the tables Product and Factory is shown.
Modifying Data Model from Power View
The Data Model in a Power View worksheet can be modified and improved upon by creating new tables in the workbook and defining new relationships among the tables.
Step 1: Create a new table named Profit in the workbook.
Step 2: In the Power View1 worksheet, change the visualization from Matrix to Table.
Step 3: In the ALL tab of the Power View Fields, the new table Profit is now present. Click on the Power Pivot tab on the ribbon to open the Power Pivot window. Click Manage -> Diagram View. The Data Model is displayed.
Step 4: As we can see, the Profit table has no relationships. Now we have to add the Profit table to the Data Model:
- Go to the Power View1 sheet.
- In the Power View Fields pane, deselect the Month field from the Product table.
- Select the Annual Profit field from the Profit table.
Step 5: The Annual Profit field is showing incorrect values, as we currently do not have any relationship between the Profit table and other tables. Click on the CREATE button in the Power View Fields pane and a Create Relationship dialogue box appears.
Create a Relationship between the tables by picking the following tables and columns:
Table: Factory
Column (Foreign): Factory ID
Related Table: Profit
Related Column (Primary): Factory ID
Step 6: Open the Diagram View from the Power Pivot window.
We see that the new relationship is now displayed. The Data Model now consists of two relationships between the three tables in our workbook.
Step 7: Click on the arrow to the right of the field Annual Profit in the FIELDS section. Select the option Do Not Summarize from the dropdown menu.
Step 8: Arrange the fields in the FIELDS section, in the order of Region, Factory Name, Annual Profit, profit.
Conclusion
Thus, we can conclude that the Data Model in Power View of Microsoft Excel is a very powerful feature to work on large data sets, define relationships among them, calculate and display data in different forms and even modify existing relationships. A proper knowledge of Data Models helps us to manage data efficiently in Excel.