Data Warehouse System Re-engineering Project: System Design Review and QA Testing.
Nielsen is the world's leading marketing and media information company.
They're passionate about measuring and analyzing how people interact with
digital platforms, traditional media and in-store environments - locally as
well as globally. Nielsen provide the most complete understanding of how
consumers get information, consume media and buy goods and services.
The Nielsen Company employs advanced data collection methodologies and
measurement science to help businesses turn new and traditional sources of
data into customer intelligence to better manage their brands, launch and
grow product portfolios, optimize their media mix and establish meaningful
The 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. Also, static reports were sent directly to clients. The static reports contained scorecards, which were created by Customer Analysts manually.
Our Customer business 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 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 manual processes
- Informatica/Microstrategy solutions were considered to be used in this project
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.
- 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.
- Components that provided a consistent security structure across the Reporting Portal.