PivotTable Fundamentals - Microsoft Business Intelligence Tools for Excel Analysts - page 40

3. Click the Tables tab, select your first table, and then click Open.

4. Repeat Steps 2 and 3 for each table you want added to the internal Data Model.

After adding all your tables, the Workbook Connections dialog box shows a connection called
ThisWorkbookDataModel , listing all the data sources associated with it.

Any changes made to the tables (such as adding or deleting records or columns) are
automatically captured in the internal Data Model. There's no need to perform any sort
of refresh action.

Note

Build relationships for the tables in the internal Data Model
Although your data now exists in the internal Data Model, Excel does not inherently know how your
tables relate to one another. For example, both tables have a column called Generator_ID (see
Figure 2-29). This column is the key that connects the two tables, allowing you to match transactions
with customer location. You have to explicitly define this relationship before Excel recognizes how to
handle the data in the Data Model.

1. Click the Data tab on the Ribbon and click the Relationships button.

The Manage Relationships dialog box opens.

2. Click the New button.

The Create Relationship dialog box opens, as shown in Figure 2-32.

3. Select the tables and fields that define the relationship.

In Figure 2-31, the Transactions table has a Generator_ID field. It is related to the Generators
table via the Generator_ID field.

4. Click OK.

Figure 2-31: Create the relationships between your tables, defining each table and the associated fields.