Challenge Overview
Ilium is a direct to consumer seller of various items. One set of items can be seen at the GreatCleaners site (http://greatcleaners.com). Processing of sales by the GreatCleaners includes sending of daily sales data to invoicing and inventory system (SAP).
This process is already implemented and based on hand-coded data warehouse. Sales data is originally stored in OLTP databases on production server. Asynchronously but fairly close to real time OLTP tables are replicated to the reporting server and then ELT processes are used to load data from reporting server to the data warehouse. The daily sales data is extracted from data warehouse into text file. This text file is moved to an FTP directory where electronic data interchange systems access it and pass to SAP. On the SAP side a text file is used to relieve inventory for the sold items and create invoices for recording the sales.
Data warehouse is also used as convenient data source for various reports.
This existing application is being upgraded in the following manner:
1. Each set of products has its own branded site. One such site being developed now is the Trojan Vibrations and it will be incorporated so that orders shipped through the Trojan site are also included in the export file that is sent to SAP.
2. Cash reconciliation. There are downstream projects that involve balancing order-to-cash processes on the SAP side. As such, data warehouse will be the one place to go to identify key attributes in the order life cycle. Data in warehouse should allow to track when an order was created, authorized, shipped, captured, sent to SAP, the product order number, and bank deposit date. Currently only part of this data is present in data warehouse.
3. The ETL processes will be more robust. The current ETL processes don't correctly handle replication outage periods between production and reporting servers. It means that orders can be missed and not loaded into data warehouse. New application fixes this problem and also sends email alerts to confirm the jobs ran and email notifications with summaries of what was processed.
See detailed requirements here
Final Submission Guidelines
- For each member, the final submission should be uploaded to theOnline Review Tool.
- Submissions must include all deliverables as specified above.
- All questions must be asked in the contest forum