Reporting Database Schema

The following information describes the reporting database schema.

The reporting database schema defines the tables, the fields in each table, and the relationships between fields and tables. The reporting database schema is based on dimensional modeling. Two important concepts of dimensional modeling are facts and dimensions:

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.