
A full data warehouse implementation using the AdventureWorks dataset, built to simulate the kind of multi-source, multi-region data environments common in enterprise settings. The goal was to consolidate data from four different database platforms into a single analytical layer that supports meaningful business reporting.
Architecture
Data is sourced from four regional systems running on different databases:
- SQL Server (US region)
- MySQL (EU region)
- PostgreSQL (AU region)
- Oracle (CA region)
All four feed into a central data warehouse — AdventureWorksDW_neu — built on both MySQL (Talend target) and SQL Server (SSIS target).
ETL Pipeline
Built parallel ETL pipelines in Talend and SSIS to cover both toolsets:
- Extraction: Connected to all four source systems with error handling at each step
- Transformation: Applied business rules, data cleansing, and standardization across regions
- Loading: Optimized bulk loading with indexing strategies tuned for analytical query patterns
Data Model
Designed a star schema to support sales and purchasing analysis:
- Dimension tables: DimStore, DimCustomerPerson, DimCurrency, DimPromotion, DimSalesTerritory, and others
- Fact tables: FactInternetSales, FactStoreSales, FactPurchases
- Slowly Changing Dimensions: Type 1 implementation for handling business changes over time
- Reject tables: Capturing records that fail validation, with reconciliation reports
Business Intelligence Layer
Built a reporting layer on top of the warehouse:
- Materialized views for frequently queried patterns
- Vendor ranking by purchase volume
- Sales performance by territory, product, and channel
- Employee and vendor involvement in purchasing workflows
- Dashboard-ready output structured for Power BI consumption
What This Covers
- Multi-platform data integration across heterogeneous source systems
- ETL development in both Talend and SSIS
- Dimensional modeling with proper fact and dimension separation
- Data quality management with validation, rejection, and reconciliation
- BI query design for operational and analytical reporting