Data Model is available in Excel 2013 and later versions. In Excel, you can use the Data Model to integrate data from multiple tables in the current workbook and / or from the imported data and / or from the data sources connected to the workbook through data connections.
With a Data Model, you can create relationships among the tables. Data model is used transparently in PivotTable, PivotChart, PowerPivot and Power View reports.
When you import data from relational databases like Microsoft Access database that contain multiple related tables, Data Model is automatically created if you import more than one table at once.
You can optionally add tables to the Data Model, when you import data from the following data sources −
For example, while you are importing data from an Excel workbook, you can observe the option Add this data to the Data Model, with an enabled check box.
If you want to add the data you are importing to the Data Model, check the box.
You can create the Data Model from Excel tables using PowerPivot commands. You will learn PowerPivot in detail in later chapters.
All the Data Model commands are available under the PowerPivot tab on the Ribbon. You can add Excel tables to the Data Model with these commands.
Consider the following sales data workbook, wherein you have Product Catalog worksheet that contains Product, Product ID and Price. You have four worksheets for the sales in 4 regions – East, North, South and West.
Each of these four worksheets contain No. of Units sold and Total Amount for each of the Products in each month. You need to calculate the total amount for each of the products in each region and total amount of sales in each region.
The following steps enable you to arrive at the desired results −
A new window – PowerPivot for Excel - <your Excel file name> appears.
The following message appears in the center of the blank window −
The table Product Backlog that you added to the Data Model appears as a sheet in PowerPivot window. Each row in the table is a record and you can go back and forth the records using the left and right arrow buttons at the bottom of the window.
The Excel data window appears.
Another sheet appears in the PowerPivot window displaying the East table.
Repeat for the worksheets – North, South and West. In all, you have added five tables to the Data Model. Your PowerPivot window looks as below −
If you want to make calculations across the tables, you have to first define relationships among them.
Click the Home tab on the Ribbon in the PowerPivot window. As you can observe, the tables are displayed in the Data View.
Click Diagram View.
The tables appear in the Diagram View. As you observe, some of the tables may be out of display area and all the fields in the tables may not be visible.
In the box under Table, East is displayed. In the box under Column, Product ID is displayed.
Line representing Relationship between the tables East and Product Backlog appears.
Now, you are all set to summarize the sales data for each of the products in each region in just few steps.
Create PivotTable dialog box appears in the Excel tables window. Select New Worksheet.
In a new worksheet, an empty PivotTable appears. As you can observe, the Fields List contains all the tables in the Data Model with all the fields displayed.
Select Product ID from Table 1 (Product Catalog).
Select Total Amount from the other four tables.
For each of fields in ∑ Values, change the Custom Name in Value Field Settings to display the region names as column labels.
The sum of Total Amount will be replaced by the label you give. PivotTable with summarized values from all the data tables shows you the required results.
You can add a new data table to the Data Model or new rows of data to the existing tables in the Data Model.
Add a new data table to the Data Model with the following steps.
Click the DATA tab on the Ribbon.
Click Existing Connections in the Get External Data group. The Existing Connections dialog box appears.
Click the Tables tab. The names of all the tables in the workbook will be displayed.
Click the name of the table you want to add to the Data Model.
Click on the Open button. The Import Data dialog box appears.
As you are aware, while importing data table, it is automatically added to the Data Model. The newly added table appears in the PowerPivot window.
Add new rows of data to the existing tables in the Data Model.
Refresh the Data Connection. New rows of data from the data source get added to the Data Model.