🧩 Problem Statement

The business was facing a challenge in identifying which service lines were truly profitable after factoring in all cost structures. Without a centralized data model, finance and operations teams had to manually compare revenue reports with cost breakdowns across services, leading to inconsistency, inefficiency, and poor decision-making.

The objective was to deliver a data-driven Power BI dashboard that enables senior leadership to quickly analyze:

• Revenue vs. Cost across service lines
• Profitability trends over time
• Monthly comparisons & variance analysis
• Insights by region and customer type

🔄 Data Pipeline

1. Excel – Raw Operational Exports

• Service-wise revenue data was exported monthly from the accounting tool into Excel files.
• Cost data (operational, labor, fixed overheads) came from finance in another set of Excel workbooks.
• These sheets had inconsistencies like missing values, multiple date formats, and nested headers.

2. SQL – Data Consolidation and Transformation

• The Excel files were ingested into a SQL Server staging database.
• Using SQL (via a 1,300+ line script with Common Table Expressions - CTEs), I:
  • Cleaned column names and removed duplicates
  • Joined revenue and cost tables using service IDs
  • Created calculated fields like Gross Margin, Profitability %, Monthly Variance, etc.
  • Created views to feed directly into Power BI via an ODBC connection

❗ For this portfolio, dummy data was used, but the SQL step is included to represent the complete pipeline.

3. Power BI – Data Modeling and Dashboard