Customer Experience: Building an Oracle Data Warehouse

1. Customer Experience: Building an Oracle Data Warehouse Argonaut Insurance http://www.argonautinsurance.com by ADW team Saqib Mausoof Raimundo Reyes

2. Content • Introduction • Argonaut Profile • Argonaut Business Highlights • Argonaut Systems • Driving Factors • Business Users & Data • ADW Tool Set • Oracle Pure*Extract • Oracle Designer • Oracle Discoverer • Oracle Warehouse Builder • Oracle Database • Oracle 9i AS • Oracle JDeveloper 9i • Future & lessons • Conclusion

3. Argonaut Profile

4. Argonaut Business Highlights • In Menlo Park since 1948 • Revenues 400 Million + • Employees 900+ • Customers include • Major electronic manufacturers • Telecommunication • B2B and B2C • Semi conductors • Wrap up construction projects • Ball parks & sports arena • public and urban transports hubs • Competitors • Kemper • Liberty Mutual • Zurich Insurance • AIG

5. Argonaut Systems • Other than Regular Comp (OTRC) Mainframe • Cobol based transactional system • Argonaut Information System (AIS) on 8i/AIX • Oracle 6i forms based OLTP system • Oracle Financials 11i on Oracle 8i/AIX • GL, AP and Cash Management • Financial Intelligence • STG’s Renaissance 5 • Account Receivables on 9i/AIX • Salesforce CRM • ASP based per user license • Argo Online extranet • Oracle 9iAS • Argonaut Data Warehouse & Business Intelligence (ADW) • Saetl 2-way 2G IBM AIX Model 80 • Saprod 4-way 4G IBM AIX Model 80 HA • EMC Symentrix 1.5 TB • Oracle 8i/9i • 9i AS Rel 1

6. Driving Factors for ADW • ’De-install mainframe’ to reduce operational costs of COBOL systems • Provide a more productive reporting environment for knowledge workers • Utilize Information Technologies like CRM, data mining and business intelligence to remain competitive

7. Business Users • Actuarial • Statistical Analysis • Data Mining • Risk Assessment • Accounting • Financial Analysis • SEC reporting • Marketing • Sales and New Business acquisition • Underwriting • Premium estimation • Loss Ration Analysis • Reinsurance • Dividends • Claims • Reserve allocation & analysis • Claims analysis by state

8. Business Data • Facts • EAP Estimated Annual premium • UP Unearned Premium • EBNB Earned But Not Billed • EP Earned Premium • WP Written Premium • Billed Premium • Loss Ratio • Incurred Loss • Dimensions • Customer • Policy • State (Policy and claim) • Broker/Agent • Line Of Business • Policy Type • Profit Center • Region/Office • Snapshot Date • Policy Inception date

9. ARGONAUT DATA WAREHOUSE FEED ADW OLAP Financial Intelligence Ext Tables Daily YTD migration Daily snapshot Monthly snapshot Daily ORA-STG 2003 -> OWB SQL OPE ORA-FIN 2002 -> AIS SNAPS 1995 – > AIS ORACLE DATA 1991 – > OTRC MAINFRAME DATA 1962 – Current

10. ADW BI Framework

11. ADW Toolset • Data Modeling – Oracle Designer 6i • Database - Oracle 8i/9i • MVS gateway – Oracle Pure*Extract (Carleton’s Passport) • ETL – Oracle Warehouse Builder • Project Mgmt/Facilitation – MS Project Website • Reporting – Oracle Discoverer 4i & Oracle Reports • OLAP – Cognos PowerPlay, 9i OLAP • Application server – 9iAS • Portal – Evaluating Cognos Upfront, Oracle Portal • Development tools – 9i JDeveloper, PL/SQL Developer

12. Oracle Pure*Extract Pros • Allows different formats for Source Files • VSAM, DB2, IMS • DASD, Sequential Tape/Cartridge • Able to handle Packed (COMP) decimals & OCCURS clause • Able to handle Variable-length records • Direct access of source files on mainframe • Allows multiple source and multiple target datasets Concerns • Concerns about future support (future integration with OWB expected) • Metacenter Manager and Application Builder are separate applications • No integration with OWB or Designer repository Products Evaluated • Informatica, Data Junction, Data Mirror

