Archiving, Subsetting, Scrambling Oracle Apps, PeopleSoft, Siebel, and Custom Applications

1
Welcome

• Archiving, Subsetting, Scrambling Oracle Apps,
  PeopleSoft, Siebel, and Custom Applications

2
Welcome

• Lets make todays presentation interactive
• Who are you? And why are you Here?
• Functional?
• Technical?
• DBA
• Developer
• Superuser
• Applications and Modules causing growing pains

3
About me

• Adam Gwosdof agwosdof_at_applimation.com
• Worked at Oracle Corporation in the Applications
  Division, starting in 1988
• Oracle Assets
• Oracle Payables
• Worked with Oracle Consulting through the early
  and mid 1990s, doing Apps Implementations
• Now manage Applimations Oracle relationships

4
Applimation Overview
INFORMIA
INTEGRA
CompanyBackground
Data GrowthManagement
IT Controls Compliance

• Application Archiving
• Database Subsetting
• Data Masking
• Database Reorg

• Change Management
• SOX Compliance
• Segregation of Duties
• Security Reporting

• Headquartered in NYC
• 375 Customers
• 105 Employees
• Investors JMI Equity Fund Battery Ventures
• Oracle, PeopleSoft Siebel

5
Applimation Lifecycle Management
Challenge Data Growth Solution
Challenge Managing Change Solution
INFORMIA
INTEGRA

• Archive and Purge
• Combined access to historical and current
  transactions
• Repeatable Policy-based data movement
• Shrink test environments
• Mask sensitive data
• throughout the lifecycle

• Enhance internal controls
• Manage impact of change
• Ensure application integrity
• Enforce Segregation of Duties rules
• Automate SOX compliance
• throughout the lifecycle

6
Data Management Challenges

• Benefits of archiving

7
The challenge of increasing data growth

• AFTER INFORMIA
• Reduced Total Cost of Ownership

• BEFORE INFORMIA
• Growing storage costs
• Diminishing performance
• Increasing maintenance compliance work

• AFTER INFORMIA
• Lower, stable storage costs
• Improved, stable performance
• Reduced maintenance compliance work

Database Size
Performance
Reduced costs
Escalating costs
Inactive data
Active data
Time
8
Business reasons to manage growth

• Many copies of production
• Project teams require many testing and
  development environments
• Compliance requirements
• SOX regulating data retention
• Data must be retained for potential audits
• Application Upgrades
• Increased growth due to larger footprint

9
Data Management Options

• Purchase additional hardware
• DBAs tune application standard code
• Purge using Vendor provided purge routines if
  available

THE PROBLEM constantly spending on hardware,
tuning only does so much, purging doesnt archive
10
Benefits of 3rd party solution

• Improve production environment performance
• Fully retire inactive data to offline storage
  devices
• Reduce size and resource requirements of the
  production environment
• Archive and purge inactive data
• Improve backup and recovery time requirements
• Maintain access to application data through
  familiar screens reports

11
Key Strengths

• Speed and Scalability of Archiving / Purging
  Process
• Faster than approaches based on
  vendor-proprietary purge
• Archive and purge are unified and optimized
• Applimation metadata-driven technology
• Does not mine archive logs
• Never requires specific Oracle patches or patch
  sets
• Data is never unloaded then reloaded to database!
• PeopleSoft and Siebel Certified
• Oracle Integration Validation
• Table/Index/Tablespace Reorg built in
• MetaStudio productized developers edition

12
Over 115 Informia Customers
City of Chicago
13
Applimation Informia

• Solution Overview

14
Informia-Enterprise Solution
Application Support
Oracle Apps(E-Business Suite)
Custom or 3rdParty Applications
PeopleSoft
Siebel
Applimation Informia
Database Support
InformiaSubset
InformiaReorg
InformiaArchive
Product
OracleDatabase
SQL Server
DB2/UDBDB2/zOS
DataGrowthMonitor
MetaStudio
ApplimationDashboard
Platform
15
Meeting the Challenge - Archive

• Use Archive to identify inactive data, honoring
  retention policy and
  business rules.

• Then relocate data to archives

ONLINE ARCHIVE DATABASE
16
Meeting the Challenge Subset Data Masking

• Use Subset to flexibly create database subsets

• Optionally mask sensitive data in non-production
  databases

SUBSET OFPRODUCTION DATABASE
DATA MASK OFDATABASE SUBSET
CLONE OFDATABASE SUBSET
CLONE OF MASKEDDATABASE SUBSET
PRODUCTIONDATABASE
CLONE OF PRODUCTION DATABASE TO SUBSET
Data for Module A
Subset for Module A
Data for Module B
Subset for Module B
Data for Module C
Subset for Module C
17
Meeting the Challenge - Reorg

• Use Reorg to optimize tables, indexes, and
  tablespaces after archiving

TABLESPACE
TABLESPACE
18
MetaStudio Developers Edition for Custom
Archiving

• Developer Toolkit to build and enhance metadata
• Identify tables to archive/purge
• Discover relationships
• Create or modify business rules
• for pre-supported business applications, or for
  entirely custom/in-house applications

Archive Engine
Archive Engine
19
Online Archive
Production
???
Business Application(s)
APPS or SYSADM or SIEBEL
20
Online Archive Process
Production
Archive / History
1. Archive 2003 Data to Relocation Area
2. Purge 2003 Data from Production
2006
3. Merge 2003 Data to Online Archive
2005
AM_HISTORY
2004
4. Validate Drop Relocation Area
2003
Optional process following archive
2003

