Sales_DWH_SQL_Project

🚀 Project Requirements

1-Building the Data Warehouse (Data Engineering)

🧭 Objective

Develop a modern data warehouse using SQL Server to consolidate sales data, enabling analytical reporting and informed decision-making.

---

📋 Specifications

• Data Sources:
  Import data from two source systems (ERP and CRM) provided as CSV files.

• Data Quality:
  Cleanse and resolve data quality issues prior to analysis.

• Integration:
  Combine both sources into a single, user-friendly data model designed for analytical queries.

• Scope:
  Focus on the latest dataset only historization of data is not required.

• Documentation:
  Provide clear documentation of the data model to support both business stakeholders and analysts.

Draw DWH Architecture

---

Silver Layer || Build Integration Model

Gold Layer || Draw Logical Model

🔍 Phase 2: Exploratory Data Analysis (EDA)

🎯 Goal: “Understand the Data”

• Perform basic SQL queries to get familiar with datasets.
• Conduct data profiling to detect anomalies or missing values.
• Use simple aggregations and subqueries for initial summaries.

---

📊 Phase 3: Advanced Data Analytics

🎯 Goal: “Answer Business Questions”

• Write complex SQL queries for multi-source insights.

• Use window functions for advanced aggregations.

• Implement CTEs (Common Table Expressions) for readable logic.

• Build reports to support decision-making.

  📊 Phase 4: Data Visualization (Power BI)

🎯 Goal: Create Dashboards Using the Final SQL Views

After completing Exploratory Data Analysis and Advanced Analytics, the final step is to connect Power BI to the SQL Server data warehouse and build business dashboards.

Steps:

• Connect Power BI to SQL Server

  • Use Get Data → SQL Server
  • Enter server and database name
  • Load only the final SQL views created in previous phases

• Load the Analytical Views

  • Import or DirectQuery the views (facts, dimensions, analytical outputs)
  • Validate relationships and model structure

• Create Dashboards

  • Build visuals using the cleaned, modeled views
  • Include KPIs, charts, filters, and summaries based on business needs

💡 Tools & Technologies

• Draw DIO
• SQL Server
• SQL Server Integration Services (SSIS)
• Power BI / Excel (for reporting)
• CSV Data Files

---

📁 Project Structure

---

• Data Engineering

---

1-Create_database 2-Bronze Layer 3-Silver Layer 4-Gold Layer

---

• EDA

---

• AQL Analysis

---

• PBI Dashboard

---