Business-intelligence reporting dataset

Wadi Market's data team has loaded its sales into an analytics mart: a Kimball-style star schema with one fact table of order lines (fact_sales), a fact table of web and app visits (fact_web_sessions), monthly marketing spend (fact_marketing_spend) and four dimensions (dim_date, dim_customer, dim_product, dim_channel). The commercial team is about to replace a dozen spreadsheets with a single dashboard, and you have been asked to build the dataset behind it.

A star schema makes reporting easy, but only if you respect its rules. Facts link to dimensions through surrogate keys (customer_key, product_key), not the business ids. dim_customer is a type 2 slowly changing dimension: when a customer moves city, a new version of their row is created with its own key, and valid_from/valid_to (inclusive; '9999-12-31' for the current version) say when each version was true. Each sale already points at the version that was valid on the sale date, so "region" in a sales report means the region at the time of sale unless someone explicitly asks for the current one. Guest checkouts use the special member customer_key = 0, whose region is NULL. The company's fiscal year runs from April to March and is named after the year it ends in, and the weekend is Friday and Saturday.

You will first prove the mart is trustworthy, then build the KPI layers (regional, monthly by channel, fiscal-year trend, funnel, marketing return), publish a view for the dashboard tool and finish with the channel scorecard for the commercial director. All data is fictional.