In This Chapter

Interacting with the Internal Data Model directly

Adding tables to the Internal Data Model

Removing tables from the Internal Data Model

Using multiple tables with the Internal Data Model

In the preceding chapters, you use the Power Pivot add-in to work with the Internal Data Model. But as you’ll see in this chapter, you can use a combination of pivot tables and Excel data connections to directly interact with the Internal Data Model, without the Power Pivot add-in.

This is useful if you’re using versions of Excel that don’t come supplied with the Power Pivot add-in, such as when you’re using Microsoft Office, either Home or Small Business edition. Every Excel 2013 and 2016 workbook comes with an Internal Data Model.

You can find the sample files for this chapter on this book’s companion website at www.dummies.com/go/excelpowerpivotpowerqueryfd. These include the Chapter 5 Sample File.xlsx Excel workbook and the Facility Services.accdb Access database.

Directly Feeding the Internal Data Model

Imagine that you have the Transactions table you see in Figure 5-1, and on another worksheet you have an Employees table (see Figure 5-2) that contains information about the employees.

You need to create an analysis that shows sales by job title. This would normally be difficult given the fact that sales and job title are in two separate tables. But with the Internal Data Model, you can follow these simple steps:

1. Click inside the Transactions data table and start a new pivot table by choosing Insert ⇒ Pivot Table from the Ribbon.
2. In the Create PivotTable dialog box, select the Add This Data to the Data Model option (see Figure 5-3).

3. Click inside the Employees data table and start a new pivot table.

   Again, be sure to select the Add This Data to the Data Model option, as shown in Figure 5-4.

   Notice that in Figures 5-3 and 5-4, the Create PivotTable dialog boxes are referencing named ranges. That is to say, each table was given a specific name. When you’re adding data to the Internal Data Model, it’s a best practice to name the data tables. This way, you can easily recognize your tables in the Internal Data Model.

   If you don’t name your tables, the Internal Data Model shows them as Range1, Range2, and so on.

4. To give the data table a name, simply highlight all data in the table, and then select Formulas ⇒ Define Name command from the Ribbon. In the dialog box, enter a name for the table.

   Repeat for all other tables.

5. After both tables have been added to the Internal Data Model, open the PivotTable Fields list and choose the ALL selector, as shown in Figure 5-5.

   This step shows both ranges in the field list.

6. Build out the pivot table as normal. In this case, Job_Title is placed in the Row area, and Sales_Amount goes to the Values area.

   As you can see in Figure 5-6, Excel immediately recognizes that you’re using two tables from the Internal Data Model and prompts you to create a relationship between them. You have the option to let Excel autodetect the relationships between your tables or to click the Create button. Always create the relationships yourself, to avoid any possibility of Excel getting it wrong.

7. Click the Create button.

   Excel opens the Create Relationship dialog box, shown in Figure 5-7. There, you select the tables and fields that define the relationship. In Figure 5-7, you can see that the Transactions table has a Sales_Rep field. It’s related to the Employees table via the Employee_Number field.

After you create the relationship, you have a single pivot table that effectively uses data from both tables to create the analysis you need. Figure 5-8 illustrates that, by using the Excel Internal Data Model, you’ve achieved the goal of showing sales by job title.

In Figure 5-7, you see that the lower-right drop-down is named Related Column (Primary). The term primary means that the Internal Data Model uses this field from the associated table as the primary key.

A primary key is a field that contains only unique non-null values (no duplicates or blanks). Primary key fields are necessary in the data model to prevent aggregation errors and duplications. Every relationship you create must have a field designated as the primary key.

The Employees table (in the scenario in Figure 5-7) must have all unique values in the Employee_Number field, with no blanks or null values. This is the only way that Excel can ensure data integrity when joining multiple tables.

After you assign tables to the Internal Data Model, you might need to adjust the relationships between the tables. To make changes to the relationships in an Internal Data Model, click the Data tab on the Ribbon and select the Relationships command. The Manage Relationships dialog box, shown in Figure 5-9, opens.

Here, you’ll find the following commands:

