Managing

1

• Managing
• Oracle Applications Data
• Smarter

No part of this presentation may be reproduced or
transmitted in any form by any means, electronic
or mechanical, including photocopying and
recording, for any purpose without the express
written permission of Princeton Softech, Inc.
2
Information Powers the Organization

• Information is an increasingly valuable and
  costly organizational asset
• Goals minimize risk, reduce costs, and increase
  flexibility...
• By aligning IT investments according to evolving
  information value

Structured
Semi-Structured
Unstructured
3
Challenge Database Growth
2003
2002
2001
4
How much? How fast?
With growth rates exceeding 125, organizations
face two basic options continue to grow the
infrastructure or develop processes to separate
dormant data from active data. Source META
Group 2003
5
Data Growth Impacts Business Process

• As Transaction Data
  Accrues
• Longer batch cycles
• Resources and maintenance increases
• Increased storage and processing costs
• Batch window crosses into prime time
• Impacts to the business process

6
Whats Driving Data Growth?

• High-volume online transaction processing
• Customer facing eCommerce applications
• ERP/CRM
• Supply chain applications
• Record retention requirements
• Financial Services Sarbanes-Oxley
• Healthcare HIPAA
• Pharmaceutical 21 CFR 11
• Financial IRS and SEC Rule 17a-4
• Multiplicity of data
• Multiple operational, development and testing
  environments
• Disaster recovery and business continuity
• Routine backup and recovery

7
Data Retention Example

• SEC Rule 17a-4
• Retain records for six years from close of
  account or termination of associated employees
• Keep records in an "easily accessible place"
• Produce records immediately if the records are
  located in the office where the request is made
• Produce records within three business days if the
  requested records are located off-site
• Display requested records electronically in a
  local office and immediately produce printed
  copies to satisfy Rule requirements

8
Data Multiplier Effect
9
Database Growth Cant Ignore It

• continue to add
• People
• Processes
• Technology
• continue to limit
• Performance
• Availability
• Time for other projects

ProductionDatabase
10
Alternatives

• Add more capacity
• Bottom line impact
• Uncontrolled cost spiral
• Institute rigorous database tuning
• Does not directly address data growth
• Reaches point of diminishing returns
• Delete/ purge data
• Legal and data retention issues
• Data may be needed for data warehousing
• In-house development
• Complex undertaking
• Application specific
• Support/upgrade/maintenance/opportunity cost

11
What Can We Do?
Deploying a successful database archiving
strategy is critical to ensure database
availability.
Organizations should incorporate data archival
policies within current design or implementation
plans to maintain future performance and reduce
related costs.
12
About Princeton Softech

• Providing relational data management solutions
  for enterprise IT since 1989
• Global company 2000 customers in 30 countries
  
• Recognized by Gartner, Giga and META as market
  leader
• Pioneered enterprise database archiving and test
  data management segments
• Partners with industry-leading database,
  enterprise application and storage solution
  providers

13
Manage Enterprise DataSmarter
ClarifyCRM
PeopleSoft
Oracle Apps
RelationalTools for Servers
Archive for DB2
Archive for Servers
Relationship Engine
Legacy
Oracle
SQL Server
Sybase
Informix
DB2 UDB
DB2
14
Smaller Production DB Lower Cost
15
Princeton Softechs Active Archive Solutions

• Reduce the amount of data in the application
  database
• Remove obsolete or infrequently used data
• Maintain business context of archived data
• Enable easy user access to archived information
• View, research and restore as needed
• Support data and storage management strategies

16
Solution Overview

• Support for Oracle Applications versions 11.0
  11i
• Financials
• Manufacturing
• Supply Chain
• Human Resources
• Projects
• Transparent access to data via standard Oracle
  Applications forms and reports
• Pluggable archiving framework designed to support
  predefined archive templates and local
  customizations

17
Archiving Oracle Financials Data
Archiving Historical Data
Active Archive Database

