Sales Data Warehouse & ETL Pipeline (SSIS Project)

Project Overview

Designed and implemented a Sales Data Warehouse solution using ETL processes to integrate and transform data from OLTP systems into an analytical OLAP structure.

The project focuses on building a structured data model to support business intelligence and reporting.

Problem Statement

Raw sales data stored in transactional systems (OLTP) is not optimized for analysis or reporting.

This project solves the problem by transforming and loading data into a data warehouse for efficient querying and decision-making.

Architecture Overview

Source System: OLTP Database (Transactional Data)

ETL Layer: SSIS Packages

Target System: OLAP/Data Warehouse (SALES Database)

Technologies Used

SSIS (SQL Server Integration Services)

SQL Server

Data Warehouse Modeling (Star Schema)

ETL Pipelines

Git (Version Control)

⚙ Key Components

OLTP.conmgr → Connection to source transactional database

OLAP.conmgr → Connection to data warehouse

Package1.dtsx / Package2.dtsx → ETL workflows

Dim_product.dtsx → Product Dimension loading

fact_sales.dtsx → Sales Fact table loading

SALES.database → Data warehouse structure

ETL Process

Extract data from OLTP system

Clean and transform data (handling nulls, formatting, etc.)

Load dimension tables (e.g., Product)

Load fact table (Sales transactions)

Maintain relationships between dimensions and facts

Data Model

Fact Table: fact_sales (contains sales transactions)

Dimension Table: Dim_product (product details)

Implemented using Star Schema Design for optimized querying and reporting.

Key Features

Automated ETL pipelines using SSIS packages

Separation between OLTP and OLAP systems

Scalable data warehouse design

Efficient handling of large sales datasets

Structured data ready for BI tools

Business Value

Enables faster and more efficient reporting

Supports data-driven decision-making

Improves data consistency and quality

Provides a single source of truth for sales data