• New: Create a new relationship between two tables in the Internal Data Model.
• Edit: Alter the selected relationship.
• Activate: Enforce the selected relationship, telling Excel to consider the relationship when aggregating and analyzing the data in the Internal Data Model.
• Deactivate: Turn off the selected relationship, telling Excel to ignore the relationship when aggregating and analyzing the data in the Internal Data Model.
• Delete: Remove the selected relationship.

Adding a New Table to the Internal Data Model

You can add a new table to the Internal Data Model in one of two ways, as described in this section.

The easiest way is to create a pivot table from the new table and then choose the Add This Data to the Internal Data Model option. Excel adds the table to the Internal Data Model and produces a pivot table. After the table has been added, you can open the Manage Relationships dialog box and create the needed relationship.

The second, and more flexible, method is to define the table manually and add it to the Internal Data Model. Here’s how:

1. Place the cursor inside the data table and select Insert Table.

   The Create Table dialog box, shown in Figure 5-10, opens.

2. Specify the range for your data and click the OK button.

   Excel turns that range into a defined table that the Internal Data Model can recognize.

3. On the Table Tools Design tab, change the Table Name field (in the Properties group), as shown in Figure 5-11.

   Pick a name that’s appropriate and easy to remember.

4. From the Data tab on the Ribbon, select Connections.

   The Workbook Connections dialog box opens, as shown in Figure 5-12.

5. Click the drop-down list next to Add and choose the Add to the Data Model option.

   The Existing Connections dialog box opens.

6. On the Tables tab, find and select the newly created table, as shown in Figure 5-13. Click the Open button to add it to the Internal Data Model.

   At this point, all pivot tables built on the Internal Data Model are updated to reflect the new table. Be sure to open the Manage Relationships dialog box and create the needed relationship.

Removing a Table from the Internal Data Model

You might find that you want to remove a table or data source altogether from the Internal Data Model. To do so, click the Data tab on the Ribbon and then click the Connections command. The Workbook Connections dialog box, shown in Figure 5-14, opens.

Click the table you want to remove from the Internal Data Model (Employees, in this case) and then click the Remove button.

Creating a New Pivot Table Using the Internal Data Model

In certain instances, you may want to create a pivot table from scratch using the existing Internal Data Model as the source data. Here are the steps to do so:

1. Choose Insert ⇒ PivotTable from the Ribbon.

   The Create PivotTable dialog box opens.

2. Select the Use an External Data Source option, as shown in Figure 5-15, and then click the Choose Connection button.

   You see the Existing Connections dialog box, as shown in Figure 5-16.

3. On the Tables tab, select Tables in Workbook Data Model, and then click the Open button.

   You return to the Create PivotTable dialog box.

4. Click the OK button to create the pivot table.

   If all goes well, you see the PivotTable Fields dialog box with all tables that are included in the Internal Data Model, as shown in Figure 5-17.

Filling the Internal Data Model with Multiple External Data Tables

Suppose you have an Access database that contains a normalized set of tables. You want to analyze the data in that database in Excel, so you decide to use the new Excel Internal Data Model to expose the data you need through a pivot table.

To accomplish this task, follow these steps:

1. Click the Data tab on the Ribbon and click the From Access command, as shown in Figure 5-18.

2. Browse to your target Access database and open it.

   The Select Table dialog box opens.

3. Place a check mark next to the Enable Selection of Multiple Tables option (see Figure 5-19).

4. Place a check mark next to each table that you want to import into the Internal Data Model, as shown in Figure 5-20. Then click OK.

   The Import Data dialog box opens.

5. Click the drop-down arrow next to Properties, and remove the check mark next to the Import Relationships Between Tables option, as shown in Figure 5-21.

   This step ensures that Excel doesn’t introduce a potential error by misinterpreting how the tables are related. In other words, you’ll want to create relationships yourself.

6. Still in the Import Data dialog box, choose the PivotTable Report option and click OK to create the base pivot.

7. Click the Data tab on the Ribbon and choose the Relationships command.

   This step opens the Manage Relationships dialog box.

8. Create the needed relationships, as shown in Figure 5-22, and then click Close.

   You now have a pivot table based on external data imported into the Internal Data Model (see Figure 5-23). At this point, you can use the Pivot Table Field list to build the pivot table.

In just a few a few clicks, you have created a powerful platform to build and maintain pivot table analysis based on data in an Access database!