Модель «Звезда»:

• Таблица фактов Fact_Sales: sale_id, date_key, drug_key, pharmacy_key, customer_key, quantity (кол-во), revenue (выручка), cost (себестоимость).
• Измерения:

- Dim_Date (date_key, full_date, month, quarter, year);

- Dim_Drug (drug_key, drug_name, category, manufacturer, is_prescription);

- Dim_Pharmacy (pharmacy_key, pharmacy_name, city, region);

- Dim_Customer (customer_key, loyalty_card, age_group).

SQL-запрос для этапа ETL:

INSERT INTO dwh.Fact_Sales (date_key, drug_key, pharmacy_key, customer_key, quantity, revenue, cost)

SELECT

CAST(TO_CHAR(s.sale_date, 'YYYYMMDD') AS INT) AS date_key,

d.drug_key,

p.pharmacy_key,

COALESCE(c.customer_key, -1) AS customer_key,

s.qty AS quantity,

s.qty * s.price AS revenue,

s.qty * s.unit_cost AS cost

FROM staging.raw_sales s

JOIN dwh.Dim_Drug d ON s.drug_code = d.business_code

JOIN dwh.Dim_Pharmacy p ON s.store_id = p.store_business_id

LEFT JOIN dwh.Dim_Customer c ON s.card_num = c.loyalty_card

WHERE s.qty > 0 AND s.price > 0;