Database Design

The Visix Displays are driven by a series of custom metrics tables:

METRICS_BLDGS – Stores compiled metrics about each building related to space and work done during the Fiscal Year
METRICS_BLDG_COST_THISFY – Stores compiled metrics about each building’s cost during the Fiscal Year and Past Month
METRICS_CREW_BACKLOG – Stores Counts of work orders in a state of backlog at monthly increments
METRICS_CREW_CLOSED_BY_MONTH – Stores Counts of work orders that were closed each month by crew
METRICS_CREW_OPENED_BY_MONTH – Stores Counts of work orders that were opened each month by crew

The METRICS_ tables are populated via a series of stored procedures that connect to STORPRD or external APIs:
MAX_TIME – is used to determine the most recent time_recorded for data in the metrics tables
METRICS_COLLECT_BLDG– Collects compiled metrics about each building related to space and work done during the Fiscal Year
METRICS_COLLECT_BLDG_COST_FY– Collects compiled metrics about each building‘s cost during the Fiscal Year and Past Month
METRICS_COLLECT_CREW_BACKLOG– Collects Counts of work orders in a state of backlog at monthly increments
METRICS_COLLECT_CREW_CLOSED– Collects Counts of work orders that were closed each month by crew
METRICS_COLLECT_CREW_OPENED – Collects Counts of work orders that were opened each month by crew

The stored procedures are run periodically to give a near real-time view of the data in the system.  The scheduled jobs that run are:

UPDATE_BLDG_METRICS – Calls METRICS_COLLECT_BLDG once every 24 hours (during the overnight hours)
UPDATE_BLDG_COSTS – Calls METRICS_COLLECT_BLDG_COST_FY once every 24 hours (during the overnight hours)
UPDATE_CREW_BACKLOG_METRICS – Calls METRICS_COLLECT_CREW_BACKLOG once every 24 hours (during the overnight hours)
UPDATE_CREW_CLOSED_METRICS – Calls METRICS_COLLECT_CREW_CLOSED once every 24 hours (during the overnight hours)
UPDATE_CREW_OPENED_METRICS – Calls METRICS_COLLECT_CREW_OPENED once every 24 hours( during the overnight hours)