# BI / KPI / Charts engine

xly does **not** ship a generic OLAP / cube engine (no Mondrian, no
Saiku, no MDX; the bundled `olap4j-1.2.0.jar` has zero Java imports
and is dead weight — see [tech-stack.md](tech-stack.md#3-cache-in-memory)
note on OLAP4J).

What xly *does* ship is a **homebrewed metadata-driven dashboard +
KPI layer** built on the same primitives as everything else in the
framework: rows in `gds*` metadata tables pointing at `Sp_*` stored
procedures, rendered through generic Java services. This page maps it
end-to-end.

## Three pieces

| Piece | Surface | Backing tables | Service |
|---|---|---|---|
| **Charts** (cards, bar/line/pie/gauge widgets, dashboards) | `/indexPage/commonChar` modules in FROUNT; 图表配置 admin in BACK | `gdsconfigcharmaster` (3,006 rows), `gdsconfigcharslave` (1,951 rows) | `CharServiceImpl` (2,219 lines, one of the heaviest in xlyBusinessService) |
| **KPI** (per-employee / per-module performance scoring) | KPI screens in BACK / FROUNT (e.g., `行为KPI项目设置`, `5.经营KPI分析`, `异常清除KPI任务表`) | `kpimaster` (124,524 rows), `kpidetail` (1,308), `kpimodule` (44), `kpimoduleuser`, `kpimoduleuserday`, `kpislavel` | `KpiServiceImpl` (833 lines, in `xlyBusinessService/.../KPIService/`) + `BusinessModelKpiServiceImpl` (901 lines) + `FlushModleKpiThread` (background refresh) |
| **Pre-baked aggregation procs** | Invoked by chart rows | n/a | 20 `Sp_chart_*` procedures + 2 `Sp_KPI_*` procedures + `spKPImodule` |

## Charts: how a dashboard renders

A chart in xly is a row in `gdsconfigcharmaster` with this shape (key columns):

| Column | Role |
|---|---|
| `sId` | Chart id |
| `sParentId` | The owning module (`gdsmodule.sId`) |
| `sChinese` / `sEnglish` / `sBig5` | Chart title |
| `sCharType` | Widget type — see distribution below |
| `sProcedureName` | The stored procedure that produces the chart's data |
| `sProcedureParam` | JSON parameter spec for the proc |
| `iWidth` | Layout span (24-column grid) |

`sCharType` distribution in the live dev DB:

| `sCharType` | Count | What it renders |
|---|---:|---|
| `Div` | 1558 | Container / layout-only block |
| `sLabel` | 1143 | Single-value text card (e.g., "今日销售额: ¥X") |
| `Progress` | 137 | Progress bar |
| `sPie` | 52 | Pie chart |
| `commonList` | 45 | Embedded data grid (re-uses the universal grid) |
| `sColumnarGroup` | 30 | Grouped bar chart |
| `sColumnar` | 28 | Single-series bar chart |
| `sBrokenLine` | 5 | Line chart |
| `sBar` | 3 | Horizontal bar chart |
| `ColorBlock` | 3 | Color-coded heatmap-style block |
| `sGauge` | 2 | Gauge / dial widget |

Slave rows (`gdsconfigcharslave`) carry the per-series / per-column
breakout for charts that need multiple data series.

The runtime path:

```
SPA opens a /indexPage/commonChar?sModelsId=<dashboard module id>
    │
    ▼
GET /xlyEntry/business/getModelBysId/<id>
    → returns the dashboard's metadata composite (formData includes
      the chart layout from gdsconfigcharmaster + slave rows)
    │
    ▼
For each chart:
  POST /xlyEntry/business/getXxx (CharServiceImpl method) with the
       chart's sProcedureName + sProcedureParam
    → CharServiceImpl invokes the named Sp_chart_* proc through
      generic procedure dispatch
    │
    ▼
SPA renders each card using ECharts (frontend), one card per chart row
```

## The 20 `Sp_chart_*` procs

| Proc | What it computes |
|---|---|
| `Sp_chart_home_11`, `Sp_chart_home_13` | Homepage dashboard cards |
| `Sp_chart_TodayOrder`, `Sp_chart_TodayOrder_hm`, `Sp_chart_ThisMonthQty`, `Sp_chart_MonthOrder`, `Sp_chart_MonthTeamQty` | Today / current-month order counts and team output |
| `Sp_chart_TodayProfit`, `Sp_chart_MonthProfit`, `Sp_chart_TodayReceivables`, `Sp_chart_TodayReceive`, `Sp_chart_expenses` | Financial: profit, receivables, expense rollups |
| `Sp_chart_EquipmentLoad`, `Sp_chart_EquipmentLoad1`, `Sp_chart_EquipmentLod1`, `Sp_chart_EquipmentLast`, `Sp_chart_sMachine_speed`, `Sp_chart_Bottleneck` | Shop-floor: equipment utilisation, last-running state, current bottleneck |
| `Sp_chart_OrderProcess`, `Sp_chart_WorkOrderProcess` | Order / work-order progress timelines |

Each follows the standard `(IN sLoginId, IN sBrId, IN sSuId, ...) →
result-set` shape so generic dispatch can call it. The
[multi-tenant scoping](../../concepts/multi-tenancy.md) flows through
naturally — every chart is automatically tenant-filtered.

## Pre-built dashboard modules in this dev DB

`/indexPage/commonChar` is the shared route. The dev DB has 6 modules
mapped to it:

| Module sId | 中文 |
|---|---|
| `19211681019715464089035510` | 销售图表分析 (Sales chart analysis) |
| `19211681019715481435115760` | 财务图表分析 (Financial chart analysis) |
| `19211681019715481435298200` | 生产图表分析 (Production chart analysis) |
| `19211681019715708435449190` | 销售大数据分析 ("sales big-data analysis") |
| `19211681019715708471874620` | 采购大数据分析 (Procurement big-data analysis) |
| `101251240115015889205266000` | 采购价格分析查询 (Procurement price analysis) |

All six are metadata-driven via `gdsconfigcharmaster` rows — no Java
code change needed to add or modify them.

## KPI subsystem

> **Disambiguation.** The FROUNT home page also shows a card titled
> "**KPI监控**" (KPI Monitor) — that is **not** the same thing as
> what's documented here. The home-page card is an open-task counter
> served by `BusinessModelCenterController.getModelCenter`; it reads
> `gdsmodule.bUnTask` / `sUnType`, has no targets / scoring / charts,
> and is misleadingly named. See
> [The KPI Work Center in runtime.md](runtime.md#the-kpi-work-center-front-end-home-dashboard).
> The `kpi*` table family below is the *actual* per-employee
> performance-scoring layer.

`kpi*` is a **per-employee performance-scoring** layer separate from
the chart rendering. The shape:

| Table | Role | Live row count |
|---|---|---:|
| `kpimaster` | Per-employee per-period KPI rollup. The volume table — one row per employee per scored event. | 124,524 |
| `kpidetail` | Detail rows backing each `kpimaster` aggregate. | 1,308 |
| `kpimodule` | KPI definitions — which modules / metrics participate. | 44 |
| `kpimoduleuser` | Per-user KPI assignments. | 0 (unassigned in dev DB) |
| `kpimoduleuserday` | Daily KPI bucket per user. | 1 |
| `kpislavel` | KPI level / band definitions. | 0 |

Java side:

- `KpiServiceImpl.java` (833 lines, in `xlyBusinessService/.../KPIService/`) — the read/write API for KPI events.
- `BusinessModelKpiServiceImpl.java` (901 lines) — the computation layer that turns business-event data into KPI rows.
- `FlushModleKpiThread.java` — background recompute worker.
- `KpimasterCloum.java` enum (xlyPersist) — column-name constants.

Procs:

- `Sp_KPI_DetailByEmployee` — per-employee detail report.
- `Sp_KPI_SumByEmployee` — per-employee summary rollup.
- `spKPImodule` — recompute KPI for a module.

There is also a sizable customer override under
`script/客户/`: e.g., `script/标版/30100101/spKPImodule.sql` and
several `Sp_SalesOrder_Kpi*` procs (matches the
[per-customer SQL override channel](../../slices/05-customer-sql-override.md)
— customers who want different KPI rules ship their own proc).

## Drawbacks of the homebrewed approach

The metadata + per-chart-proc design is consistent with xly's data-
driven thesis, and it avoids carrying a heavy OLAP engine. The costs:

1. **Every new chart needs a SQL author.** "PM adds a metadata row"
   is true *after* an engineer has written the matching `Sp_chart_*`
   proc. There is no aggregation builder, no field-picker, no auto-
   generated query — every metric is a hand-coded stored procedure
   the engineering team has to write, review, and maintain. The
   20-proc catalogue and 11 chart types are the **whole** set of
   shapes the system can render today.
2. **Charts run heavy SQL on the OLTP DB.** No warehouse, no
   pre-aggregation, no incremental rollup. A "today's profit"
   chart is a SELECT against the live transactional schema.
   Heavy customers will see chart loads contend with order-entry
   load on the same MySQL instance. Caching helps, but only on hit;
   the first load after metadata change pays full cost.
3. **No semantic consistency between charts.** Each `Sp_chart_*`
   proc decides for itself how to compute "monthly profit", "today's
   sales", etc. Two charts purporting to show the same metric can
   silently disagree because they're separate proc bodies. A real
   semantic layer would prevent that; the homebrewed model can't.
4. **No drill-down, no slice-and-dice.** Each chart is a frozen
   query shape. Users can't pivot on different dimensions or drill
   from a summary card into the underlying transactions without an
   engineer authoring a separate proc for each path.
5. **Customer-divergent KPI logic.** Customers under
   `script/客户/` ship their own `spKPImodule` and
   `Sp_SalesOrder_Kpi*` overrides — different KPI math per
   customer, in code that lives only on that customer's DB. This
   makes "what does this KPI mean" depend on which schema the
   reader is connected to.

The simpler design is fine for "show me the same 20 cards xly has
always shown". It is not fine if the goal is ad-hoc analytics or
self-service reporting — those would require a separate semantic /
warehouse layer that xly does not have.

## What this is *not*

- **Not a self-service BI tool.** Customers cannot point at any
  table and build a chart through drag-and-drop; new charts require
  a SQL stored procedure and an admin who knows how to register the
  metadata row.
- **Not real-time analytics infrastructure.** Charts run their procs
  on cache miss against the OLTP MySQL schema. There is no separate
  warehouse, no incremental aggregation pipeline, no streaming layer.
  Heavy charts on large customers will execute heavy SQL on the live DB.
- **Not column-store / OLAP-engine-backed.** The `olap4j` jars in
  `xlyPersist/build.gradle` have zero Java imports — they're
  classpath-only dead weight. xly uses MySQL's regular row-store via
  MyBatis through generic procedure dispatch.