Guident Case Studies - Business Intelligence by uoi11893

VIEWS: 10 PAGES: 30

									                                             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
Agenda

   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
    reports.
   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
  requests.

 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
  system.

 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
                    Funds
  Divisions                                        Teams
                         Groups


 Contracting System
          Solicitations and Contracts                           Amendments


                    Assignments                    Vendors


   Workorders             Modifications                  Obligations

                                          May 19, 2006                              8
Building the Data Mart

 HQ
OLTP

                                                                Corporate Reports
                     ICMS
Region                ODS
 OLTP    Daily ETL

                                                    Data Mart
                             OARM
                              ODS
                                        Daily ETL
Region
 OLTP                SPEDI
                      ODS
         Daily ETL




Region
 OLTP
                                                                   Ad Hoc Query,
                                                                    and Analysis




                                    May 19, 2006                     9
Configuration Architecture

                        ODS   DEV              QA   PROD

Data Integrator (ETL)




  Web Intelligence




       Data




                                May 19, 2006               10
Development Process


Requirements Analysis   Data Modeling               ETL Design




 Universe Design        Data Extraction         Store Data       Reporting

                         Transfor
                            m




                                 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

                                                           Guident




                          ORACLE
                                                           Data Quality
 Source        ETL                 DQ Raw
  Data
                                                           Engine
                                   Tables




                                                                          ORACLE
                                                    DQ Reveal Engine               DQ Results



                        ORACLE

             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
  weeks.

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

 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
Agenda
   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
                                      Source
                                                                                               Excel
                                                                                             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
Goal


   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
Solution




                                                         OLAP Intelligence



                                                Cube
              ETL




                                             Universes

                     Enterprise Data
                       Warehouse



    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
Q&A



 Questions?

 Contact information:
      Lisa Kidd
      Ian Graham
      Chris Diep
      Ned Blackburn

      Office: 703.326.0888
      Fax:    703.326.0677




                             May 19, 2006   30

								
To top