Data Warehouse Best Practice Architecture Review, Recommendation and Implementation
Transit authority gets on schedule, gains 100% ROI
Business Challenge
One of the largest mass transit authorities worldwide was looking at ways to maximize the value of their customer data and improve customer service. The customer service group, or example, only had access to online data for a period of five days. Over 90 percent of customer inquiries required much more historical data that needed to be requested from a central processing area – possibly resulting in delays in answering customer questions. Another area needing more efficient data access was security. Fraud detection is an essential element in transit card management and determining where and what kind of fraud is being committed must be made in a timely manner. Reports were being generated only after data has been selected from the primary database and downloads were passed to the security department. These downloads were then loaded into a departmental access database for report generation. If additional information was needed (historical data for example) security would need to request a new feed from the processing group. These delays can cost thousands of dollars in lost revenues.
CSI was retained by the transit authority to perform an architecture review/recommendation and develop a proof of concept Data Warehouse. CSI worked with the client to select a best of breed infrastructure including hardware platform, RDBMS, ETL tool, enterprise reporting, OLAP tools and portal.
Data Warehouse Center Solution
The project focused on three primary business areas within the transit authority: Security, OMB, and Customer Service. A proof of concept consisted of an extract transform process and bulk load of approximately 8,000,0000 daily transit card transactions into the selected RDBMS.
Based upon feature/function requirements and scalability issues defined by the transit authority, Data Warehouse Center partnered with the client to conduct a detailed investigation into each database product, ETL product, query product, and enterprise reporting product. The study yielded the top two competitors in each space, which were to be benchmarked.
- Database
Client database schemas were implemented and the two leading databases were loaded with data, indexed, and optimized. The fully loaded databases were then subjected to a series of SQL selects reflecting actual data requests from the transit authority’s business centers. The results of the loads, indexes and queries were documented and a recommendation made by . - ETL
Extract/transform scripts were written, executed and timed for the two leading products. Both CSI and the client analyzed the ease of use, functionality and performance of each vendor. Use of the metadata exchange to pass information from the data-modeling product into the ETL product was validated. The winning vendor was added to the transit authority’s infrastructure. - Hardware
CSI analyzed benchmarking data for the selected database on each hardware platform. A cost analysis was performed. - OLAP/Ad-Hoc Reporting
Based upon feature/function requirements and scalability issues two Ad-Hoc reporting vendors were evaluated. Data Warehouse Center implemented client reports utilizing each product and had the IT and user community work with each product, with their own data. The client IT/user community made the final product selection.
Upon completion of the benchmarking phase, Data Warehouse Center developed a proof of concept data mart utilizing the newly selected architecture. The mart focused on the development of selected reports that were compared to existing reports generated by transit authority legacy systems. The users were given online access via the Internet to production data during the testing phase. The final phase was development of the production warehouse.
Return on Investment
The prototype Data Warehouse Center developed provided user access to online data of at least six months in time cutting the customer service response rate from weeks to minutes. Customer satisfaction improved exponentially. The security area has also realized a tremendous benefit from this effort. The prototype warehouse put the power of the data in the hands of the decision makers, dramatically decreasing the timeframes for detection, and increasing the opportunity for preventive measures.
Offloading the intensive data manipulation and reporting efforts from the mainframe to a user-friendly star schema provided the transit authority with an ROI of over 100 percent. The result achieved was a much faster and more comprehensive access to transit authority customer data.