systems built: data warehouse & all analytical systems for a catalog direct-marketing firm
This client had an old 3rd-party order-entry and warehouse fulfillment system, which they would not abandon for economic and political reasons. The client asked for MIS reporting capabilities, lacking in their old system.
- Creates a data mirror from 3rd party order-entry system output and imports via ETL to avoid the substantial hardware investment, required to support the workload of live reporting.
- Generates an operations report in ~4 minutes - after prompting the user only for date boundaries, passed to stored procedures, which go through 2.5 million records 24 times in 2 minutes. Analysts used to collect component reports for three days - to generate the equivalent operations report.
- Wrote stored procedures to generate demand by order-entry system, cost per order, backorders, open orders ( e.g. hold-to-arrive open orders, customer pickup open orders, re-ship open orders and open orders by age) and shipment reports in Seagate Crystal Reports.
- Built a marketing system, utilizing stored procedures to feed ~25 reports for mailed-catalogs forecasting analysis.
- Reports demand during user-defined periods by drop, drop split, drop & segment etc.
- Labor-tracking system that integrates a 4th party time-keeping system with the 3rd party order-entry system to track picker and packer productivity and packer-error exchange reports.
- Process customer email opt-in and opt-out demands for email promotions.
- Group email customers by various criteria, such as orders-by-quarter and orders-by-category, for selective promotions.
- Wrote all SQL Server stored procedures and ~100 DTS Packages and Jobs to facilitate the above functionality.
- Received Administrative privileges to all systems and 24x7 facility access, reflecting trust, earned!
If you need analytical reporting enhancements to existing systems - without modifying old systems, call Data-based-Systems.com