Turning Data Into Decisions: A 360° Business Intelligence System
📌 BI Report
View the interactive Power BI dashboard:
BI Report
📌 Project Overview
AtliQ Hardware sells computers and accessories through three primary channels:
- 🏬 Retailers
- 🛒 Direct Sales
- 📦 Distributors
The company faced financial losses from a poorly planned store expansion in the USA. The expansion decision was based mainly on surveys and Excel-based analysis without a centralized analytics framework.
This exposed major challenges:
- Fragmented reporting across departments
- Heavy dependency on multiple Excel files
- Conflicting KPI definitions
- Delayed visibility into declining sales
- No structured marketing ROI tracking
- Poor forecast accuracy monitoring
Leadership required a centralized analytics solution to reduce guesswork and improve strategic decisions.
🎯 Objective
Build a scalable BI solution that:
- Eliminates manual Excel consolidation
- Standardizes KPIs across Sales, Finance, Marketing, and Supply Chain
- Provides real-time performance visibility
- Enables confident, data-backed expansion planning
🛠️ Tech Stack
- MySQL – Data extraction
- Power BI – Data modeling & visualization
- Power Query (M Language) – Data cleaning & transformations
- DAX – KPI & business logic creation
- DAX Studio – Performance optimization
- Excel – Data validation & reconciliation
⚙️ Solution Architecture
🔹 Data Layer
- Extracted structured transactional data using MySQL
- Performed full data transformation in Power Query
- Created a custom Date Table using M language
- Applied validation checks to ensure revenue and margin accuracy
🔹 Data Modeling
- Designed a scalable Snowflake schema
- Built optimized relationships between fact and dimension tables
- Ensured efficient filter propagation and performance
🔹 KPI Framework
Developed standardized business KPIs:
- Net Sales
- Revenue Growth (MoM, YTD)
- Gross Margin %
- P&L by customer, product, and region
- Marketing ROI
- Forecast Accuracy %
- Net Error & Risk segmentation
Optimized DAX measures using DAX Studio.
🎨 Advanced Power BI Features
- Data cleaning & transformation with Power Query
- Snowflake data modeling
- Advanced DAX measures & KPI standardization
- Dynamic titles responding to slicers
- Conditional formatting with icons and colors
- KPI indicators for quick performance checks
- Drill-through for deep-dive analysis
- Custom tooltips for contextual insights
- Bookmarks & navigation buttons for smooth report flow
- Interactive executive dashboard layout
- Date table creation using M language
- Data validation techniques to ensure consistency
📊 Dashboards Delivered
🔹 Sales View
Revenue decline and growth driver analysis by region, channel, and customer.
🔹 Finance View
Automated P&L reporting with profitability insights.
🔹 Marketing View
Spend vs revenue contribution and ROI tracking.
🔹 Supply Chain View
Forecast accuracy tracking and inventory risk profiling.
🔹 Executive View
High-level KPI cockpit for leadership decision-making.
📈 Results & Business Outcomes
- Eliminated fragmented Excel-based reporting across departments
- Reduced manual consolidation effort and reporting delays
- Standardized KPI definitions, improving cross-functional alignment
- Improved visibility into revenue decline drivers
- Supported data-backed expansion and investment decisions
- Improved marketing budget allocation through ROI visibility
- Enhanced forecast accuracy tracking, reducing inventory risk
- Faster executive decision cycles
- Reduced reliance on guesswork
- Improved profitability visibility
- Stronger strategic planning capability
- Established a scalable analytics foundation