General Ledger
GL Balances, Journals AP Payments,
Invoices, Vendors AR Receipts, Invoices FA
Depreciation, Adjustments
Payables
Production Database
Receivables
Assets
Locate, Browse, Query, Report . . .
Data Access
18
Transparent Access to Archived Data

• Seamless access to BOTH archived and production
  data via Oracle Applications
• Leverages Responsibility to access data, using
  standard Oracle forms and reports
• Steps to view archived data
• Login
• Select Responsibility
• Access archived data, production data or BOTH

19
Archive Workbench

• Manage and report on the lifecycle of the entire
  archive process
• Current location of the archived data
• Run archive jobs
• To the active archive database
• To Offline storage
• Restore
• Detailed Reporting on the Archive Process
• Request date and time
• Current status
• Module and user
• Criteria used
• Number of records archived from each table

20
Verification Engine

• System Verification Diagnostics
• Comprehensive top to bottom configuration
  analysis/validation
• Archive Process Diagnostics
• Performs application validation prior to
  archiving data
• Verifies and matches table structures in the
  active archive database
• Oracle Patch and Upgrade Checking
• Detects mismatches
• Provides upgrade procedures

21
Information Lifecycle Management

• All data has a lifecycle
• Parameters differ over time
• Business value
• Access requirements
• Performance requirements
• Store and manage accordingly

22
Customer Success - AVX

• Who is AVX?
• Kyocera Group company
• manufacturer and supplier of electronic
  components
• Challenges
• Implement a single global instance without
  adversely impacting business
• Avoid business disruption during software
  upgrades
• Reduce frequency of hardware upgrades
• Maintain consistent database size and performance
  
• Reasons
• Consolidation will grow the database by
  magnitudes
• Need to implement database archiving before
  adding the largest manufacturing site (70,000
  WIP move transactions daily)

23
Customer Success - AVX

• Implementation Approach
• Business requirements
• Decrease upgrade time
• Focus staff on core competencies
• Prepare for archiving during the definition phase
  of the global IT solution
• Provide user community with transparent data
  access
• Results
• First phase installed, configured implemented
  in one week
• Consistent database size and performance
• Smaller test environments refresh quickly

24
Database Archiving Benefits Summary
25
Archiving Reduces Database Volume
26
Streamlining Application Environments

• Multiple environments required
• Application deployments
• Customizations
• Upgrades
• Patches
• QA database
• Training, Staging, etc.
• Challenges
• Space
• Time
• Cost

27
Typical Approaches

• Purchase additional disk space?
• Stretch the project timeline?
• Discontinue one of your current projects?
• Most common solution
• Share test and development databases
• among projects/teams

28
Managing Oracle Applications Test Data

• Extract precise subsets of related data to build
  realistic, right-sized test databases
• Create referentially intact subsets
• Remove the bulk of production data
• Minimize the load on testing and staging servers
• Speed iterative testing tasks with reusable
  processing definitions and Extract Files to
  ensure consistency
• Browse and edit complex relational data from
  multiple tables in a single display

Production Database
Test
29
Create, Review, Edit Templates

• Add or Remove tables
• Include new DB objects
• Functions
• Packages
• Procedures
• Rules
• Sequences
• Views

30
Creating a Subset using a Template

• Criteria can be based on one or more modules
• All Date Values
• GL Date
• Transaction Date
• Default Effective
• Date
• Sets of Books
• Organizations
• Status
• Invoice number(s)
• And/Or combos
• More.

Define your criteria
31
Benefits

• Maximize allocated disk space
• Increase number of test/ dev environments
• Reduce infrastructure costs
• Realize development and test efficiencies
• Reduce the cycle times for test upgrades
• Reduces time and resources required to backup and
  maintain non-production environments

32
Manage Enterprise DataSmarter
ClarifyCRM
PeopleSoft
Oracle Apps
RelationalTools for Servers
Archive for DB2
Archive for Servers
Relationship Engine
Legacy
Oracle
SQL Server
Sybase
Informix
DB2 UDB
DB2
33
Questions

• David Stevens
• Princeton Softech
• dstevens_at_princetonsoftech.com
• 609.627.5500