13. Oracle Pure*Extract COBOL Copybook Oracle DDL Script OPE Legacy Data COBOL Code and JCL Mainframe ASCII Data SQLLoader Control Oracle Oracle Table

14. Oracle Pure*Extract Application Builder

15. Oracle Designer 6i Pros • Metadata (Designer's data) is stored in a repository in an Oracle database • Designer takes care of all your development needs - data modeling, function modeling, site-specific server implementation (including stored PL/SQL) and development. • Models are maintained in common repository Concerns • Migration of 6.0 repository 6i is cumbersome • ER models can’t be exported in other formats like HTML, PDF or XML for sharing with users • Products Evaluated None

16. Oracle Warehouse Builder (9 iDS) Pros • Generates standard PL/SQL code for fine tuning • Fully integrated with Oracle 9iDS and 9iAS suites • Easy to learn following industry standard user interface • Seamless integration with Oracle database • Discoverer EUL can use OWB repository Concerns • Only supports Oracle databases as target • Does not share common repository • Scheduling is cumbersome and requires OEM which is not DBA friendly as it distributes secure access • OWB runtime has to be installed on individual target schemas (test, stage, target, etc.) • Key lookup function is weak compared to industry leaders (i.e., does not deal with duplicate keys) • OWB Aggregator has bugs when an update to the mapping is made, this anomaly has not been resolved even in the latest release. Products Evaluated Informatica, Data Junction, Data Mirror, Cognos Decision Stream

17. Oracle Warehouse Builder

18. Oracle Discoverer 4i (9iDS) Pros • Easy connectivity to Oracle databases • Easy to learn • Seamless integration with the Oracle database security • Fully integrated with Oracle 9i AS and Oracle Apps Concerns • Creation of sub-report not possible • Web edition is not very flexible • Drill through sheets have to be formatted every time • Desktop version can only be installed into oracle DEFAULT_HOME Products Evaluated Cognos Impromptu

19. Oracle 8i/9i Databases Pros • Supports External tables • Partitioning for efficient indexing and data retrieval • Local vs global indexes for DW (local better for high # of rows) • Materialized views (Claims & Premium star schemas ) • OLAP and data mining functions (Actuarial) • Multi-table inserts (ETL) • Upsert and Merges • Autonomous distributed transactions (used in AR) Concerns • Prudent to wait for 9i Release 2 before migrating business critical databases. In test instance Products Evaluated None

20. Oracle 9i AS Application Server Pros • Integrated with Oracle database and applications • Supports J2EE and EJB • Efficient web cache • OID and single sign on solution for AuthZ and AuthN Concerns • Licensing issues regarding portal ownership • Jinitiator increases footprint of thin client Products Evaluated None

21. Oracle 9i JDeveloper Pros • Integrated source control w/Oracle Software Configuration Manager • UML Modeling (not found in Forte or Sun ONE) for classes, workflows, activities, relations, and relation views • Wizards to quickly prototype and build web services and enterprise applications for deployment to Oracle 9i Application Server • Code insight for Java, JSP, and XML files Concerns • Portlet development not supported until next release/version of JDeveloper • Not easy to upgrade to newer versions of the J2EE (i.e., from J2EE 1.3.1 to J2EE 1.4 when released) Products Evaluated Sun One, Forte

22. Future • 9i OLAP • No separate DB. • SQL & Java beans access • Aggregation – non additive • Regression & forecasting • YTD calculations • 9i data mining Integrate data mining within DB for faster reads and incorporate DM4j components of JDeveloper. • Classification • Clustering • Decision trees • Market Basket Analysis • 9i compression Compress snapshots data that is seldom used. • 9i ETL engine Table functions to avoid staging tables, transportable tablespaces, resumable statements & parallal execution • 9i Streams Once OLTP moves to 9i, plan on using oracle streams for real time DW. Streams read OLTP log file • 9i SSO and OID Integrated with 3rd party LDAP, including Active Directory and database security.

23. Conclusion . . . • Wrap up • Q & A • Thank you!