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