Data Warehouse System Redesign
Data Warehouse System Redesign.
Business Situation:
Our Customer supplied reports to its clients via two methods. An internet portal accessed from Customer homepage which offered dynamic reporting functionality and the ability to download some static reports. Secondly large, static reports were emailed directly to clients. The static reports contained scorecards, which were created by Customer Analysts manually.
Our Customer busiiness grew up through acquisition and had multiple back-end systems. Data was stored in a variety of databases (SQL Server, Oracle, Teradata) and reporting was performed by several tools (Crystal Reports, Brio, MicroStrategy, Java). The front-end was a custom Java application. Many of the ETL and static report production processes contained manual elements.
The Customer Internet helped mobile businesses respond quickly to system problems and bottlenecks and more efficiently manage the performance of legacy applications.
Key Issues :
· Data model of existing databases was used. It was required for the proposed ETL, reporting and security solution to perform modifications to the model
· The customer didn't provide well-defined business rules to automate outage processes wherever it was possible
· Informatica/Microstrategy solutions were considered to be used in this project
Result :
Our engineers participated in new system design review, implementation and QA testing of the following components:
- Migration of existing portal reporting DB to a new reporting database.
- ETL & Database processes analysis and automation.
- MicroStrategy 7i reports that support exporting of reports in both Excel & PDF format were implemented. These reports were exported to PowerPoint using MicroStrategy SDK and VB MSTR.
- Ad Hoc Data Filtering ASP application was modified to re-point to new database.
- Alert application that was re-pointed and modified appropriately to work with the new database.
- Data Feed application was re-pointed and modified appropriately to work with the new database.
- Migration to a new database the archive stored procedures and tables.
- A separate Administration tool.
- Provided a consistent security structure across the Reporting Portal.
· Data Warehouse System Redesign