Open the Workbook which we prepared in the last exercise in our previous post.
Add Excel Sheet/Table to the PowerPivot Data Model
- In the Power Pivot window, in the View section, click Diagram View.
- Use the slide bar to resize the diagram so that you can see all objects in the diagram. Rearrange the tables by dragging their title bar, so they’re visible and positioned next to one another. Notice that all tables are unrelated to each other, we will create relationship among these tables.
After adding relationship the Diagram will Look as below.
- Let start creating the relationship.
- Sports->SportID click and drag to Disciplines->SportID and release. You will see the relation is built between these two tables on SportID.
Similarly follow to create other relations too as shown in below image.
You notice that both the Medals table and the Events table have a field called DisciplineEvent. Upon further inspection, you determine that the DisciplineEvent field in the Events table consists of unique, non-repeated values.
The DisciplineEvent field represents a unique combination of each Discipline and Event. In the Medals table, however, the DisciplineEvent field repeats many times. That makes sense, because each Discipline+Event combination results in three awarded medals (gold, silver, bronze), which are awarded for each Olympics Edition the Event is held. So the relationship between those tables is one (one unique Discipline+Event entry in the Disciplines table) to many (multiple entries for each Discipline+Event value).
- Create a relationship between the Medals table and the Events table. While in Diagram View, drag the DisciplineEvent field from the Events table to the DisciplineEvent field in Medals. A line appears between them, indicating a relationship has been established.
- Click the line that connects Events and Medals. The highlighted fields define the relationship, as shown in the above screen.
- To connect Hosts to the Data Model, we need a field with values that uniquely identify each row in the Hosts table. Then we can search our Data Model to see if that same data exists in another table. Looking in Diagram View doesn’t allow us to do this. With Hosts selected, switch back to Data View.
- After examining the columns, we realize that Hosts doesn’t have a column of unique values. (Assume EditionID Field is not available in the Host & Medal Table, in my case I have already added this Field in my workbook I shared). In such case we’ll have to create it using a calculated column, and Data Analysis Expressions (DAX).
It’s nice when the data in your Data Model has all the fields necessary to create relationships, and mash up data to visualize in Power View or PivotTables. But tables aren’t always so cooperative, so the next post will describe how to create a new column, using DAX that can be used to create a relationship between tables.
We will see how to work with calculated fields in our next upcoming post.
ARKSD Consultancy Private Limited 