Aggregator table
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 |
Available dataElement values
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 |
| |