System Architecture

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