pivot_forecast/readme.md

pre-requisites

• setup postgres db with a username and password and database

setup

• setup_sql/01_schema.sql setup the application tables
• create a table of data to forecast
• setup_sql/02_target_info.sql to populate the meta table (edit table name)
• fill out the target_meta table
• setup_sql/03_build_maste_tables.sql to create master data tables from forecast data
• ./routes/baseline/generate_route_sql.sh to create the baseline sql used by the /baseline route

schema	tname	cname	opos	func	fkey	fcol	dtype	appcol	pretty
fc	live	fb_cst_loc	91	cost		fb_cst_loc	numeric
fc	live	ship_cust	36	scust	scust	ship_cust	text
fc	live	rdate	98	rdate	rdate	drange	date
fc	live	geo	42	scust		geo	text	customer
fc	live	part	54	item	item	part	text	item
fc	live	odate	96	odate	odate	drange	date	order_date
fc	live	sdate	100	sdate	sdate	sdate	date	ship_date
fc	live	oseas	97	odate		ssyr	integer
fc	live	calc_status	94	order_status	order_status	calc_status	text	order_status
fc	live	rseas	99	rdate		ssyr	integer
fc	live	sseas	101	sdate		ssyr	integer
								version
								iter
								logid

• func:
  • a foreign key and table is setup (except date types)
  • baseline function does a group-by to compact data for any func/appcol and value types are aggregated
• fkey:
  • primary key of assoicated data and foreign key basis
  • for date types no foreign key is setup, but dynamic table joins use the fkey field to join on
• fcol: associated field from the master data table if it is different (oseas would refer to ssyr in fc.perd)
• pretty: display column name (user friendly)
• appcol: flag columns that will be needed by the application
  • order_date
  • ship_date
  • customer
  • item
  • order_stats
  • units
  • cost
  • value
  • version (added if missing)
  • iter (added if missing)
  • logid (added if missing)

issues

• use sql stored in table instead of pulling from file
• include building all the route sql as setup step
• baseline request json is not depostiing correctly into the sql
• what if you create a func table but some of the values are null? (orders without quotes)
• what if you want certain things like invoice numbers included in the baseline, is a func table necesary? no, just leave fkey null and no table will be setup
• baseline needs to populate all columns in sequential order like the scale route
• what if there is more than one table in fc.target_meta
• validate the ability to join to associated date tables, for example order season that needs incremented
• there is not currently any initial grouping to limit excess data from all the document# scenarios
• how to handle a target value adjustment, which currency is it in?