1. Data modeling in Power Pivot
Hello there! In this screencast, we will review how to use various data modeling techniques in Power Pivot, including creating relationships and building hierarchies in our data.
In Power Pivot, creating relationships is very simple. First, we open up the Power Pivot window. Then let’s go to the diagram view. Here, we can see in a visual way how our datasets are related to each other. We have three tables. Schedule is the fact table that holds event information for each class that was scheduled at PowerPivot Gym. Then we have two dimension tables: classes and instructors.
On the schedule table, we can see the foreign keys we need to define these relationships: class ID and Instructor ID. As a reminder, foreign keys are columns that can be used to join datasets together. You may also hear the term primary key. Primary keys are unique identifiers, for example, Class ID is also a primary key on the classes table because it identifies each unique class.
To create a relationship, we can easily drag the field from one table to another. Its as easy as that! Notice that it also gives us information on the cardinality, one to many, with the asterisk symbol for many.
Let’s also do this for the instructors table. We can join this to schedules on instructor id. Nice!
We can also create a hierarchy in our data with Location and Studio. Looking in the data view, we see that Location describes the physical location of the gym, and the studio corresponds with which studio at the gym.
So, going back to our diagram view, we can create a hierarchy by highlighting our two fields, then right-clicking and selecting Create Hierarchy. Let’s rename this to “Studio Location”.
Wonderful! Let’s see this in action by putting this into a PivotTable. Let’s put the Studio Location Hierarchy into the rows. Here, we can see that East Bloomfield holds studio 1, whereas Farming Hills has studios 2 and 3. From here, we can do even more analysis if we want to!
There’s one last thing to show you. Power Pivot can create a date dimension table with just a few inputs. Let’s go back to the Power Pivot window, to the Design ribbon. Then, on this date table icon, click new. That’s it! Pretty cool, huh?
Now, this table goes back to 1899, which is too far for our analysis. We can save some memory by making it shorter. So click on that date table icon, then update the range. Since our schedule data is only in 2024, we’ll just make this start on January 1, 2024.
Finally, in the diagram view, let’s create a relationship from this table to our schedule table.
Data modeling is easy to do in Power Pivot, and creating these relationships in a visual way makes this a lot more organized than linking tables together with VLOOKUPs. These features are just the beginning of Power Pivot’s capabilities. Now you’ve seen it, it’s your turn to give it a try!
2. Let's practice!