Client Experience: Building an Oracle Data Warehouse

Presentation Transcript

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

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 9 i AS Oracle JDeveloper 9i Future & lessons Conclusion

Argonaut Profile

Argonaut Business Highlights In Menlo Park since 1948 Revenues 400 Million + Employees 900+ Customers incorporate Major electronic makers Telecommunication B2B and B2C Semi conductors Wrap up development ventures Ball parks & sports field open and urban transports centers Competitors Kemper Liberty Mutual Zurich Insurance AIG

Argonaut Systems Other than Regular Comp (OTRC) Mainframe Cobol based value-based framework Argonaut Information System (AIS) on 8i/AIX Oracle 6i shapes based OLTP framework 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 client permit 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 AS Rel 1

Driving Factors for ADW 'De-introduce centralized server' to lessen operational expenses of COBOL frameworks Provide a more beneficial reporting environment for learning specialists Utilize Information Technologies like CRM, information mining and business insight to stay focused

Business Users Actuarial Statistical Analysis Data Mining Risk Assessment Accounting Financial Analysis SEC reporting Marketing Sales and New Business securing Underwriting Premium estimation Loss Ration Analysis Reinsurance Dividends Claims Reserve designation & investigation Claims examination by state

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

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

ADW BI Framework

ADW Toolset Data Modeling – Oracle Designer 6i Database - Oracle 8i/9i MVS passage – 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 apparatuses – 9i JDeveloper, PL/SQL Developer

Oracle Pure*Extract Pros Allows diverse configurations for Source Files VSAM, DB2, IMS DASD, Sequential Tape/Cartridge Able to handle Packed (COMP) decimals & OCCURS condition Able to handle Variable-length records Direct access of source documents on centralized computer Allows various source and numerous objective datasets Concerns about future support (future joining with OWB expected) Metacenter Manager and Application Builder are separate applications No mix with OWB or Designer storehouse Products Evaluated Informatica, Data Junction, Data Mirror

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

Oracle Pure*Extract Application Builder

Oracle Designer 6i Pros Metadata (Designer's information) is put away in a vault in an Oracle database Designer deals with all your improvement needs - information displaying, work m odeling, webpage particular server usage (counting put away PL/SQL) and advancement. Models are kept up in like manner storehouse Concerns Migration of 6.0 vault 6i is awkward ER models can't be traded in different organizations like HTML, PDF or XML for offering to clients Products Evaluated None

Oracle Warehouse Builder (9 iDS) Pros Generates standard PL/SQL code for adjusting Fully incorporated with Oracle 9iAS suites Easy to learn taking after industry standard UI Seamless combination with Oracle database Discoverer EUL can utilize OWB archive Concerns Only backings Oracle databases as target Does not share regular storehouse Scheduling is lumbering and requires OEM which is not DBA well disposed as it conveys secure get to OWB runtime must be introduced on individual target compositions (test, arrange, target, and so forth.) Key query capacity is frail contrasted with industry pioneers (i.e., does not manage copy keys) OWB Aggregator has bugs when an overhaul to the mapping is made, this inconsistency has not been determined even in the most recent discharge. Items Evaluated Informatica, Data Junction, Data Mirror, Cognos Decision Stream

Oracle Warehouse Builder

Oracle Discoverer 4i (9 i DS) Pros Easy network to Oracle databases Easy to learn Seamless incorporation with the Oracle database security Fully coordinated with Oracle 9i AS and Oracle Apps Concerns Creation of sub-report impractical Web version is not extremely adaptable Drill through sheets must be arranged each time Desktop form must be introduced into prophet DEFAULT_HOME Products Evaluated Cognos Impromptu

Oracle 8i/9i Databases Pros Supports External tables Partitioning for effective ordering and information recovery Local versus worldwide files for DW (nearby better for high # of lines) Materialized sees (Claims & Premium star diagrams ) OLAP and information mining capacities (Actuarial) Multi-table supplements (ETL) Upsert and Merges Autonomous disseminated exchanges (utilized as a part of AR) Concerns Prudent to sit tight for 9i Release 2 preceding relocating business basic databases. In test case Products Evaluated None

Oracle 9i AS Application Server Pros Integrated with Oracle database and applications Supports J2EE and EJB Efficient web store OID and single sign on answer for AuthZ and AuthN Concerns Licensing issues in regards to entrance proprietorship Jinitiator expands impression of thin customer Products Evaluated None

Oracle 9i JDeveloper Pros Integrated source control w/Oracle Software Configuration Manager UML Modeling (not found in Forte or Sun ONE) for classes, work processes, exercises, relations, and connection sees Wizards to rapidly model and construct web administrations and undertaking applications for organization to Oracle 9i Application Server Code knowledge for Java, JSP, and XML documents Concerns Portlet advancement not upheld until next discharge/adaptation of JDeveloper Not simple to move up to more up to date forms of the J2EE (i.e., from J2EE 1.3.1 to J2EE 1.4 when discharged) Products Evaluated Sun One, Forte

Future 9i OLAP No different DB. SQL & Java beans get to Aggregation – non added substance Regression & anticipating YTD computations 9i information mining Integrate information digging inside DB for quicker peruses and join DM4j parts of JDeveloper. Arrangement Clustering Decision trees Market Basket Analysis 9i pressure Compress depictions information that is at times utilized. 9i ETL motor Table capacities to abstain from organizing tables, transportable tablespaces, resumable explanations & parallal execution 9i Streams Once OLTP moves to 9i, anticipate utilizing prophet streams for constant DW. Streams read OLTP log document 9i SSO and OID Integrated with 3 rd party LDAP, including Active Directory and database security.

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