This project demonstrates the design and implementation of an ETL (Extract, Transform, Load) pipeline using SQL Server Integration Services (SSIS). The ETL process extracts transactional data from the Northwind database, applies data transformations, and loads it into a dimensional data warehouse optimized for business intelligence and analytical reporting.
The project follows dimensional modeling principles by creating dimension tables and a centralized fact table to support efficient querying and reporting.
- Build a dimensional data warehouse from the Northwind database.
- Design and implement ETL workflows using SSIS.
- Extract data from the source database.
- Perform data cleansing and transformations.
- Load data into dimension and fact tables.
- Maintain referential integrity through foreign key relationships.
- SQL Server
- SQL Server Integration Services (SSIS)
- SQL
- Visual Studio
- Data Warehousing
- ETL
- Dimensional Modeling
ssis-etl-northwind-data-warehouse
│
├── README.md
├── Northwind_Create_Tables.sql
├── Northwind_ETL.sql
├── Northwind_ETL_Project.slnx
├── ETL_Project_Report.pdf
└── Screenshots
- DimCustomers
- DimEmployees
- DimProducts
- DimShippers
- FactOrders
The fact table stores transactional measures while dimension tables provide descriptive attributes for analysis.
The ETL process consists of the following stages:
- Extract data from the Northwind source database.
- Apply transformations using SSIS components.
- Clean and standardize data using Derived Column transformations.
- Perform Lookup transformations to establish relationships between dimension and fact tables.
- Load processed data into the SQL Server data warehouse.
- OLE DB Source
- Derived Column
- Lookup Transformation
- OLE DB Destination
- Data Flow Task
- Control Flow
- End-to-end ETL pipeline implementation
- SQL-based dimensional data warehouse
- Fact and dimension table creation
- Data cleansing and transformation
- Lookup-based key mapping
- Efficient loading into SQL Server
- Structured data model for business intelligence
- Successfully extracted data from the Northwind database.
- Loaded cleaned and transformed data into the data warehouse.
- Established relationships between dimension and fact tables.
- Created a structured warehouse suitable for analytical reporting.
- Incremental ETL loading
- SSIS package logging
- Error handling and notifications
- SQL Server Agent scheduling
- Power BI integration for dashboard reporting
- Restore the Northwind database in SQL Server.
- Execute the SQL scripts to create the data warehouse schema.
- Open the SSIS solution in Visual Studio.
- Configure SQL Server connection managers.
- Execute the SSIS packages.
- Verify data loading using SQL queries.
- SQL scripts for warehouse creation
- SSIS project files
- Project documentation
- ETL workflow screenshots
Divyanshi Sharma
MBA (Artificial Intelligence & Data Science)
Graphic Era Deemed to be University
This repository contains an academic ETL project developed as part of the MBA (Artificial Intelligence & Data Science) curriculum. It demonstrates practical implementation of data warehousing concepts using SQL Server Integration Services (SSIS) and SQL Server.