Problem Statement:
The azad client, a major utility provider, had to make large purchases in support of daily marketing operations on a regular basis. To analyze the purchase need, the process included manual data entry to transfer information between systems. Ultimately the data was compiled in Excel through many manual processes for presentation to management and archived for evidentiary reasons. The amount of manual data processing required, greatly hindered the ability to further analyze the data, and provide further process improvement for unknowns such as system outages.
azad Approach & Solution:
A team of several azad consultants and Client IT staff performed detailed data analysis of several data systems.
The system design resulted in the following features:
- Strict auditing requirements.
- Complete traceability of all data.
- 15 minute latency for automated data feeds.
- On demand aggregations of large data set spanning multiple years.
- Repeatable point in time reporting.
- Advanced analysis, trending and ad-hoc querying.
- Maintain the flexibility of ad-hoc reporting currently performed in Excel.
The azad consultant provided a comparison of BI tools to the development team and IT management. The comparison involved details on: development time per tool, server configuration, server deployment, usability, and performance. The results of the analysis provided the decision of using tools available from two vendors currently part of the existing client infrastructure. The fixed reports were created using Microsoft SQL Server Reporting services and ad-hoc analysis was provided using Oracle Hyperion Interactive Report.
The complete system solution used SQL Server to store a database exceeding 100GB loaded using commercially available ETL tools to meet the low latency requirements. The use of star schema database design techniques allowed for advanced fixed reports, history of data changes, and real- time ad-hoc analysis. These design techniques also allow for excellent scalability and extensibility.
Tangible Outcome:
The resulting system eliminated the client reliance upon inefficient system for this critical decision making process. Freeing hours of staff time and allowing in depth analysis to reduce real-time purchasing costs. Reports which used to take several hours to generate each time are now generated on demand with the ability to view point in time history. Additional benefits provided through the custom dashboard screens created by the azad consultant include: the ability to query, summarize and drill down on data to answer new question, in addition to graphs for trending. The result was a reliable method to present information to providing accurate and repeatable decisions.