Star

1
Oracle Hyperion Financial Data Quality
Management Considerations for a scaled,
expedited and integrated approach on data quality
NCOAUG Aug 15, 2008 120 200 pm Matthias
Heilos, Pinnacle Group Worldwide
2
Introduction Matthias Heilos

• Consultant at Pinnacle Group Worldwide
• Hyperion Expertise Financial Data Quality
  Management, Essbase, Planning, Financial
  Management
• Prior European IT Management Consulting firm
• Business Intelligence
• EPM, Reporting, Planning, CRM

3
Agenda

• Introduction to FDM
• Situation at a Fortune 100 client
• Enhancing FDM to succeed
• Automation / Integration
• Useful features
• Questions

4
What is FDM?

• Oracles Hyperion Financial Data Quality
  Management
• Is a transformation tool that feeds source level
  data to consolidation, reporting, planning, and
  analytical applications
• provides an audit trail to the source financial
  data, helping ensure data integrity and mapping
  consistency that allows for easy reconciliation
• offers a consistent, end user-friendly
  environment that provides a uniform data
  collection process for all reporting units within
  the organization

Source FDQM Quick Start Guide
5
FDQM Architecture
Unmapped items?
FDQM
Email Notification about Data Quality (custom)
Mappings
Batch Loader (optional)
Import Formats
Output Files
Imported Data
Validated Data
Export
Load
6
Situation at a Fortune 100 client
Requirements
Problems faced

• MA data integration of 11 locations
• Data volume 3.5 million records (3 locations gt 1
  mio), time frame 2 hours
• gt 50 attributes
• Complex multi-step mappings
• Automated and integrated process

• Import and Mapping takes very long
• Too many attributes
• Problems with DB transaction handling
• Multi-step mappings not supported
• Export fails due to large amount of data

FDM can meet these requirements using Pinnacles
FDM Enhancer
7
Limitation Too many attributes

• Import process step
• Problem too many attributes
• Solution FDM Extension
• Add row number to each record in source file
• Separate dimensional data and attributes, process
  attributes via FDM extension (custom attribute
  table)
• Merge data during FDM Export based on row number

8
Internal Processes Overview
Import
Delete (optional)
Import data
Map data
API Event Script
Validate
Fix mappings (manual / auto-map)
Reapply mappings
Export
Export data
Load
Load data to target system
Validate results
9
Expediting the Import process

• Import process step
• Problem takes very long

1 Tests performed in test environment, results
may vary 2 Administration of FDM Enhancer
available through User-Frontend (like Import
Formats)
10
Expediting the Mapping process

• Mapping process step
• Problem takes very long
• Mapping types besides Explicit and Between
• IN should not contain many values, rather split
  1 large mapping into several mappings with only
  few values
• LIKE convert ? to 1 ? 1, 2 ? 2 etc.
  (map-thru)

• FDM Enhancer can significantly lower processing
  time
• Map-Thru only on database level via custom
  script no mappings for map-thru dimensions
• Mapping process entirely based on SQL script, no
  costly update statements needed

11
Enhancing the Mapping process

• Mapping process step
• Problem
• FDM does not support complex mappings (look up
  data from a database or several transformation
  steps), only hard-coded mappings based on
  information in source data file can be applied
• Solution
• Create custom mapping script for complex
  transformations which will be applied after FDMs
  mapping step

12
Automation / Integration
FDM Automation Script
FDM Extension
Attribs
FDM
Export /Load Files
Dims
FDM Process
Scheduler
Wait
FDM Status
Check if complete until timeout
Email Notification
Validation step skipped as integrated in
enhanced Import step (including data quality
checks)
13
Data Quality at a glance
14
Conclusion

• FDM was created to support data quality processes
  of financial data and integrate this data into
  Oracles EPM suite (Financial Management,
  Planning etc.)
• Supports Oracles Management Excellence
• Using Pinnacles FDM Enhancer, handling large
  amounts of data is possible. Tool selection
  should be primarily based on purpose should the
  process be controlled by business user or IT
• Pinnacle Group Worldwide leads even large FDM
  data integration projects to success. FDM
  Enhancer offers a variety of pre-built features
  and methods to improve, enhance, scale and
  expedite FDMs performance.

15
Questions
16
(No Transcript)
17
Scalability Resource usage

• Delete process step
• Problem rollback segment in parallel mode
  exceeded, too many transactions per commit cycle
• Solution Paging algorithm to delete subsets of
  data in smaller transactions prior to FDM step

18
Scalability Export process

• Problems
• ADODB Recordset exceeds 2GB memory limit
• Extract routine is time-consuming (data mart
  adapter)
• Solutions
• Paging algorithm to extract
• Create dynamic SQL script, use DB Tool for
  extraction into delimited flat file

19
Useful features

• Data quality at a glance, including enhanced
  management information (see next slide)
• System integrity checks
• Number of mappings per dimension and location
• Compare mappings between periods
• Archive existing mappings
• Custom logging, can be retrieved per day,
  location, and process step as stored in database