Data Warehousing Development and Support
Industry
Automotive
Customer
Our customer is a world leader in the production of tires. The company operates more than 100 plants in 29 countries.
Project Overview
The project is designed to create and support a global enterprise data warehouse (GEDW) of our client. GEDW is a major part of a large–scale project intended for the creation of a work environment that enhances the external focus of the organization and captures business growth by simplifying processes and structures.
The database warehouse is a part of the software that manages business processes based on the following modern principles: Standardized Processes, Simplified Structures, External Focus, Harmonized IT Platform, and Centralize Opportunities. This repository is supposed to accumulate data from all offices of our client. These data should facilitate business decisions as well as support any trends analysis.
The BI team is responsible for:
- Design, implementation and support the Common Data Model of our customer
- Design, implementation and support of Cognos–based date presentation (design of cubes, development of reports).
The ETL subteam is responsible for:
- Design and implementation of normalized data storage on Teradata platform
- Design and implementation of flows to load data from SAP (ABAP programs, HP–UX shell scripting, Informatica, Autosys, Teradata scripting)
- Support of data loading processes (via Lotus Notes DB).
Project Execution
Working as a part of the BI team of our customer, IBA experts take part in the design, implementation, and testing of the data warehouse.
Environment and Technologies
Programming languages: ABAP 4, UNIX HP shell scripting, Teradata BTEQ scripting, Teradata SQL, Informatica SQL.
Application servers: Informatica 8.6, HP–UX 11, IBM Cognos 8 BI.
Databases: Teradata Client Tools, Version 12.0.0 (Administrator, SQL assistant, BTEQ programming).
External systems interaction: SAP Library SAP ERP Central Component, Release 6.0 (ABAP–4 programming, SAP GUI); Unicenter Autosys Jobs Management, Version: 4.5.1.
Project management tools: Lotus Notes Databases 7.
Project documentation: MS Office, UML ERWIN diagrams.
IDE: Informatica Power Center, Release 8.6.0; Cognos Business Intelligence (Analysis Studio, Report Studio).
IBA in cooperation with the BI team developed Cognos reports and jointly with the ETL subteam created and has been supporting flows to extract, process, and qualify SAP data to be later loaded to Teradata DB.
On the SAP servers, IBA created and scheduled a list of ABAP programs to extract (periodically) data from SAP DB and store these on UNIX server in the file system. In Informatica PowerCenter 8.6.0 Designer, IBA developed a list of data mapping, while in Informatica PowerCenter 8.6.0 Workflow Manager, a list of workflows in line with the tasks and patterns.
On UNIX server, IBA developed a list of shell/BTEQ scripts corresponding to the tasks and patterns. On Autosys, IBA developed, installed and scheduled a list of ETL flows corresponding to the tasks and patterns. In Teradata DB, IBA developed a list of stage tables.
All programs, tables, and mappings were first created in the Development Environment and eventually migrated to the Test Environment. Lotus Notes was used to control the migration process. The new system was tested in the Test Environment and then migrated to production. The ETL team reports and fixes all errors coming from production on a daily basis.
Project Results
- Patterns and Templates were developed for every part of the common ETL/BI process.
- ETL processes were developed, implemented, tested, and introduced to production.
- Cognos reports were developed and implemented.
- The first stage of the software development to streamline business processes of our client was successfully finished.
- IBA programmers were recommended for the next stage of the project to streamline all business processes of our client.
