The aggregator table is refreshed daily, and allows quick access to important metrics that are otherwise slow to calculate.
| field | type | description |
|---|---|---|
| storeID | text | dimension |
| itemID | text | dimension |
| yearMonth | text | dimension |
| fullDate | date | dimension |
| dataElement | text | name of the metric |
| value real | actual | value |
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 |
| |