Читать книгу Excel Power Pivot & Power Query For Dummies - Michael Alexander - Страница 30

At this point, Power Pivot knows that you have three tables in the data model but has no idea how the tables relate to one another. You connect these tables by defining relationships between the Customers, Invoice Details, and Invoice Header tables. You can do so directly within the Power Pivot window.

If you’ve inadvertently closed the Power Pivot window, you can easily reopen it by clicking the Manage command button on the Power Pivot Ribbon tab.

Follow these steps to create relationships between your tables:

1 Activate the Power Pivot window and click the Diagram View command button on the Home tab.The Power Pivot screen you see shows a visual representation of all tables in the data model, as shown in Figure 2-7. You can move the tables in Diagram view by simply clicking and dragging them.The idea is to identify the primary index keys in each table and connect them. In this scenario, the Customers table and the Invoice Header table can be connected using the CustomerID field. The Invoice Header and Invoice Details tables can be connected using the InvoiceNumber field.FIGURE 2-7: Diagram view allows you to see all tables in the data model.

2 Click and drag a line from the CustomerID field in the Customers table to the CustomerID field in the Invoice Header table, as demonstrated in Figure 2-8.

3 Click and drag a line from the InvoiceNumber field in the Invoice Header table to the InvoiceNumber field in the Invoice Details table.

FIGURE 2-8: To create a relationship, you simply click and drag a line between the fields in your tables.

At this point, your diagram will look similar to Figure 2-9. Notice that Power Pivot shows a line between the tables you just connected. In database terms, these are referred to as joins.

FIGURE 2-9: When you create relationships, the Power Pivot diagram shows join lines between tables.

The joins in Power Pivot are always one-to-many joins. This means that when a table is joined to another, one of the tables has unique records with unique index numbers (CustomerID for example), while the other can have many records where index numbers are duplicated.

Notice in Figure 2-9 that the join lines have arrows pointing from a table to another table. The arrows in these join lines will always point to the table that has the duplicated index. In this case, the Customers table contains a unique list of customers, each having its own unique identifier. No CustomerID in that table is duplicated. The Invoice header table has many rows for each CustomerID; each customer can have many invoices.

To close the diagram and return to seeing the data tables, click the Data View command in the Power Pivot window.