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

  • 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

  • Customer Order:

    Order strategy, payment, order source, status, do consolidated status, do status, do user matrix

  • Promotion: Promotion, Prom event, prom head, coupon
  • Price zone: Price zone and price zone location matrix
  • Employee: Employee, Segment, Segment derived, Location labor
  • Payroll:

    Payroll code, Employee location day matrix, emp position

  • Miscellaneous: Country dimension, Discount type, Item location status code, reason, receipt type, retail type, state transfer type, tender, currency code, exchange rate.

Fact tables:

  1. Allocation tables: Base, current, item week aggregate, location day aggregate, location week aggregate
  1. Inventory: Inventory: item location day base, curr table, location day aggregate, … , area item week aggregate

Inv in transit: item location day base, transit curr table,

Inv markdown: markdown style location week aggregate

Inv adjustment, inv on order, inv receipt, return to vendor, transfer, transfer reserved, unavailable inventory

  1. Price: Price , Price Current , Ecommerce Price item day, price status
  1. Sales:

    Line discount, Line item, tender, aggregates on item, locaiton, week/day, subclass, location week,

  1. Customer: Credit card apps status location week, customer capture stat, creadit card transaction location day

Customer order:

Loss prevention:

Supplier cost:

Planning facts:

Retail demand forecasting:

Traffic:

Market basket analysis:

Store plan:

Payroll:

Order Commit: