Reporting Database Schema
The following information describes the reporting database schema.
- Facts
- Measures that you want to report and perform analysis on. Facts are typically numeric values that can be aggregated. For example, work data, such as soldering temperature or the number of processors used in a board assembly line, are facts.
- Dimensions
- Attributes or descriptors that define a fact. Dimensions are the entry point to facts, and are the source of all the constraints and report criteria. For example, equipment is a dimension of work data facts because it defines the location of the Segment and Work Responses. Dimension information can be a total count of used processor parts in one particular line.
Database Schemas
The following schema names are used in the reporting database to organize tables and simplify permission granting:
- dbo
- Default schema for all of the dimension and fact tables.
- admin
- Administration-related tables, such as system parameters.
- audit
- Auditing tables.
- etl
- ETL (Extract, Transform, and Load) staging tables.
Naming Conventions
The following naming conventions are used in the reporting database schema:
| Naming Convention | Description |
|---|---|
| Fact_ | Prefix for fact tables. |
| Dim_ | Prefix for dimension tables. |
| Bridge_ | Prefix for bridge tables. Bridge tables are used to maintain the data relation. For example, Bridge_EquipmentHierarchy allows you to easily query for all subsidiaries of an equipment resource. |
| SK_xxx_ID | The format of all surrogate keys in the dimension tables. |
| xxx_Key | The format of all foreign key fields. For example, the work request location column is named "Location_Key." |
| xxx_ID | The format of all the source primary keys. For example, the work request ID is named "WorkRequestID." |
The reporting database allows you to generate reports on the following types of data:
- Response Work Data
- Equipment Model
- Material Model
- Personnel Model
- Workflow Model
- Auditing
- Regulatory Compliance
Notes on Dimension Tables
- The date and time dimension tables are pre-populated when the reporting database is created. These dimension tables make it easy to generate reports such as "Report on the average temperature measured in the morning compared to the afternoon" and "Report the average temperature measured on each weekday".
- The dimension tables all have an IsDeleted column. Any dimension record imported into the reporting database is kept for data integrity purposes. These records are marked as deleted by setting the flag in the IsDeleted column.
- Each dimension table uses its own surrogate key as the primary key instead of the natural primary key. The surrogate key is a number that uniquely identifies each row in the dimension table, and is used to join the dimension table into the fact table.