When the data is loaded to the Power Pivot model (as shown in Part 2), you’ll see that we have 6 tables in 6 tabs attached to the model. These tables are:
Since we are analyzing sales and marketing data, each table would represent an important piece for our analysis. We don’t have to go through each table to explain what it consists of, but the table’s title is really self-explanatory.
Within Power Pivot window, in the Home ribbon, you will see that at the right in the “View” section, the Data View is selected. This is default view that shows all the tabs in the Excel-like view. Clicking on the Diagram View next to it switches to box-like view as shown in the figure below.
The easiest way to create relationships among these tables is from the Diagram View.
Now before we create our first relationship, let’s first go through why would we need to create the relationships in the first place.
You can think of relationships as automated VLOOKUPs. In the past, if you for example have a data table that contains part numbers, selling price, dates, customer names etc… and a lookup table that contains the cost of these parts identified by part number. Now if you wanted to create a pivot table that shows the price, cost and margin for all customers, you’d have to first flatten your data table using VLOOKUP to include your cost from the lookup table.
Now with Relationships, you can forget about VLOOKUPs. Yes I know. It might have been your favorite Excel formula for years now, but trust me with PowerPivot you do not need VLOOKUP. You leave your data where it is, and leverage the power of relationships to build pivot tables that pull from multiple tables. You can not do this in old-school Excel.
This makes you insanely more efficient. Want to view sales by Age? Drop it on your pivot table. Done. Want to view sales by weather? Import weather data, relate OrderDate to weather date… drop “Rainfall” on your table… done.
Now that I got you excited about creating relationships, let go ahead and create them.
Remember the Diagram View we discussed above? Let’s connect our SalesFact table to the Product Table. All you have to do is drag Product ID field from the sales fact table to the Product ID in the product table.
Now some facts about relationships that you need to understand:
You don’t have to worry about the direction of the relationship; it’s detected automatically by PowerPivot, so all you need to do is just drag the line from whichever table to the other.
Now let’s do the same for the rest of the tables, and connect them to each other:
You see that the Geography table (bi_geo) is left out and not connected to any other table. When we try to connect to the salesfact table through the Zip column, we get the below error.
Now this is because we tried to create a many-to-many relationship (the same Zip code is repeated more than once in each of the tables) which is not supported by PowerPivot and this is what we just discussed above in the relationship facts section.
But don’t panic. We got this under control and we will overcome this in the next part, so stay tuned :)