Audit of architecture and performance of financial data warehouse for the Ministry of Finance, USA

The USA Ministry of Finance have entrusted DigitalXpert with works on auditing one of their projects related to financial data analysis. Within the framework of the project, it was necessary to conduct audit of the system operating several terabytes of data and possessing a developed architecture of ETL processes, data cleaning, data warehouse, and BI tools. The project was characterized by considerable complexity of the environment, because the system was developed by a number of different companies, there have been many people involved, and the system became poorly documented and specified.

Requirements

A modern data warehouse is a complex system that unites business processes, multiple technologies, and specific issues of data analysis. The customer's system was developed with Oracle Data Warehouse technologies. Current ETL processes (data loading and transforming) were supported by Oracle Warehouse Builder. The key problem of the existing system was data quality. An additional problem was low productivity of the existing system.

The project scope was to analyze the existing solutions and make suggestions on the system modernization. It needed to be done in very short timeframes and without relevant information about the working system (absence of documentation or original developers, the customer not having detailed knowledge of how the system works).

Suggested solution

The company's employees have conducted a series of interviews, performance metrics, an investigation of the customer's business processes, data quality analysis, and suggested a new architecture for the system. This architecture was based on classic standards and approved technologies. As a result of the investigation, a 500-page document was built; it contained the report and suggestions, and made up for the missing documentation, while the suggestions were specified in a most thorough way. The work was performed in close collaboration with the customer's representatives who were actually included in the team right from the start of project works.

For testing the solutions offered, DigitalXpert has conducted a series of experiments that confirmed the righteousness of the chosen technologies. Some experiments took into account the usage of vector-parallel calculations and systems for massive processing of data.

Result

As a result of the investigations and suggestions report delivered to the customer, the common system modernization strategy has been formed, plus business and economical criteria for further project management; besides, conclusions have been made about the system's bottlenecks, about means for productivity increase, and data quality enhancement.