
I’ve had these notes laying around for a while, so, I thought I consolidate them here. So, here are few guidelines to ensure the quality of your dimension table structures.
Dimension Table Effective Practices
- The table naming convention should identify it as a dimension table. For example:
- Suffix Pattern:
- <<TableName>>_Dim
- <<TableName>>_D
- Prefix Pattern:
- Dim_<TableName>>
- D_<TableName>>
- Suffix Pattern:
- Have Primary Key (PK) assigned on table surrogate Key
- Audit fields – Type 1 dimensions should:
- Have a Created Date timestamp – When the record was initially created
- have a Last Update Timestamp – When was the record last updated
- Job Flow: Do not place the dimension processing in the fact jobs.
- Every Dimension should have a Zero (0), Unknown, row
- Fields should be ‘NOT NULL’ replacing nulls with a zero (0) numeric and integer type fields or space ( ‘ ‘ ) for Character type files.
- Keep dimension processing outside of the fact jobs
Related References
- Data Modeling – What is Data Modeling?
- Where do data models fit in the Software Development Life Cycle (SDLC) Process?
- What is a Common Data Model (CDM)?
- Data Modeling – The Value of Data Indicator Flags
- Data Modeling – What is Data Modeling?
- Data Modeling – Fact Table Effective Practices
- SQL Server – Basic SQL Server Object Naming Convention Guidance
- What are the Factor Affecting the Selection of Data Warehouse Naming Convention?
- Database Table Field Ordering Effective Practices
1 Comment