Achieve Siebel Excellence Best Practices Solution in Archiving and Test Data Management Northern California OAUG, Traini

1. Achieve Siebel ExcellenceBest Practices Solution inArchiving and Test Data ManagementNorthern California OAUG, Training DayJanuary 2007 Stephen Mohl Siebel Specialist

2. 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?

3. 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

4. What is Enterprise Data Management?

5. 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.

6. Comprehensive Performance Management Strategy Tune System Add Capacity Reconfigure Application Train Users Archive Outdated Data

7. 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 • Combined Reporting of active and inactive data

8. 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/Auditing

9. Siebel Mobile Applications Local DB Design PST Runtime Archive File Directory Archive Files PST CX_Table Integration Schematic Siebel On Premise Applications Siebel Teller Applications Siebel Portal Framework Multiple Client Device Support Sync Web Server Development Environment Siebel Tools Siebel Application Server User Interface Services Business Logic Layer and Core Service Bus. Process Siebel Repository EAI Data Layer Services Federated Data Sources OLTP Files Siebel Universal Customer Master Oracle PSFT OLTP Legacy JD Edwards

10. 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’

11. CUSTOMERS CUSTOMERS -- ---- ---- ---- ------- ------ ---- ---- ---- ------- ---- -- ---- ---- ---- ------- ------ ---- ---- ---- ------- ---- ORDERS ORDERS -- -- ------ -- --------- ------ -- ------ -- --------- ------ -- ------ -- --------- ------ -- ------ -- --------- ---- -- -- ------ -- --------- ------ -- ------ -- --------- ------ -- ------ -- --------- ------ -- ------ -- --------- ---- DETAILS DETAILS -- ---- ---- ---- ------- ------ ---- ---- ---- ------- ------ ---- ---- ---- ------- ------ ---- ---- ---- ------- ------ ---- ---- ---- ------- ---- -- ---- ---- ---- ------- ------ ---- ---- ---- ------- ------ ---- ---- ---- ------- ------ ---- ---- ---- ------- ------ ---- ---- ---- ------- ---- Archive Process Production DB Archive Purge Optim Server Storage Archive File Directory Archive Files Restore Access Staging Area

12. Optim Engine Archive Template Template Template Archiving Process Flow Production OLTP OLTP Support for: • Databases • Oracle, Sybase, Informix, DB2, UDB, SQL/Server • Complex interrelationships • Applications • Custom & Packaged, Legacy, Oracle E-Business Suite, PeopleSoft Enterprise, JD Edwards EnterpriseOne, Siebel, Amdocs CRM • Multiple, interrelated applications, databases & platforms • Templates • Define a business object • Created from schema RI, Erwin import, GUI or a combination • Multiple DBMS support • Out of the box • Siebel 7.5, 7.7, & 7.8 • Establish policies • Constraints or condition checks, used to determine eligibility • Time or other parameters provided at run time • Optim Archive • Compressed • Secured • Indexed retrieval • Widest selection of Information Lifecycle options • SAN, NAS • Nearline (Centera, RISS, DR550, Intellistore) • Offline (Tape, CD, Optical) • Enterprise Vault, Tivoli • Industry standard archive • Does not require a DBMS • Does not require the application (Decommissioning) • Cannot be altered • Allows for deferred purge operation • Auditable • Proves data archived is identical to purged data • Allows for user review prior to purge • NT, Solaris, AIX, HP/UX • OS/390, z/OS • Archive while online for 24x7 operations • Access DB2 from Unix • Place DB2 Archive on Unix

13. Ensure Referential Integrity Ex: Activities Archive File

14. Enterprise Test Data Management

15. 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

16. 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

17. 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

18. Clone Production • Complex • Subject to • Change Request for Copy Extract Wait After Production Database Copy Changes Extract After Changes Manual examination: Right data? What Changed? Correct results? Unintended Result? Someone else modify? Expensive, Dedicated Staff, Ongoing Responsibility. • RI Accuracy? • Right Data? Current Practice? #1 - Clone Production #2 - Write SQL Repeat ?*%$! Write SQL Production Database Copy Share test database with everyone else

19. Conceptual Options Tables are Truncated, but database footprint still the same Production Database Production Clone Database resized and re-indexed Reduced Clone Resized Clone Training Stage Dynamically load relationally intact data set’s and objects based on selection criteria's QA Test

20. Comparing Data • 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

21. What about data privacy? • Provide the fundamental components of test data management and enable organizations to de-identify, 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-based privacy regulations

22. 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

23. Transformation Techniques • String literal values • Character substrings • Random or sequential numbers • Arithmetic expressions • Concatenated expressions • Date aging • Lookup values • Intelligence

24. 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” “001-4570” becomes “001-1001” Example: Bank Account Numbers Complexity 1

25. 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 last names Complexity 2

26. 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. 12th Street NY, NY 10079” • Optim ships with over 100,000 valid CASS addresses Complexity 3

27. 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 Complexity 3

28. Social Security Numbers and Credit Cards Production Database Data before Masking Test Database Valid Valid Data after Masking… Masked with Valid CC# and SS# How are these numbers valid?

29. 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 transaction activity Complexity 4

30. Project Start Project Team Activated Project Delivered SUPPORT IMPLEMENTATION PLANNING AND DISCOVERY Project Scope Analysis Design & Build Testing • Identify • Application(s) • Access requirements • Application locations • Develop • Resource Plan • Training Plan • Project Plan Production • Analyze • Infrastructure • IT & Business • Processes • Enhanced access • Define • Retention policies • Archive location • Business objects • Update • Resource plan • Project Plan Review • Design • Architecture • Business objects • Conduct • Team training • Prepare • Environments • Test Plans • Build (Optional) • Business Objects • Enhanced data access Support • Conduct • End user training • Test • Archive • Data Access • Backup • Prepare • Go live plan • Production Environment • Go Live • Recurring archive process • Conduct • Project Review • Value measurement • Prepare • Success Story • Provide • User support • Monitor • Maintenance • Issue resolution Implementation Time Line

31. Questions & Answers