• Resize tables/indexes based on purged data

• Reclaim additional space from non-purged data

• Right-size the underlying datafiles with revised
  growing room based on forward-looking retention
  policy

21
Seamless Data Access

• Auto-generated
• No user re-training
• Access to
• Current
• Combined (current history)
• No modifications to code base
• Support for any/all query tools

22
Data Growth Monitor

• On-demand view
• Current size
• Growth rate
• Data distribution
• Tablespace
• Application
• Instance
• Server
• Storage device

23
Create and Schedule Policies
1. Choose Entities
2. Specify Parameters
3. Generate Candidates
24
Candidate exception reporting
25
Online Archive Workbench
1. Choose Entities
2. Specify Parameters
3. Generate Candidates
4. Process Archive/Purge
5. Relocate History Data
26
Oracle Integration Validation
Certified Archive Solution
27
PeopleSoft Certified
Certified Archive Solution
28
Siebel Validated Solution
Certified Archive Solution
29
Application and Customer Aware

• For your Oracle packaged modules
• Application-aware pre-configured Entities
• Oracle validated
• Policies compatible with your calendar periods
• Faster and more scalable than alternative
  approaches
• For your custom tables and applications
• MetaStudio a complete workbench for managing
  custom data

30
Role-Based Security

• Supports Compliance Segregation of Duties
• Roles include Archive Administrator (DBA),
  Archive User (Policy Manager), Archive Scheduler
  (Operations), ArchiveDeveloper
• Keeps Administrator and Policy Managers apart
• Who can Define versus Who can Do

31
Oracle Applications 11i Archiving
32
PeopleSoft 7.5 through 8.9 Archiving
33
Siebel 7.5, 7.7, 7.8 Archiving
34
Sample Retention Policies
35
Informia Reorg

• Use Reorg to optimize tables, indexes, and
  tablespaces after archiving

TABLESPACE
TABLESPACE
36
Data Masking

• Scramble or encrypt any data FK/PK surrogate keys
  
• Masking Methods
• Built-in encryption
• Scrambling algorithm
• Easily extend to include one of your own
• De-identify sensitive data
• Employees
• Customers
• Suppliers
• Perfect for training, testing, development
  databases
• Good for offshore development

37
Archive Sample Project Plan
Project Timeline
38
Customer Support

• 24x7Availability
• Product Download Center
• Products
• Patches
• Documentation
• Tools
• Customer Support Center
• Submit Cases
• View and Comment on Open/Closed Cases
• Solutions Center Knowledge Base
• Patch Development performed within Customer
  Support
• Interface with Product Development for
  enhancement requests and long-term problem
  resolution

39
Client Electronic Arts

• Industry Multimedia and Graphics Software
• Modules Archived AR, INV, OE/ONT
• Business Rules Modified None
• Customizations Accommodated Added 27 custom
  tables
• Environment Oracle Apps 11.5.9, DB Version
  9.2.0.5 RAC
• Implementation timeline
• Phase 1 1/24/05, Testing in February/March then 3
  week delay until UAT, UAT testing April/May.
• Archive Production Cycles started June, 2005
• Key project highlights Archives will run every
  weekend from June to November until data
  retention policies are caught up

40
Client ATT Wireless

• Industry Telecommunications
• Modules Archived FA, INV, AP, PO, OE/ONT
• Business Rules Modified Upgrade to 11i affected
  last_update_date rules
• Customizations Accommodated Added custom tables
  to INV entity
• Environment Oracle Apps 11.5.7/11.5.8 mixed, DB
  Version 9.2.0.4
• Implementation timeline
• Phase 1 7/6/04, Changed priority of modules to
  put FA first due to end of month close issues
• Archive Production Cycles started November, 2004
  
• Key project highlights Archived out 1998
  through 2002 data to reduce database by 50.
  Performed reorg to reclaim space. Monthly
  archives planned.

41
Client IKON

• Industry Office Solutions
• Modules Archived OM, PO, INV
• Business Rules Modified Created rule to check
  open payment schedules for OM
• Customizations Accommodated Added custom tables
  
• Environment Oracle Apps 11.5.9, DB Version
  9.2.0.5 RAC
• Implementation timeline
• Phase 1 January 2004, Implementation of Subset
  and Archive concurrent so priorities shifted
  often, Delayed to prioritize upgrade from 11.5.8
  to 11.5.9 in November, 2004
• Archive Production Cycles started May, 2005
• Key project highlights Archived out 540GB in
  first cycle, Archive will run quarterly, Field
  Service and Contracts are next to Go Live

42
Customer Quotes

• Thank you - The superb support staff at
  Applimation who made me feel like I was their
  only customer and my issue was their most
  important concern - Peter Plackowski
• Applimation's Service and Support team surpassed
  any support team we've ever worked with.  They
  were on-call 24/7 and addressed all our questions
  and concerns so we could achieve a smooth archive
  and go-live. We have not only saved substantial
  costs is disk storage, we have improved the
  overall performance of our system
  
  - Bob Peery
• Excellent service. Always available to help
  - Chris Carriero

43
Summary

• Anticipation of performance and storage issues
  might actually be your ticket towards
  proactively addressing data retention and
  compliance issues.
• Data Growth Management is an interdisciplinary
  challenge you need to build consensus among
  technical, functional, and management
• Start Soon with budgeting and implementation
  timeframes, your time horizon is now