Enterprise data warehouse
ICE Entertainment
Unified disconnected sales and ERP systems into a star-schema warehouse designed to answer board-level questions on revenue, cost, and margin.
50,000+ records · 500 stores · 10 countries
Executive summary
ICE Entertainment 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. “The objective is to provide a global truth,” as the brief put it. 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 instead of reconciling spreadsheets.
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.
Architecture
Five-layer ETL architecture: staging → integration → warehouse → presentation → access.
Schema at a glance
| Table | Rows | Description |
|---|---|---|
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. |