Build a reliable SQL-backed BI layer

Business purpose

The retail project already demonstrates Python-based diagnostics. This extension answers the BI question: how should the same evidence be exposed to a reporting user through a reusable analytical layer?

The design separates raw/clean facts, dimensions, KPI views, and validation queries so Power BI or another BI client can consume stable outputs.

Model structure

Fact: monthly retail sales grain.

Dimensions: product/category, region, customer segment, acquisition channel, time.

KPI layer: revenue, units, ASP, profit, margin, discount.

Diagnostic layer: H1/H2 comparison, dimensional drivers, anomaly queue.

Example SQL

WITH period AS (
  SELECT
    CASE WHEN EXTRACT(MONTH FROM sale_date) <= 6 THEN 'H1' ELSE 'H2' END AS half,
    revenue,
    units,
    profit,
    discount_sum,
    row_count
  FROM retail_kpi_monthly
)
SELECT
  half,
  SUM(revenue) AS revenue,
  SUM(units) AS units,
  SUM(revenue) / NULLIF(SUM(units),0) AS asp,
  SUM(profit) AS profit,
  SUM(profit) / NULLIF(SUM(revenue),0) AS margin,
  SUM(discount_sum) / NULLIF(SUM(row_count),0) AS avg_discount
FROM period
GROUP BY half
ORDER BY half;

BI page design