Data Models and Tables

SQL Reporting solutions store data according to a dimensional data model where transaction fields are categorized as either facts (metrics) or dimensions (context).

  • Related facts are organized together into SQL tables, prefixed by 'Facts'.

  • Related dimension fields are organized together into SQL tables, prefixed by 'Dim'.

This method aims for a balance between ease of use, performance and extensibility.

Data Models for the SQL Reporting Solutions

Fact Tables

These tables contain metrics for the entities measured by the system. Additionally, they contain links to any related Dimension table entries. These tables have the prefix 'Facts'.

Dimension Tables

These tables contain the context for the entities measured by the system. Entries here are linked to items in the Fact tables in order to provide the related context for the measured entity. These tables have the prefix 'Dim'.

Meta Tables

These tables contain information about the ETL process. They would typically not be used directly in business level reports but may be used by database administrators to help manage the data.

Lookup Tables

These tables contain lookup information, typically used for converting codes and IDs into readable descriptions. There will typically be a related CSV file attached to each table and updates to these tables would be performed via the CSV file. These table have the prefix 'LK'.

Internal Tables

These are temporary tables used for the ETL process.

Common Tables for SQL Reporting Solutions

Provide feedback on this article