Guident Case Studies Business Intelligence

1
Guident Case Studies - Business Intelligence

• - Center of Excellence Financial Reporting

- EPA Administrative Offices Data Mart
2
EPA Administrative Offices Data MartAgenda

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

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

4
BI Maturity at Project Start
5
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.

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

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

8
High Level View of OLTP Systems
Purchasing System

• Purchase Orders

Purchase Requests
Vendors
Details
Obligations
Details
Funds
Divisions
Teams
Groups
Contracting System
Amendments
Solicitations and Contracts

Assignments
Vendors
Workorders
Modifications
Obligations
9
Building the Data Mart
Corporate Reports
Daily ETL
Daily ETL
Daily ETL
Ad Hoc Query, and Analysis
10
Configuration Architecture
ODS
DEV
QA
PROD
Data Integrator (ETL)
Web Intelligence
Data
11
Development Process
Data Modeling
ETL Design
Requirements Analysis
Data Extraction
Universe Design
Reporting
Store Data
Transform
12
Data Quality
13
Data Quality - Example
Budget by State

• ID - 8M
• IA - 10M
• IN - 12M
• IL - 7M
• KA - 2M
• KS - 20M
• Kansas - 6M
• KY - 17M
• LA - 40M

14
Data Quality Assessment Process
GuidentData QualityEngine
Source Data
ETL
DQ Reveal Engine
Rules Information
15
Data Quality Accessing DQ Reports
16
Data Quality Assessment Summary
17
Data Quality Assessment Details
18
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.

19
BI Maturity at Project End
20
Lessons Learned

• Its 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.

21
Center Of Excellence Financial ReportingAgenda

• Case Study - Background
• Business Challenge
• Goal
• Implementation
• Why Crystal Reports and Universe
• Lessons Learned
• QA

22
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

23
Business Challenge

• Stove Pipe Approach to Reporting Solutions

Source Systems
Middle Layer Data Source
Multiple Reporting Tools Utilized
Illustration is an example of a Stove Pipe
problem many organizations encounter and has been
altered due to client confidentiality.
24
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

25
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

26
Solution
27
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

28
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

29
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

30
Q A
Questions?
Contact information
Lisa Kidd Ian Graham Chris Diep Ned
Blackburn Office 703.326.0888 Fax [phone]