At the core of Power Pivot is relational data modeling, which allows users to create relationships between tables for efficient calculation and data analysis. With Power Pivot, users can create and manage these relationships using a drag-and-drop interface. Let’s see how it’s done using a simple office employees database.
The three tables in the workbook have already been loaded to the Data Model. To learn how to do that, check out this post.
Viewing the schema
We would like to define the relationships between the three tables in this workbook: emp, loc, and dept. To get started, head to the Power Pivot tab of the ribbon and Manage > Diagram View.
In Diagram View, you should see the three tables, each listing their respective column names.
Defining the relationships
Defining relationships in Power Pivot lets us link data from across tables based on common columns, creating a unified Data Model. This will let us create more sophisticated and efficient analyses and reports.
To create the first relationship, click anywhere on the emp table. Next, head to the Design tab of Power Pivot and click Create Relationship:
Let’s start by creating a relationship between the emp and dept tables. Select dept from the second dropdown menu, then click to highlight dept_no in each table.
If you’ve never defined a data relationship before, think back to VLOOKUP(): we want to find a column in common between the two tables that will allow us to combine these data sources together. That’s the dept_no column in this case.
Click OK and you should now see a line connecting the two tables:
We still need to create one more relationship in the Data Model so that all three tables are connected. Can you find it?
This one will be the locno field that is found in both emp and loc. Rather than set this one up through the Create Relationship tab, we can simply drag-and-drop it between the two tables:
Identifying fact and dimension tables
Nice work creating a cohesive Data Model! A typical next step is to identify its fact tables and dimension tables.
We do math with fact tables
If you’ve never heard of this distinction, think of the types of data that you might find in a table. Some data is the kind you might “do math on,” such as taking averages, minimums and maximums. Fact tables are those which store this quantitative data.
For example, the emp table contains the salary for each employee. We can use this column to find the average salary or total payroll by department, for example. It also has a hire date column which we can perform some basic operations on, such as calculating each employee’s tenure.
Dimension tables help us identify, slice and dice
Often, fact tables lack helpful context for making sense of the numbers. For example, we might want to know what department each employee is affiliated with, but all we have is a dept_no identifier. Fortunately, as we saw before, this field is also found in dept, which serves as a dimension table. While dimension tables typically don’t have columns that we can “do math” on, they are invaluable to help us slice, dice and make better sense of the data:
This distinction between fact and dimension tables can help users make sense of and manage the Data Model. It also serves as a useful way to organize our Power Pivot diagram.
Arranging the Diagram View
Our Data Model only has three tables right now, but in the business world, it’s not uncommon to have a dozen or more. By organizing the Data Model diagram, users can better grasp and fix issues in the model design. One easy trick is to put the fact table in the middle of the diagram and surround it with dimension tables, like so:
Managing the relationships
If for any reason you need to edit any of the defined relationships, Power Pivot has a few options.
First, you can right-click on the line defining any relationship and select Edit Relationship to head back to the earlier menu, where you can change which tables are related to one another and by what columns.
You can also make it temporarily inactive or delete it altogether with this right-click.
To manage all relationships found in the Data Model in one place, head to the Design tab of the ribbon, then Manage Relationships:
This table also contains information about the cardinality and filter direction of each relationship.
Though this data model is relatively simple, others can be more complex, and databases may not follow identical rules. Nonetheless, these guidelines are highly beneficial.
Were you able to follow along? Check it out for yourself:
What questions do you have about creating and maintaining relationships in Power Pivot? How has the distinction between fact and dimension tables change how you perceive and work with data? Let me know in the comments.
Leave a Reply