In this article, we have explained, how to create relationship in Power Pivot data model. We have taken 2 dimension table and once fact table.
What is Power Pivot?
Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.
In both Excel and in Power Pivot, you can create a Data Model, a collection of tables with relationships. The data model you see in a workbook in Excel is the same data model you see in the Power Pivot window. Any data you import into Excel is available in Power Pivot, and vice versa.
In this example, we have taken 2 dimension table (Location Master and Employee Master) and one fact table (Sales).
How to create the relationship in Power Pivot Data Model?
Creating table relationships requires that each table has a column that contains matching values. For example, in this example we have City in Sales Table (Fact Table) and Location in Location Master Table (Dimension Table).
- In the Power Pivot window, click Diagram View. The Data View spreadsheet layout changes to a visual diagram layout, and the tables are automatically organized, based on their relationships.
- Right-click a table diagram, and then click Create Relationship. The Create Relationship dialog box opens.
- If the table is from a relational database, a column is preselected. If no column is preselected, choose one from the table that contains the data that will be used to correlate the rows in each table.
- For Related Lookup Table, select a table that has at least one column of data that is related to the table you just selected for Table.
- For Column, select the column that contains the data that is related to Related Lookup Column.
- Click Create.
Alternatively, you can simply pick a column form 1st table in diagram view and drag on the other table’s matching column.
Once you have created the relationship successfully, you can create the pivot table just click on Pivot table button. Now all the tables will be available in Pivot table.
