Guident Case Studies - Business Intelligence by uoi11893


									                                             IT Solutions With Bottom-Line Impact

Guident Case Studies - Business Intelligence
  - Center of Excellence – Financial Reporting
  - EPA Administrative Offices – Data Mart

Bottom-Line Business Solutions
EPA Administrative Offices Data Mart

   Case Study - Background
   Challenges
   Goals
   Project Parameters
   OLTP to Data Mart
   Data Quality
   Results
   Lessons Learned

                              May 19, 2006   2
Case Study - Background

   Manages $1B+ in purchases and contracts per year.
   2 software applications residing in 16 separate regional offices.
    A total of 32 independent source systems.
   Mandatory quarterly statistics, annual and Congressional
   Each system produced its own reports.
   IT Group within EPA Acquisitions Division manages the
    applications and producing reports.

                                 May 19, 2006                   3
BI Maturity at Project Start

                      May 19, 2006   4
Challenges of Environment

 Management had no direct access to reports.

 IT team under staffed and unable to reduce backlog of report

 Highly visible reports for FOIA and Congress.

 Unable to create a single Acquisition report which encompassed
  all work and costs.

 Poor data quality and timeliness.

 Heavy reliance on “human ETL”.

                               May 19, 2006                 5
Project Goals

 Build a “Self service “ data mart. Get data into the hands of
  users. Reduce backlog of report requests to IT.

 “One stop shopping” - Build a secure, user-friendly, and robust
  reporting environment with conformed data from both systems.

 Improve reporting timeliness and accuracy. Refresh data from all
  sites every 24 hours.

 Build a scalable technical architecture to support goals using
  existing EPA software and hardware.

                               May 19, 2006                   6
EPA Required Parameters

 Use EPA Agency software tools
   • Oracle 9i,Oracle Designer 9.02,Business Objects 6.5, Bobj
     Designer, Web intelligence 6.5, BCA ,Data Integrator,
     Application Foundation 6.5, TOAD 8.6

 Refresh data every 24 hours. Conform data from all regional
  systems to create a single “Acquisitions” reporting and analysis

 Star schema data mart which can share conformed dimensions
  with other Agency data marts/systems.

 Resolve poor data quality issues.

                              May 19, 2006                  7
High Level View of OLTP Systems

   Purchasing System
Details         Purchase Orders            Vendors              Purchase Requests

                                  Obligations                      Details
  Divisions                                        Teams

 Contracting System
          Solicitations and Contracts                           Amendments

                    Assignments                    Vendors

   Workorders             Modifications                  Obligations

                                          May 19, 2006                              8
Building the Data Mart


                                                                Corporate Reports
Region                ODS
 OLTP    Daily ETL

                                                    Data Mart
                                        Daily ETL
 OLTP                SPEDI
         Daily ETL

                                                                   Ad Hoc Query,
                                                                    and Analysis

                                    May 19, 2006                     9
Configuration Architecture

                        ODS   DEV              QA   PROD

Data Integrator (ETL)

  Web Intelligence


                                May 19, 2006               10
Development Process

Requirements Analysis   Data Modeling               ETL Design

 Universe Design        Data Extraction         Store Data       Reporting


                                 May 19, 2006                     11
Data Quality

    May 19, 2006   12
Data Quality - Example

Budget by State
  ID - $8M
  IA - $10M
  IN - $12M
  IL - $7M
  KA - $2M
  KS - $20M
  Kansas - $6M
  KY - $17M
  LA - $40M

                    May 19, 2006   13
Data Quality – Assessment Process


                                                           Data Quality
 Source        ETL                 DQ Raw

                                                    DQ Reveal Engine               DQ Results


             Rules             DQ
          Information        Metadata

                                                                          BO Universe

                                                       BO Reports

                                            May 19, 2006                                    14
Data Quality – Accessing DQ Reports

                    May 19, 2006      15
Data Quality – Assessment Summary

                   May 19, 2006     16
Data Quality – Assessment Details

                     May 19, 2006   17
