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:
- Allocation tables: Base, current, item week aggregate, location day aggregate, location week aggregate
- 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
- Price: Price , Price Current , Ecommerce Price item day, price status
-
Sales:
Line discount, Line item, tender, aggregates on item, locaiton, week/day, subclass, location week,
- 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: