ETL Olist

Data Warehouse (DWH) for Olist using ETL powered by SQL Server Integration Services (SSIS). ️

Facts Table: Order Fact

Measures: Price, Freight Value

Foreign Keys: Customer, Review, Seller, Date, Product

Fact History (Accumulating Snapshot Fact Table)

Key Events: Order Purchase, Order Approved, Order Delivered Carrier, Order Delivered Customer

ETL Process:

Extract:

Extracting data from the comprehensive Olist database, encompassing customer, review, seller, date, and product information.

Transform:

Transforming raw data into a structured format conducive to insightful analysis.

Addressing data quality challenges and incorporating calculated fields.

Load:

Loading transformed data into meticulously designed tables within the Data Warehouse.

SSIS Package Execution:

Implemented a recurring SSIS job to automate the ETL process on a weekly basis.

Ensures that the Data Warehouse is consistently refreshed with the latest information.

Robust monitoring and logging for swift issue resolution.

Benefits:

Centralized and streamlined data for enhanced reporting and analytics.

Granular historical tracking through accumulating snapshot fact tables.

Automation of ETL processes ensures efficiency and timeliness.

Technical Details:

Leveraged SSIS to seamlessly orchestrate ETL workflows.

Adhered to the Kimball methodology for designing the Data Warehouse schema.

Applied industry best practices for performance optimization and error handling.

Weekly SSIS Job:

Established a weekly SSIS job to execute the ETL package, ensuring the Data Warehouse is always up-to-date. Let's continue to transform data into actionable insights!