Transcript Transparent Access to Archived Data

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.
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
2
2004 Princeton Softech, Inc.
Challenge: Database Growth
2003
2002
2001
3
2004 Princeton Softech, Inc.
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
Current Data
Historical Data
2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011
4
2004 Princeton Softech, Inc.
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
5
2004 Princeton Softech, Inc.
What’s 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
6
2004 Princeton Softech, Inc.
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
7
2004 Princeton Softech, Inc.
Data Multiplier Effect
2
3
1
6
4
Production
Backup
Disaster Recovery
Development
Test
Quality Control
Total Size =
200 GB
200
200
200
200
200
1200 GB
5
8
2004 Princeton Softech, Inc.
Database Growth: Can’t Ignore It
…continue to add
– People
– Processes
– Technology
…continue to limit
Production
Database
– Performance
– Availability
– Time for other projects
9
2004 Princeton Softech, Inc.
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
10
2004 Princeton Softech, Inc.
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.”
11
2004 Princeton Softech, Inc.
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
-
12
2004 Princeton Softech, Inc.
Manage Enterprise Data…Smarter
PreProduction
Data
Production &
History Data
PeopleSoft ClarifyCRM
Relational
Tools for Servers
Archive
for Servers
Oracle
Apps
Archive for
DB2
Relationship Engine
Oracle
SQL
Server
Sybase
Informix
DB2
UDB
DB2
Legacy
13
2004 Princeton Softech, Inc.
Smaller Production DB; Lower Cost
Historical
Reporting
DR
BU
Archive Production
Data
Live
Near line
Archive
DBMS
Production
DBMS
Compare
Results
Offline
Archive
Storage
Refresh
Test Data
Test
DBMS
Streamline
Test Data
Edit
Test Data
14
2004 Princeton Softech, Inc.
Princeton Softech’s Active Archive Solutions
Archive
Files
Production
Database
Active Archive
Database
Archive
&
Restore
Archive Files
Data Access (locate, browse, query, report)



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
15
2004 Princeton Softech, Inc.
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
16
2004 Princeton Softech, Inc.
Archiving Oracle Financials Data
Archiving Historical Data
Active Archive
Database
Production
Database
GL – Balances, Journals …
AP – Payments, Invoices, Vendors…
AR – Receipts, Invoices …
FA – Depreciation, Adjustments
General Ledger
Payables
Receivables
Assets
Locate, Browse, Query, Report . . .
Data Access
17
2004 Princeton Softech, Inc.
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
Archived
Data
Production
Data
18
2004 Princeton Softech, Inc.
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
19
2004 Princeton Softech, Inc.
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
20
2004 Princeton Softech, Inc.
Information Lifecycle Management
 All data has a lifecycle
 Parameters differ over time
– Business value
– Access requirements
– Performance
requirements
 Store and manage
accordingly
Acquisition
Disposal
Minimal
Access
Frequent
Access
Sporadic
Access
21
2004 Princeton Softech, Inc.
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)
22
2004 Princeton Softech, Inc.
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
23
2004 Princeton Softech, Inc.
Database Archiving Benefits Summary
Improved
Performance
• Faster response time
• Increased productivity
• Satisfied customers
Improved
Availability
• Faster batch cycles
• Ready for business
• Transparent access
Cost Containment
• Postpone hardware upgrades
• Postpone software upgrades
• Deliver competitive advantage
24
2004 Princeton Softech, Inc.
Archiving Reduces Database Volume
Production
Backup
Disaster Recovery
Development
Test
Quality Control
Total Size =
200 GB
200
200
200
200
200
1200 GB
Production Data
120 GB
Active Archive Data
Production
Backup
Disaster Recovery
Development
Test
Quality Control
120 GB
120
120
120
120
120
Total Size =
720 GB
Saving 480 GB (40%)
Primary Storage
80 GB
25
2004 Princeton Softech, Inc.
Streamlining Application Environments

Multiple environments
required
– Application deployments
– Customizations
– Upgrades
– Patches
– QA database
– Training, Staging, etc.
 Challenges
– Space
– Time
– Cost
26
2004 Princeton Softech, Inc.
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
27
2004 Princeton Softech, Inc.
Managing Oracle Applications Test Data

Extract precise subsets of related
data to build realistic, “right-sized”
test databases
– Create referentially intact
subsets
Production
– Remove the bulk of production Database
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
Test
QA
Dev
28
2004 Princeton Softech, Inc.
Create, Review, Edit Templates


Add or Remove
tables
Include new DB
objects
– Functions
– Packages
– Procedures
– Rules
– Sequences
– Views
29
2004 Princeton Softech, Inc.
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…
30
2004 Princeton Softech, Inc.
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
31
2004 Princeton Softech, Inc.
Manage Enterprise Data…Smarter
PreProduction
Data
Production &
History Data
PeopleSoft ClarifyCRM
Relational
Tools for Servers
Archive
for Servers
Oracle
Apps
Archive for
DB2
Relationship Engine
Oracle
SQL
Server
Sybase
Informix
DB2
UDB
DB2
Legacy
32
2004 Princeton Softech, Inc.
Questions
David Stevens
Princeton Softech
[email protected]
609.627.5500
33
2004 Princeton Softech, Inc.