​

📊 Analytics & KPI Module (an_*)

​

1. Module Purpose

• Menyimpan definisi KPI (kode, nama, formula).
• Menyimpan nilai KPI per periode.

​

2. Tables & Structure

​

3. Key Fields & Relationships

​

3.1 an_kpi

• Fields: company_id, code, name, description, formula, unit.

​

3.2 an_kpi_val

• Fields: kpi_id, company_id, period_start, period_end, value, dimension1, dimension2.
• FK: kpi_id → an_kpi.id.

​

4. Business Flows

​

4.1 ETL/Job per Periode

1. ETL job (di luar DB) menghitung KPI (mis. total sales, gross margin, dsb.).
2. Hasilnya dimasukkan ke an_kpi_val dengan kpi_id yang sesuai.
3. Dashboard membaca dari an_kpi + an_kpi_val.

​

5. Example Reports (SQL)

​

5.1 KPI Terbaru per Kode

SELECT
  k.code,
  k.name,
  v.period_start,
  v.period_end,
  v.value
FROM an_kpi k
JOIN an_kpi_val v ON v.kpi_id = k.id
WHERE k.company_id = :company_id
  AND v.period_end = (
    SELECT MAX(v2.period_end)
    FROM an_kpi_val v2
    WHERE v2.kpi_id = k.id
  );

​

5.2 KPI dengan Dimensi (Contoh per Branch)

SELECT
  k.code,
  k.name,
  v.dimension1 AS branch_code,
  v.value
FROM an_kpi k
JOIN an_kpi_val v ON v.kpi_id = k.id
WHERE k.company_id = :company_id
  AND k.code = 'TOTAL_SALES'
  AND v.period_start = :period_start
  AND v.period_end   = :period_end;