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

You return to the Manage Relationships dialog box where you can add, delete, and edit relationships.

In Figure 2-31, notice that the lower right-hand drop-down list is called Related Column
(Primary). The term Primary means that the internal Data Model uses this field from the
associated table as the primary key. Every relationship must have a field you designate
as the primary key. Primary key fields are necessary in the Data Model to prevent
aggregation errors and duplications. In that light, the Excel Data model must impose
some strict rules around the primary key. You cannot have any duplicates or null values
in a field being used as the primary key. So the Generators table (in the scenario in
Figure 2-31) must have all unique values in its Generator_ID field; with no blanks or null
values. This is the only way Excel can ensure data integrity when joining multiple
tables.

Note

Using your Data Model in a PivotTable
After you have filled your internal Data Model, you can start using it. Later, of course, you learn how
to leverage it with Power View (Chapter 5).

Follow these steps to leverage the Data Model in PivotTables to analyze the data within:

1. Click the Insert tab and click the PivotTable icon.

2. In the Create PivotTable dialog box, select the Use an External Data Source option and click
the Choose Connection button.

The Existing Connections dialog box opens.

3. Click the Tables tab and select Tables in Workbook Data Model. Click Open.

4. Click OK in the Create PivotTable dialog box.

After the PivotTable is created, the PivotTable Fields list shows each individual Table in the internal
Data Model (see Figure 2-32).

Figure 2-32: PivotTables that use the internal Data Model as the source show all the tables within the Data Model.

With a Data Model-driven PivotTable, you have the ability to merge disparate data sources into one ana-
lytical engine. Figure 2-33 demonstrates how you can build a view using data fields from the different
tables in the Data Model.