This project is an end-to-end Business Intelligence solution built on the Microsoft Adventure Works 2022 transactional database.
The objective of the project is to transform raw enterprise transactional data into analytical datasets and interactive dashboards focused on:
- Product profitability analysis
- Sales channel performance
- Customer segmentation (B2B & B2C)
- RFM behavioral modeling
- Pareto revenue concentration analysis
- Firmographic and demographic profiling
- Time-series revenue analysis
The analytical layer was developed using advanced T-SQL in Microsoft SQL Server, where multiple SQL views were designed to support scalable business reporting and multidimensional analysis.
The solution was later integrated into Power BI to build a multi-page interactive dashboard environment optimized for business exploration, KPI tracking, customer analytics, and profitability evaluation.
This project emphasizes:
- Advanced SQL analytical modeling
- Window functions (
LAG,NTILE,ROW_NUMBER) - Historical cost matching using
OUTER APPLY - Customer-level aggregation strategies
- RFM segmentation models
- Pareto analysis
- Time-series calculations (MoM & YoY)
- Dimensional analytical design for Power BI
- Interactive dashboard development
- Microsoft SQL Server
- T-SQL
- Power BI
- DAX
This project uses the AdventureWorks2022 sample database from Microsoft, a simulated enterprise transactional dataset designed for business intelligence, sales analytics, and enterprise reporting scenarios.
The analytical solution integrates transactional, product, customer, geographic, demographic, and firmographic information to support multidimensional business analysis across both B2B and B2C environments.
The project combines data from multiple business areas, including:
- Sales
- Customers
- Products
- Production Costs
- Stores
- Customer Demographics
- Customer Firmographics
- Geographic Information
Sales.SalesOrderHeaderSales.SalesOrderDetailSales.Customer
Production.ProductProduction.ProductSubcategoryProduction.ProductCategoryProduction.ProductCostHistory
Person.PersonSales.StoreSales.vPersonDemographicsSales.vStoreWithDemographicsSales.vStoreWithAddresses
Person.AddressPerson.BusinessEntityAddressPerson.vStateProvinceCountryRegion
The analytical model was designed to support seven major business intelligence areas:
- Product Profitability & Cost Analysis
- Sales Channel Analysis (Online vs Offline)
- Customer B2B Segmentation & RFM
- Customer B2C Segmentation & RFM
- Customer B2B Firmographics & Value Profitability
- Customer B2C Demographics & Value Profitability
- Time Series Analysis
The project includes both transactional-level analysis and aggregated analytical datasets optimized for dashboard performance and interactive business exploration.
The analytical layer was developed through multiple SQL views designed specifically for Power BI integration.
These views support:
- Product-level profitability analysis
- Historical production cost matching
- Customer segmentation models
- Revenue concentration analysis
- Geographic and demographic enrichment
- Customer profitability evaluation
- Time-series growth analysis
The project follows an analytical modeling approach focused on dimensional consistency, aggregation control, and reporting scalability.
- Aggregated analytical datasets
- Historical cost matching using
OUTER APPLY - Customer-level enrichment
- Time-series normalization
- RFM segmentation models
- Pareto revenue distribution analysis
- Address prioritization using
ROW_NUMBER() - Window-function-based analytical calculations
- Separation of B2B and B2C analytical populations
- Avoidance of duplicated transactional granularity
CTEsLAG()NTILE()ROW_NUMBER()OUTER APPLYCASECOALESCENULLIFDATEFROMPARTS- Windowed aggregations with
SUM() OVER
- Raw transactional extraction from AdventureWorks2022
- SQL-based analytical transformations
- Historical cost matching and customer enrichment
- Aggregated analytical dataset creation
- Power BI data modeling and DAX measures
- Dashboard development and business analysis
- Common Table Expressions (CTEs)
- Window Functions
- OUTER APPLY
- Historical Cost Matching
- RFM Segmentation Logic
- Pareto Analysis
- Time-Series Aggregation
- Conditional Classification with CASE
- Customer-Level Data Enrichment
- Duplicate Prevention Strategies
- Data Normalization
- Geographic & Demographic Enrichment
The analytical datasets developed in SQL Server were integrated into Power BI to create an interactive business intelligence solution focused on profitability analysis, customer segmentation, channel performance, and time-series analytics.
The Power BI layer was designed to support:
- Dynamic filtering
- Drill-down exploration
- KPI monitoring
- Cross-visual interaction
- Geographic visualization
- Comparative analytical reporting
- Aggregated SQL analytical views
- Customer-level and product-level modeling
- Historical cost integration
- Independent B2B and B2C segmentation models
- Pareto analytical structures
- Monthly time-series normalization
- Prevention of many-to-many relationship conflicts
Main DAX implementations include:
- Revenue calculations
- Profitability metrics
- Average Order Value (AOV)
- MoM Growth %
- YoY Growth %
- Dynamic KPI calculations
- Filter-responsive aggregations
Analyzes product-level profitability by combining revenue, historical costs, margins, pricing behavior, and product hierarchy analysis.
Compares Online and Offline customer purchasing behavior through revenue, orders, customer participation, and Average Order Value analysis.
Implements a B2B RFM segmentation model combined with Pareto analysis to evaluate customer value concentration and purchasing behavior.
Implements a B2C RFM segmentation model focused on customer loyalty, behavioral segmentation, and revenue concentration analysis.
Combines firmographic attributes, geographic information, business size, and transactional performance for B2B customer analysis.
Combines demographic attributes, customer profitability metrics, and geographic analysis for B2C customer profiling.
Analyzes monthly revenue evolution, MoM growth, YoY growth, and long-term business performance trends.
Product Profitability & Cost Analysis
Sales Channel Analysis (Online vs Offline)
Customer B2B Segmentation & RFM
Customer B2C Segmentation & RFM
Customer B2B Firmographics & Value Profitability
Customer B2C Demographics & Value Profitability
Detailed SQL implementations and technical breakdowns:
- Product Profitability & Cost Analysis
- Sales Channel Analysis
- B2B & B2C RFM Models
- Pareto Revenue Analysis
- Firmographic & Demographic Modeling
- Time Series Analysis
Detailed dashboard explanations, DAX logic, and data modeling structure.
Extended business insights, analytical interpretations, and strategic findings are documented separately in the project case study.
This project demonstrates the complete development of an end-to-end business intelligence solution using SQL Server and Power BI over the AdventureWorks2022 transactional database.
The analytical workflow combines:
Advanced T-SQL data modeling Customer behavioral segmentation Historical profitability analysis Time-series revenue analytics Geographic and demographic enrichment Interactive business intelligence dashboards
The SQL layer was designed to transform transactional data into analytical datasets optimized for reporting and decision-making, while Power BI was used to create interactive dashboards focused on operational visibility and business performance evaluation.
The project covers multiple analytical domains, including:
Product profitability Sales channel performance B2B and B2C customer segmentation RFM behavioral modeling Pareto revenue concentration analysis Demographic and firmographic profiling Revenue trend and seasonality analysis
Several advanced SQL concepts were applied throughout the implementation, including:
CTE-based modular transformations Window functions Historical cost resolution Customer-level aggregation Pareto cumulative analysis Time-series calculations Data enrichment and normalization
From a business perspective, the solution enables:
Identification of high-value customers and products Detection of revenue concentration patterns Analysis of customer purchasing behavior Evaluation of geographic and demographic business performance Monitoring of profitability and growth trends Support for strategic and operational decision-making
Overall, the project reflects a complete analytical pipeline that integrates data engineering, business intelligence, and data visualization practices into a unified enterprise analytics solution.
Feel free to connect with me on the following platforms:
Check out my personal projects!