Audit Dimensions are special type of dimensions used primarily for trace back of data. As the name implies, this dimension holds data that users can use to identify the exact load in which erroneous data has been inserted in a data mart. It was one of the first Data Quality and Governance methodologies adopted in large scale Enterprise Data Warehouses, which is part of standard design practice nowadays.
From a data model perspective, an audit dimension is just another dimension table in the schema. Role played by audit dimension is slightly different when compared to other dimensions. Audit dimensions are not updated as part of master data, instead they are the last dimension tables to be updated during ETL process. ETL jobs are designed to update the target fact table and subsequently update the audit dimension with meta data. This action of updating audit dimension happens every time the ETL job is executed. This provides a detailed log of data loads into the data mart. A typical audit dimension contains the following meta-data.
- Job Execution Date & Time
- Records from source
- Records inserted
- Records updated
- Source System
- Records flagged for missing master data
- Filter criteria
Master data i.e. Dimension tables in a data warehouse does not require audit criteria to be captured. Changes to master data is tracked using a different concept called Slowly Changing Dimensions.
In SAP BW, 0REQUID is a dimension object that performs role of Audit Dimension