Achieve Siebel Excellence Best Practices Solution in Archiving
Achieve Siebel Excellence Best Practices Solution in Archiving and Test Data Management Northern California OAUG, Training Day January 2007 Stephen Mohl Siebel Specialist © 2007 Princeton
What If …? What if you could easily identity and remove outdated data from your Siebel database? What if your users could still view the archived data from within the Siebel application? What if you could easily populate test databases with masked production data? © 2007 Princeton Softech, 2
Princeton Softech Optim™ Manage enterprise application data throughout the information lifecycle Apply business rules to subset, archive, store and access enterprise application data Protect data privacy Leverage a single solution to support and scales across applications, databases, and platforms Optimize the business value of your IT infrastructure © 2007 Princeton Softech, 3
What is Enterprise Data Management? © 2007 Princeton Softech, 4
Siebel Archiving Business Drivers Manage application performance and data volume growth cost effectively. Ensure regulatory compliance by maintaining data needed for potential audit. Preserve data snapshot prior to upgrade. © 2007 Princeton Softech, 5
Comprehensive Performance Management Strategy Tune System Add Capacity Reconfigure Application Train Users © 2007 Princeton Softech, Archive Outdated Data 6
Siebel Archiving Solution Results Archive subsets of Siebel data - Complete business object - Audit-ready “snapshot in time” Delete inactive, historical data from production Archive associated attachments from file system Locate and browse archived data © 2007 Princeton Softech, 7
Siebel Archiving Solution Results Store data on cost-effective media - Access across medium types Access archive data across Siebel versions - Archive in 7. 7, but access in 7. 8 Provide scalable, enterprise support Selectively restore data for additional business processing - Production/Reporting/Au © 2007 Princeton Softech, 8
Integration Schematic Siebel Mobile Applications Siebel On Premise Applications Multiple Client Device Support Siebel Teller Applications Siebel Portal Framework Development Environment Siebel Tools Sync Local DB Web Server Siebel Application Server User Interface Services Design PST Siebel Repository Ru n Archive File Directory Business Logic Layer and Core Service EAI Data Layer Services tim e Federated Data Sources OLTP Archive Files © 2007 Princeton Softech, Bus. Process PST CX_Table Files Siebel Universal Customer Master OLTP Oracle JD Edwards PSFT Legacy 9
Translating Siebel Object Model to Optim. Siebel The Business Object becomes an Access Definition (AD) BO’s primary BC table becomes Optim’s start table Links, Joins and Multi value links determine: - Tables included in the Access Definition - Relationships between the tables - Separate Access Definitions for Optim-Siebel Archiving that use ‘Cascade Delete property’ determine the setting of Optim ‘Delete After Archive’ © 2007 Princeton Softech, 10
Archive Process Production DB CUSTOMERS -- ---- ------- ----- -------------------- DETAILS ------------------------ Staging Area Archive Purge Storage ORDERS Optim Server Archive File Directory Archive Files Restore Access CUSTOMERS -- ---- ------- ----- ORDERS ------ ----- --------- --------- ---- DETAILS ------- -------------------- © 2007 Princeton Softech, 11
Archiving Process Flow Optim Engine Production OLTP Template Archive Template for: Industry Optim Archive Templates Support standard NT, Solaris, AIX, archive HP/UX Databases Define a business Compressed OS/390, z/OS --- Does not require aobject DBMS Oracle, Sybase, Informix, DB 2, UDB, SQL/Server -- Created from schema RI, Erwin import, GUI or a Archive while online for 24 x 7 Secured -- Does not require the application Complex interrelationships combination operations (Decommissioning) Indexed. DBMS retrieval Applications -- Multiple support Access DB 2 from Unix - Cannot be altered Custom & Packaged, Legacy, Oracle E-Business Widest selection of Information Lifecycle Out of the box Place DB 2 Archive on Unix options Enterprise, JD Edwards Allows for People. Soft deferred purge operation - Suite, Siebel 7. 5, 7. 7, & 7. 8 Enterprise. One, Siebel, Amdocs CRM SAN, policies NAS Establish -- Auditable - Multiple, interrelated applications, databases & Constraints or condition checks, to determine Nearline (Centera, RISS, DR 550, used --- Proves data archived is identical to purged platforms eligibility Intellistore) data - Time or(Tape, other parameters provided at run time Offline Optical) -- Allows for user. CD, review prior to purge - Enterprise Vault, Tivoli © 2007 Princeton Softech, 12
Ensure Referential Integrity Ex: Activities Archive File © 2007 Princeton Softech, 13
Enterprise Test Data Management © 2007 Princeton
Challenges of Siebel Test Data Management Siebel doesn’t provide a solution or methodology for TDM Siebel has a very complex data model consisting of many tables with multiple relationships between tables Siebel Industry Applications share a common repository - Each application doesn’t use all tables and relationships that are found in Siebel tools Configuration at each customer will determine the final use case © 2007 Princeton Softech, 15
Solution Goals 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 © 2007 Princeton Softech, 16
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 © 2007 Princeton Softech, 17
Current Practice? #1 - Clone Production #2 - Write SQL Repeat ? *%$! Clone Production Write SQL Request for Copy • Complex • Subject to Change Extract Wait After Production Database Copy Extract Changes After Changes Manual examination: Right data? What Changed? Correct results? Unintended Result? Someone else modify? © 2007 Princeton Softech, • RI Accuracy? • Right Data? Expensive, Dedicated Staff, Ongoing Responsibility. Share test database with everyone else 18
Conceptual Options Tables are Truncated, but database footprint still the same Database resized and re-indexed Production Database Production Clone Dynamically load relationally intact data set’s and objects based on selection criteria's © 2007 Princeton Softech, Reduced Clone Stage Resized Clone Training Test QA 19
Comparing Data © 2007 Princeton Softech, Compare the "before" and "after" data from an application test Compare results after running modified application during regression testing Identify differences between separate databases Audit changes to a database Compare analyzes complete sets data – finding changes in rows in tables Single-table or multi-table compare Creates compare file of results Displays results on screen 20
What about data privacy? Provide the fundamental components of test data management and enable organizations to deidentify, mask and transform sensitive data Companies can apply a range of transformation techniques to substitute customer data with contextually-accurate but fictionalized data to produce accurate test results By masking personally-identifying information, it protects the privacy and security of confidential customer data, and supports compliance with local, state, national, international and industry© 2007 based Princeton Softech, privacy regulations 21
De-Identifying Test Data Removing, masking or transforming elements that could be used to identify an individual - Name, address, telephone, SSN / National Identity number No longer confidential; therefore acceptable to use in open test environments Masked or transformed data must be appropriate to the context - Consistent formatting (alpha to alpha) - Within permissible range of values © 2007 Princeton Softech, 22
Transformation Techniques String literal values Character substrings Random or sequential numbers Arithmetic expressions Concatenated expressions Date aging Lookup values Intelligence © 2007 Princeton Softech, 23
Example: Bank Account Numbers First Financial Bank’s account numbers are formatted “ 123 -4567” with the first three digits representing the type of account (checking, savings, or money market) and the last four digits representing the customer identification number To mask account numbers for testing, use the actual first three digits, plus a sequential four-digit number The result is a fictionalized account number with a valid format: - “ 001 -9898” becomes “ 001 -1000” © 2007 Princeton Softech, - “ 001 -4570” becomes “ 001 -1001” 24
Example: First and Last Name Direct Response Marketing, Inc. is testing its order fulfillment system Fictionalize customer names to pull first and last names randomly from the Customer Information table: - “Gerard Depardieu” becomes “Ronald Smith” - “Lucille Ball” becomes “Elena Wu” - Optim ships with over 5, 000 male/female names and over 80, 000 © 2007 Princeton Softech, 25
Example: Addresses Direct Response Marketing, Inc. is testing its order fulfillment system Fictionalize customer addresses to pull an entire address from the Customer Information table: - “ 111 Campus Drive Princeton NJ 08540 ” becomes “ 1223 E. 12 th Street NY, NY 10079” - Optim ships with over 100, 000 valid CASS addresses © 2007 Princeton Softech, 26
Example: Intelligence Generating valid social security numbers (as defined by the US Social Security Administration) Generate valid credit card numbers (as defined by credit card issuers) Generate desensitized e-mail addresses Generate Email address based on format: name@domain © 2007 Princeton Softech, 27
Social Security Numbers and Credit Cards Production Database F. Name L. Name Credit Card# SSN# John Denver 5298774132478 855 254 -77 -6644 Vanessa Jones 4324115574123 154 -74 -7788 F. Name L. Name Test Database 654 Valid Credit Card# SSN# John Denver 5326458711 224956 854 -77 -6644 Vanessa Jones 4972584612 154 -74 -7788 Data before Masking Valid How are these numbers valid? 457744 For Social Security Numbers For Credit Card Numbers A Social Security Number (SSN) consists of nine digits. The first three digits is called the "area number'. The central, two-digit field is called the "group Number". The final four-digit field is called the "serial Number". All numbers must fit the latest available criteria for each section. Most credit card numbers are encoded with a "Check Digit". A check digit is a digit added to a number (either at the end or the beginning) that validates the authenticity of the number. A simple algorithm is applied to the other digits of the number which yields the check digit. © 2007 Princeton Softech, Data after Masking… Masked with Valid CC# and SS# 28
Using Custom Masking Exits Apply complex data transformation algorithms and populate the resulting value to the destination column Selectively include or exclude rows and apply logic to the masking process Valuable where the desired transformation is beyond the scope of supplied Column Map functions Example: Generate a value for CUST_ID based on customer location, average account balance, and volume of © 2007 Princeton Softech, 29
Implementation Time Line Project Team Activated Project Start IMPLEMENTATION PLANNING AND DISCOVERY Project Scope Analysis Identify • Application(s) Analyze • Infrastructure • Access • IT & Business requirements Processes • Application • Enhanced locations access Develop Define • Resource Plan • Retention • Training Plan policies • Project Plan • Archive location • Business objects Update • Resource plan • Project Plan © 2007 Princeton Softech, Project Delivered Design & Build Design • Architecture • Business objects Conduct • Team training Prepare • Environments • Test Plans Build (Optional) • Business Objects • Enhanced data access SUPPORT Testing Production Conduct • End user training Test • Archive • Data Access • Backup Prepare • Go live plan • Production Environment • Go Live • Recurring archive process Review Support Conduct • Project Review Provide • Value • User support measurement • Monitor Prepare • Maintenance • Success Story • Issue resolution 30
Questions & Answers © 2007 Princeton
- Slides: 31