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.

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?