MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Using Relationships (Star Schema & Cardinality)0%
Using Relationships (Star Schema & Cardinality)
Beginner12 min read•Updated: Jul 11, 2026
Using Relationships (Star Schema & Cardinality)
In classical Excel modeling, creating a comprehensive report required using VLOOKUP to pull Customer Name, Product Category, and Store Location into a massive, bloated 50-column sales sheet. In Power Pivot, you build a Relational Data Model using a Star Schema, connecting tables with lightweight relationships.
1. The Star Schema: Fact vs. Dimension Tables
A professional data model organizes tables into two distinct categories:
Visual Architecture Blueprint
+-----------------------------------------------------------------------------------+ | 1. FACT TABLES (The "Data" Table) | | Contains transactional events, numbers, and foreign keys. | | Examples: FactSales (OrderID, DateKey, CustomerKey, ProductKey, Revenue, Units). | | Characteristic: Contains millions of rows; keys repeat many times (* Many). | +-----------------------------------------------------------------------------------+ | 2. DIMENSION TABLES (The "Lookup" Tables) | | Contains descriptive attributes used for slicing and filtering. | | Examples: DimCustomers, DimProducts, DimStores, DimCalendar. | | Characteristic: Contains unique primary keys with NO duplicates (1 One). | +-----------------------------------------------------------------------------------+
Star Schema Visual Layout:
[ DimCalendar ] [ DimProducts ]
/
/
[ FactSalesTable ]
/ / [ DimCustomers ] [ DimStores ]
Click to Zoom2. Cardinality: One-to-Many Relationships (1 : *)
When connecting tables in Excel Power Pivot, relationships must almost always follow a *One-to-Many (1 : )** structure:
- The "One" Side (1): Resides in the Dimension table where each key appears exactly once (e.g., 'DimCustomers[CustomerID]').
- *The "Many" Side ():** Resides in the Fact table where the same customer can make multiple purchases over time (e.g., 'FactSales[CustomerID]').
3. Step-by-Step: Creating Relationships in Diagram View
- 1In Excel, go to Power Pivot > Manage.
- 2On the Home tab of the Power Pivot window, click Diagram View.
- 3You will see visual boxes representing all loaded tables.
- 4Locate DimProducts. Find the unique primary key: ProductKey.
- 5Click and drag ProductKey from DimProducts and drop it directly onto ProductKey in FactSales.
- 6A relationship line appears labeled with a 1 at DimProducts and an *``** (asterisk) at FactSales!
- 7Repeat for Customers, Stores, and Calendar.
4. Building Multi-Table Pivot Tables
Once relationships are established:
- 1Click Home tab > PivotTable in the Power Pivot window.
- 2In the PivotTable Fields pane, you will see all related tables!
- 3Drag Product Category from DimProducts into Rows.
- 4Drag State from DimCustomers into Columns.
- 5Drag Revenue from FactSales into Values.
Excel calculates the cross-tabulation across three separate tables seamlessly without a single VLOOKUP!
Interactive Quiz
Multiple Choice Questions
Question 1
In relational data modeling, what is the primary distinction between a Fact table and a Dimension table?
Question 2
What relationship cardinality is the standard building block for robust Power Pivot models?
Question 3
What happens if you try to drag a relationship from a column in a Dimension table that contains duplicate values?
Question 4
What visual icon represents the "Many" side of a relationship line in Power Pivot Diagram View?
Question 5
Why is building a Star Schema in Power Pivot superior to combining everything into one 60-column sheet with VLOOKUP?
Next Lesson
DAX Basics (Calculated Columns, Measures, SUMX, CALCULATE)
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
Related Lessons
Practice Quiz
Test your understanding of this lesson with 5 questions. Each question has one correct answer.