Microsoft Office Tutorials and References
In Depth Information
Understanding the Internal Data Model
Figure 17-8: Create the relationships between your tables, defining each table and the associated fields.
Figure 17-9: Use the Manage Relationships dialog box to add, delete, and edit relationships.
In Figure 17-8, at the lower right, notice the Related Column (Primary) drop-down field.
The term Primary means that the internal Data Model will use 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. Thus the Excel Data model must impose some
strict rules around the primary key. You cannot have duplicates or null values in a field
being used as the primary key. So the Generators table (shown in Figure 17-8) 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.
Using your Data Model in a pivot table
After you fill your internal Data Model, you can start using it. Later, in the “Creating a Power View
Dashboard” section, you find out how to leverage it with Power View. First, explore how to leverage
the Data Model in pivot tables to analyze the data within. To create a pivot table from the internal
Data Model, follow these steps:
1. Click the Insert tab and select PivotTable to start a pivot table.
2. In the Create PivotTable dialog box, select Use an External Data Source and click the Choose
Connection button (see Figure 17-10).
The Existing Connections dialog box opens, as shown in Figure 17-11.
3. Click the Tables tab and choose Tables in Workbook Data Model. Click Open to confirm.
You return to the Create PivotTable dialog box.
4. Click OK to finalize the pivot table.