Data warehouse and consolidation report

Data Warehousing / BI Case Studies

Case Study : Business Reporting & Customer Information Datamart Architecture Setup & Roll-out for a global technology company

Environment: Oracle8i, Business Objects 5.1, Web Intelligence, Informatica.

To scope, define & design a structured & systemized architecture for capturing & reporting Business & Financial performance metrics for APJCC

  • Evaluate current processes & methodology for capturing & reporting Business & financial performance metrics
  • Scope framework for systemized data capture & reporting of Business & financial performance metrics
  • Key Areas identified: (1) ABC (2) OPEX (3) Revenue (4) Profitability Analysis
  • Define processes, systems & tools for systemized data capture & reporting of Business & financial performance metrics
  • Design systems & tools for systemized data capture & reporting of Business & financial performance metrics

To scope, define & design framework for building a Customer Information Datamart Architecture for APJCC

  • Evaluate current processes & systems for capturing all customer contact information for APJCC
  • Scope framework for systemized data capture of customer intelligence data
  • Scope for pilot: KL Hub (for actual data capture, UAT & roll-out), but framework must incorporate APJCC perspective
  • Define data definitions, DWH structure, data capture processes, business logics & system rules, applications & tools for Datamart.
  • Design & implement

Case Study : Data warehouse & Consolidation Report for Asia Pacific Contact Center

Environment: Oracle8i, Business Objects 5.1, Web Intelligence, Informatica.

The scope of work included:

  • Understanding and analyzing User Requirements
  • Incorporate design enhancement to the Data warehouse
  • Enhance and modify the ETL
  • Design and development of the following reports:
    • DNA Report
    • Agent Scorecard
    • Compensation Report - Revenue
    • Compensation Report - KPI
    • Reseller Report (RSI)
    • Campaign Report
    • Japan Hub Report
    • Taiwan CC Report
    • Australian Report
    • Operation KPI
    • Agent Direct Report
    • IDA Reports (Government Accounts)

Case Study : Global Planning Data Automation

Environment: Oracle8i, Business Objects 5.1, Web Intelligence, Informatica.

Global Planning Data Automation stores the data pertaining to demand planner and sales & order system and is a part of Motorola's global Data warehouse Oneview. Oneview is a single window Data Warehouse for all the business functions of Motorola across all the regions. Data from various sources like Oracle ERP, Flat files etc. has been loaded to DW. Informatica is being used as an ETL tool & OLAP application has been developed using Business Object 5i.

The roles and responsibilities of Optimum consultants includes:

  • Interaction with Users.
  • Requirement Analysis
  • Design of ETL process in Informatica
  • Development of ETL process in Informatica
  • Design of Business Objects Universes
  • Design Review
  • Managing security and Sharing
  • Testing

Case Study : COTS-Anti Money Laundering

Environment: Oracle9i, Business Objects 5.1, Infoview, Broad cast agent, TWS & Perl Scripts.

COTS-Anti-Money Laundering system allows suspicious customer activities to be detected throughout customer relationship and the data from COTS, FLEXCUBE systems in Oracle database and populate into AML database by using stored procedures, Db link in TWS scheduler jobs and generate the analysis reports for Low risk, High risk, Watchlist, Account setup, Sign-Off and Audit reports in various levels i.e. as per Country, Branch, Cost centre and Customer by using Business Object Universe, Business Object report module & reports scheduling by using BCA (Broad case agent - Business Object tool) and send to InfoView users(View BO reports in INFOVIEW) and PERL Script used to format Business objects reports in text format (ROD format i.e. including banner).

The roles and responsibilities of Optimum consultants were:

  • Analysis and design of COTS-Anti-Money Laundering system.
  • Design of database, working tables, Summary tables, reference tables & history tables using SQL * Loader and PL/SQL programming ((stored procedures, functions, packages & triggers) with Shell scripts in UNIX environment.
  • Design of Oracle forms for setup Parameters & Accounts.
  • Design of Universe by using Business Object Designer like reusable to all countries and generates the reports by using Business Object report module and scheduled by Broad cast agent (This tool used to schedule, refresh & send business documents to InfoView users) and PERL Script to format Business Objects reports in ROD text format.
  • Involved in SIT, UAT & Production support.
  • Implementation of project & Cutover and preparation of Survival, Operational and Technical documents including User training.

Case Study : COTS-MIS

Environment: Oracle8i, Business Objects 5.1, Broad cast agent, SQL * Loader Perl Scripts.

COTS-MIS system (Bank) aims to capture the data from divergent systems (TRES & COSMOS) in ASCII format and data into Oracle database by using SQL*Loader & PL/SQL Programming in TWS scheduler jobs and generate the analysis reports for financial, sales and profits in various levels i.e. as per period, region, country, currency, warehouse, branch and customer in business objects & reports scheduling by using BCA (Broad case agent - Business Object tool) and PERL Script used to format Business objects reports in text format(ROD format i.e. including banner).

The roles and responsibilities of Optimum consultants were:

  • Analysis & design of COTS-MIS system.
  • Design of database, working tables, Summary tables, reference tables & history tables using SQL * Loader and PL/SQL programming ((stored procedures, functions, packages & triggers) with Shell scripts in UNIX environment. (All ETL Process used SQL*Loader & PL/SQL Programming)
  • Design of Universe by using Business Object Designer like reusable to all countries and generates the reports by using Business Object report module and scheduled by Broad cast agent (This tool schedule & refresh the Business Object document by Business Objects reports) and PERL Script to format Business Objects reports in ROD text format.
  • Involved in SIT, UAT & Production support.
  • Implementation of project & Cutover and preparation of Survival, Operational and Technical documents including User training.

Case Study : Management Information System for Trade Finance

Environment: Oracle8i, Cognos, Oracle Discoverer and Oracle Express.

The group is responsible to develop and commission a management information system to provide a globalize view of the Trade finance (TF) related activities of Deutsche Bank. Currently the project TFMIS is catering to the MIS needs of TF for projecting Revenues & costs involved in TF transactions resulting in getting customer and product profitability, Outstanding amount, turnover etc. We have developed a DataMart for storing the currently and historical transactions.

Optimum consultants have been involved in STAR schema design for storing the huge volume of data, approximately 500 GB in oracle database using oracle tools. Additionally to perform the extraction and transformation few modules have been built which continuously poll the TF production systems and gather / filter / process data for MIS purposes.

Discoverer & Express (Oracle tools) are used to perform BI analysis. Currently this application is being ported onto Web using Cognos BI tools, which is expected to cater to the needs of larger user base.

Please be aware that the free essay that you were just reading was not written by us. This essay, and all of the others available to view on the website, were provided to us by students in exchange for services that we offer. This relationship helps our students to get an even better deal while also contributing to the biggest free essay resource in the UK!