As usual, we will be using the Adventure Works companion material (Chapter 4) from the Ferrari book.
Creating the Data Model
Instead of using VLOOKUP multiple times to add columns into the DimProduct table, we will use relationships to merge all three tables together. Opening the PowerPivot window in Diagram View from the workbook we see that the relationships between the tables have already been formed, because PowerPivot detected existing relationships (will be elaborated in further posts.)
Fig. 1: PowerPivot Diagram View |
Fig. 2: Creating NumOfProducts Calculated Field |
Fig. 3: ProductCategory PivotTable |
So a data model is a set a tables connected by relationships, sometimes with calculated fields or columns. Simple yet powerful.
Notes on Relationships
Though these relationships will not help much in your love life, the following details on PowerPivot relationships will make your life easier when creating and understanding data models.
Looking at the Diagram View of the tables and relationships, we focus on the relationship arrow from DimProduct to DimProductSubcategory.
- DimProduct is the source table (the beginning of the relationship)
- DimProductSubcategory is the target table (where the values relate to source table)
- ProductSubcategoryKey column is the Foreign Key in source table (contains values which is searched in target table for related row)
- ProductSubcategoryKey column is the Primary Key in the target table (needs to have unique values in each row)
These are shown highlighted in blue below:
Fig. 4: DimProduct and DimProductSubcategory Relationship |
We can test the relationship by recreating it. Click the arrow and press Delete. Then in the Design tab, click Create Relationship.
Fig. 5: Creating a Relationship |
So now you know the inner workings of relationships in data models. Some DAX functions work only when invoked on the correct side of a relationship, so sides do matter. Stay tuned for more on data modeling!
Thanks for reading,
Wayne
No comments:
Post a Comment