✈️ Airline Loyalty Program Data Warehouse Project

📌 Project Overview

The Airline Loyalty Program is a customer rewards initiative designed to enhance passenger engagement and retention. This project builds a data warehouse and semantic model to analyze customer behavior, flight activity, and loyalty program performance. By integrating data from multiple sources and providing analytical dashboards, it enables business users to track key KPIs such as enrollment trends, flight patterns, and member loyalty history.

---

🎯 Objectives

• Build a scalable data warehouse for the airline’s loyalty program.
• Integrate data from multiple sources using SQL Server Integration Services (SSIS).
• Develop a semantic model with SQL Server Analysis Services (SSAS).
• Create insightful Power BI dashboards for business analysis.
• Enable data-driven decision-making on customer loyalty and retention.

---

🛠️ Tools & Technologies

• SQL Server (SSMS) → Data storage & management
• SSIS → Data extraction, transformation, and loading (ETL)
• SSAS → Semantic modeling and OLAP cube creation
• Power BI → Visualization and reporting

---

📊 Data Warehouse Design

• Fact Tables:

  • FlightsActFact → Flight activity metrics
  • LoyaltyHistoryFact → Member loyalty history

• Dimension Tables:

  • DateDim → Time-based analysis
  • EnrollInfoDim → Customer enrollment details
  • LocationDim → Departure & arrival locations
  • PersonalInfoDim → Passenger personal attributes
  • GenderDim → Gender classification

Conceptual Model

Logical Model

---

📈 Power BI Dashboards

The Power BI report provides insights such as:

• Enrollment trends over time
• Loyalty member distribution
• Flight activity analysis

Power BI Dashboard Screenshots

Dashboard Home Page	Data Overview	Filters Pane
Page Navigation	Customers Analysis	Points Analysis
Emrollment Analysis

📐 DAX Measures

Some of the key DAX measures implemented in the semantic layer include:

-- Total Flights
Total Flights = COUNTROWS(FlightsActFact)

-- Total Members
Total Members = COUNTROWS(EnrollInfoDim)

-- Average Flights per Member
Avg Flights per Member = DIVIDE([Total Flights], [Total Members], 0)

-- Loyalty Points Redemption Rate
Redemption Rate = DIVIDE([Total Redemptions], [Total Earned Points], 0)

-- Member Duration (Years)
Member Duration = DATEDIFF(EnrollInfoDim[EnrollmentDate], TODAY(), YEAR)

🙏 Acknowledgment

Thank you for taking the time to review this project. I truly appreciate your interest and support.