The aggregator table is refreshed daily, and allows quick access to important metrics that are otherwise slow to calculate.
| Field name | Description | mSupply Use | Reference link | Type | Indexed |
|---|---|---|---|---|---|
| ID | UUID of the Aggregator Not use is UI | Long integer | |||
| storeID | Store identifier | used for dashboard reports | https://docs.msupply.org.nz/web_interface:dashboard_setup?s[]=aggregator#available_dashboard_reports | text | |
| itemID | Item identifier | text | |||
| yearMonth | Month and year | text | |||
| fullDate | Full transaction/record date | date | |||
| dataElement | Name of the metric | text | |||
| value | Actual measured value | real | |||
| temp1 | Temporary / auxiliary numeric field | real | |||
| temp2 | Temporary / auxiliary numeric field | real | |||
| temp3 | Temporary / auxiliary numeric field | real |
This table holds aggregated values with different dimensions. For example, some values are aggregated per store, others per store and item, and others per store, item, and yearmonth.
| Value | StoreID | ItemID | datetime | Comment |
|---|---|---|---|---|
| monthlyConsumption | ✓ | ✓ | yearmonth | Sums consumption for each month there is consumption Consumption in this case = (transaction.type = 'ci' and transaction.status in ('cn', 'fn')) |
| AMC | ✓ | ✓ | - | Sums all consumption (using dataElement = 'monthlyConsumption') and divides by number of months with consumption |
| stockConsumption | ✓ | ✓ | fulldate | For every day there is a customer invoice, aggregate consumption |
| stockMovement | ✓ | ✓ | fulldate | For every day there is stock movement, sums up supply and subtracts sum of consumption |
| stockHistory | ✓ | ✓ | fulldate | Reverse ledger (using dataElement = 'stockMovement'), records stock level at the end of each day where stock movement existed |
| monthlyDOS | ✓ | ✓ | yearmonth | Calculate number of day per month where stock was 0 |
| |