Basic terminologies:
- Dimensions
- Facts
Table and table naming conventions
- initial 3 letter prefix: DWH, ODS etc.
- Content: e.g. PRD, SLS
- Dimension or Fact: D, F
- Granularity : Department, day/week etc.
- Suffix as use case: Lookup vs Base Vs Aggregate vs Matrix
- Naming: data region + dimension_or_fact + content + granularity + usage
Column naming:
- Naming: Attribute or measure + data type
- Attribute: Price etc.
- Data type: KEY, ID, DESC
- Maintenance columns: insert timestamp, update, close, close_dt
- Business calendar: 4-4-5 or 4-5-4 or 5-4-4 and 13 period calendar.
- Matrices are designated with MTX suffix.
Dimension Tables:
- Examples of naming:
- DWH_D_TIM_YR_LU : data warehouse, dimension table, timestamp, year, Lookup
- CURR_TIM : curr timestamp
-
Some examples
- Organization Dimension:
- company > chain > area > region > district > location
- dimension tables: center, market area, market statistics area, converted location, location_day_attr, week attribute, history attribute, commercial_invoice attri, omni channel attri, location outage, location group matrix etc.
- Product dimension:
- division > group > department > class > subclass
- other: style, color, size, item,
- Supplier and Partner:
- Supplier, supplier trait, trait matrix, partner matrix
- Customer:
- customer, customer rfmp, household
- Organization Dimension: