Overview
A 12-store retail chain had been running Power BI reports on top of their raw transactional database for two years. The reports were slow — some taking over 3 minutes to load — and frequently showed conflicting numbers depending on which report you looked at. The root cause was no data model: Power BI was querying the OLTP tables directly, with complex DAX measures compensating for the lack of a proper structure underneath.
This engagement designed and built a dimensional data warehouse in SQL Server from the ground up. The work covered requirements gathering, source system analysis, schema design, T-SQL implementation of all ETL logic, and documentation of every table, column, and business rule. The Power BI layer was rebuilt on top of the new model and every report that previously took 3+ minutes loaded in under 10 seconds.
Key Deliverables
- Fact table design covering sales transactions, inventory movements, and customer returns with appropriate granularity for each
- Conformed dimension tables for Date, Customer, Product, Store, and Promotion — shared across all fact tables
- SCD Type 2 on the Customer dimension to track address changes, loyalty tier upgrades, and segment reclassifications over time
- Bridge table for the Product–Promotion many-to-many relationship
- Full T-SQL ETL scripts with incremental load logic for all dimensions and facts
- Data dictionary document covering every table, column, business rule, and grain statement
Challenges & Solutions
The source OLTP system had no concept of a customer — orders were linked to email addresses, not customer IDs. Building the Customer dimension required a deduplication step that identified the same individual across multiple email variants (e.g., name@gmail.com and Name@Gmail.com) using a normalisation function before assigning a surrogate key. This resolved a long-standing reporting problem where the same customer appeared as dozens of separate records.
The promotion dimension required a bridge table because products could participate in multiple simultaneous promotions, and promotions could apply to many products. The bridge pattern with a weighting factor allowed DAX measures to correctly attribute revenue to promotions without double-counting — something that was impossible with the original flat structure.