Nikhil Gupta03 / 08 · Business Analytics
ICE Entertainment
Enterprise data warehouse · Academic project
Reconciled disconnected sales and ERP systems into a dimensional data warehouse for a global retailer (500 stores, 10 countries), loading 50,000+ records through a 5-layer Pentaho ETL pipeline.
50,000+ records · 500 stores · 10 countries
MySQLPentahoSQL01 — The brief
Problem & outcome.
The problem
ICE Entertainment's sales and ERP systems sat in separate databases with no shared structure, so leadership couldn't get a straight answer on revenue, cost, or margin across 500 stores in 10 countries without manual spreadsheet reconciliation.
The outcome
Designed and built a star-schema data warehouse that unified 50,000+ records from both systems into consistent fact and dimension tables, enabling direct SQL and BI queries for revenue, cost, and margin questions across store, product, and time.
ICE ran two disconnected systems (Informix for sales, Oracle for cost and inventory) and neither could tell leadership how profitable the business really was, by product, customer, or market. I designed and built a MySQL star-schema warehouse, driven by a five-layer Pentaho ETL pipeline, that unifies both systems into one source of truth so management can query revenue, cost, and margin directly.
02 — How it works
The model.
Five-layer ETL architecture: staging → integration → warehouse → presentation → access.
03 — Schema
Star schema tables.
| Table | Rows | Contents |
|---|---|---|
| fact_sales | 50,000 | One row per order line item: qty, revenue, cost, and margin in USD. |
| dim_date | 10,592 | Calendar and fiscal attributes; fiscal year runs September to August. |
| dim_customer | 5,000 | Customer demographics, location, and market segment with SCD Type 2 history. |
| dim_product | 1,000 | Product catalogue with a 3-level hierarchy: Category → Type → Product. |
| dim_store | 500 | Store locations with a 6-level geographic hierarchy. |
| dim_currency | 175 | Monthly exchange rates for multi-currency to USD conversion. |
| dim_subscription | 3,000 | Customer subscription packages and pricing. |
04 — Highlights
At a glance.
- 500 stores, 10 countries
- 50,000+ records loaded
- 5-layer ETL pipeline
- Star-schema model
- 5 management questions answered
- Revenue, cost & margin by market