Use Case - Reporting Excel Replacement
The company XXX*) creates monthly management reports, which include printed PDF-formatted reports, Excel-sheets and a structured Powerpoint presentation with summaries and charts. The reports are created from Excel-sheets and database downloads and sent via Email according to a predefined mailing list.
Two persons are charged with the creation of the reports. They extract data from three databases and receive data from four sources as Excel sheets via Email. The data from the databases are also saved in Excel sheets. The delivery of the Excel sheets is not 100% reliable. Sometimes the structure and content of the data deliveries (Excel sheets and database extracts) change without advanced notice. The structure, keys and content of the Excel-sheets are not standardized. Therefor it is necessary to perform a QA and reformatting process.
Creation of Reports
The resulting Excel-sheets are consolidated into one large master Excel-map which is then used to create the requested reports and charts. Some charts and tables are then pasted into a standardized Powerpoint-file which is presented at the monthly management meeting. Several sub-Excel-Sheets are setup and data are extracted from the master sheet. They are used for analysis, simulation and planning.
The entire process is handled by two persons, which are responsible to chase the latecomers, collect and clean the data and create the reports. From time to time they get requests for additional details and reports. They work out how to find the data and how to feed these data into the reporting process. The total cost of this "system" amounts to about 20.000 a month.
EXL-Reporting - Open Issues
There are increasingly "glitches" where the data are just not right. An investigation found that in one case the data from a previous month has been mixed with current data. Changes in the data structure lead to errors in the calculations and delays in the delivery of the result. A few managers started to maintain their private Excel-sheet from other sources. This lead always to confusions and discussing in the management meeting and the confidence in the reports became shaky. The person responsible for the reports finally gave up and gave notice to leave the company in two month.
Consequently the CEO asked for immediate action and the preparation of proposals how to overcome these problems. There were some arguments between the CIO and the Chief Controller how to proceed. Finally two alternatives were presented.
Alternative Nr 1 – BI-System
A BI-System should replace the current reporting system. The development should concentrate in a first step on the replacement of this system alone to meet tight time and budget constraints. An integrated system was proposed which accesses the databases directly and replaces also the delivered Excel-Sheets. The proposed budget was 1.6Million, it was estimated that development would take about 16months.
Alternative Nr 2 – Metasafe Solution
Phase 1: the existing interfaces are not changed: The incoming data are collected and stamped, validated with an import program and stored in the Metasafe DB using the Excel-Import-facility. The reports are (re)built using the BIRT (Business Intelligence and Reporting Tool) with erSQL-access to the database.The creation of a report (60 reports) takes about 1/2 day.
Budget and project: A proof of concept demonstrated the desired results after 4 weeks with a budget of 15000.The total budget was 120.000, development time 5 months.
Phase 2: some reports will be saved on on a server for direct / secure web-access instead of sending them per mail. A direct access to the data via a query provides a direct access for power-users.