MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Capstone: Building the Business Performance Data Model0%
Capstone: Building the Business Performance Data Model
Beginner12 min read•Updated: Jul 11, 2026
Capstone: Building the Business Performance Data Model
Welcome to the three-part MS Excel Advanced Capstone Project. In this project, you will build a comprehensive, automated, multi-million dollar Business Performance Dashboard. In this first lesson, we ingest disparate corporate datasets, clean them using Power Query, establish a relational Star Schema, and configure the underlying Power Pivot Data Model.
1. Capstone Project Overview & Data Architecture
Our enterprise model integrates three separate operational data sources:
- 1Fact_Transactions.csv (250,000 rows): OrderID, TransactionDate, CustomerID, ProductID, UnitsSold, UnitSellingPrice, DiscountAmount.
- 2Dim_Products.xlsx: ProductID, ProductName, Category, SubCategory, UnitCost.
- 3Dim_Customers.xlsx: CustomerID, CustomerName, Region, Segment (Enterprise, SMB, Consumer).
Capstone Star Schema Blueprint:
[ Dim_Customers ]
│ (1)
│
▼ (*)
[ Fact_Transactions ] ◄──(*)───(1)─ [ Dim_Products ]
▲
│ (*)
│
(1) [ Dim_Calendar ]
Click to Zoom2. Step-by-Step: Ingestion & Power Query Transformation
Step 1: Ingest and Clean 'Fact_Transactions'
- 1In a fresh workbook, go to Data > Get Data > From File > From Text/CSV.
- 2Select 'Fact_Transactions.csv' and click Transform Data.
- 3Applied Transformations:
- Verify all data types: Ensure 'TransactionDate' is set to Date, 'UnitsSold' to Whole Number, and financial figures to Currency.
- Replace Nulls: Highlight 'DiscountAmount' > right-click > Replace Values (Replace
nullwith0). - Add Custom Column: Click Add Column > Custom Column > Name: GrossRevenue:
[UnitsSold] * [UnitSellingPrice]
- 1Click Close & Load To... > Select Only Create Connection and check Add this data to the Data Model!
Step 2: Ingest Dimension Tables
- Ingest 'Dim_Products' and 'Dim_Customers' via Power Query.
- Promote headers, enforce strict Text types on ID codes, and load them as Connection Only to the Data Model.
3. Step-by-Step: Diagram View & Establishing Relationships
- 1Go to Power Pivot > Manage.
- 2Switch to Diagram View.
- 3Connect the Star Schema:
- Drag CustomerID from Dim_Customers to CustomerID in Fact_Transactions (Establishes 1 : *).
- Drag ProductID from Dim_Products to ProductID in Fact_Transactions (Establishes 1 : *).
- 1Create a dedicated Date Table:
- In Power Pivot window, go to Design tab > Date Table > New.
- Link Date from the new date table to TransactionDate in Fact_Transactions.
Your enterprise data model is now fully established, normalized, and optimized for high-speed DAX calculations!
Interactive Quiz
Multiple Choice Questions
Question 1
In our Capstone Star Schema, why is 'Fact_Transactions' loaded with 'Only Create Connection' and added to the Data Model?
Question 2
What relationship cardinality exists between 'Dim_Products' and 'Fact_Transactions'?
Question 3
Why is generating a dedicated Calendar/Date table in Power Pivot recommended for enterprise modeling?
Question 4
How did we calculate 'GrossRevenue' during the Power Query transformation step?
Question 5
In which view of the Power Pivot window do you drag and drop lines between primary and foreign keys to create relationships?
Next Lesson
Capstone: Applying Advanced Formulas, DAX, and Pivot KPIs
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.