End Results

 Built data mart prototype in 6 weeks. Final data mart with 645+
  elements and conformed data from both systems completed in 12

 All reports from previous systems replaced by Bobj Reports.
  Some report time frames reduced from 20 minutes to less 3

 Developed “smart” objects. Eliminated hard coding.

 Users can access data directly and drill down to lowest
  granularity level.

                               May 19, 2006                 18
BI Maturity at Project End

                     May 19, 2006   19
Lessons Learned

 It’s all about the data!

 BI data modeling is not equivalent to data warehouse modeling.
    Data modeling phase is compressed
    Knowledge of BI reporting functionality essential

 Meta Data management is key

 Accurate reporting from Data Marts requires pristine data.
  OLTP data systems often require data assessment and cleansing.

 Creative uses for Universe accelerate analysis phase.

                              May 19, 2006                 20
Center Of Excellence – Financial Reporting
   Case Study - Background
   Business Challenge
   Goal
   Implementation
   Why Crystal Reports and Universe
   Lessons Learned
   Q&A

                              May 19, 2006   21
Case Study

   Large Financial Institution
   Business units managed reporting
       Essbase, Excel, MS Access, Business Objects, Crystal Reports and
        custom applications
   Project Roster
       7+ Initiatives
       Development teams of 2 to 10 Developers
   11 Universes, 300+ Reports
   Operational Reporting, Financial Reporting, Ad-Hoc Analysis
   Crystal Reports XI, Business Objects XI, OLAP Intelligence, Live
    Office, Ab Initio, Oracle

                                  May 19, 2006                     22
Business Challenge

        “Stove Pipe” Approach to Reporting Solutions

Source Systems                   Middle Layer Data                                   Multiple Reporting Tools Utilized   Business Units
                                                                                             Reporting                     Unit 1
                                     OLAP Cube
                                                                              Access                      Crystal
                                                                             Reporting                   Reporting
                                                                                                                           Unit 2
                                       Database                               Business                    Custom
                                                                               Objects                   Application
                                                                                                                           Unit 3

 * Illustration is an example of a Stove Pipe problem many organizations encounter
 and has been altered due to client confidentiality.

                                                                                         May 19, 2006                           23

   One Single Source of Truth

   Improve Data Quality and Consistency

   Management of Corporate Financial Reporting Initiatives

   Sarbanes-Oxley Act (SOX) compliancy

   Develop a Business Intelligence solution NOT a reporting solution

                                 May 19, 2006                 24
Process Implementation

   Establish a competency center

       Business Intelligence SMEs are integrated into the Requirements, Data
        Model, and ETL Development

       Standard approach for report development

       Procedures and Templates utilized during all Phases of the
        Development life cycle

       Fast Track Process for Implementation

       Iterative Development Approach

       Peer Reviews

                                   May 19, 2006                      25

                                                         OLAP Intelligence



                     Enterprise Data

    Source Systems

                                                         BOXI InfoView – (LDAP)

                                       May 19, 2006                               26
Technical Implementation

   Universe
       Report Complexity
       Flexibility

   Crystal Reports
       Provide highly defined reporting
            Internal and External Financial Reporting
            Protect Corporate Financial Data

   WebIntelligence
       Provides ad-hoc analysis functionality
            Quick querying
            Validation of numbers
            Ability to ‘Slice and Dice’ Data

                                                May 19, 2006   27
Why Crystal Reports and Universes?

   Universe
       Central point of control (One Source of Truth)
       Provides ability to query the database directly
       Decreases processing time on complex SQL statements and equations

   Crystal Reports
       Provides highly defined reports that allows developers to create
        financial reports that precisely match the requirements

                                    May 19, 2006                      28
Lessons Learned

   Collaboration between Crystal Reports and Universe SMEs is an
    important aspect of the Design phase.

   The Universe is critical to performance optimization by controlling
    the result set in the queries.

   WebIntelligence Analysis capabilities can be leveraged in Crystal
    Reports via Report Linking.

   Universe and Crystal Reports can work together

                                 May 19, 2006                   29


 Contact information:
      Lisa Kidd
      Ian Graham
      Chris Diep
      Ned Blackburn

      Office: 703.326.0888
      Fax:    703.326.0677

                             May 19, 2006   30

To top