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