customer experience building an oracle data warehouse l.
Skip this Video
Loading SlideShow in 5 Seconds..
Customer Experience: Building an Oracle Data Warehouse PowerPoint Presentation
Download Presentation
Customer Experience: Building an Oracle Data Warehouse

Loading in 2 Seconds...

play fullscreen
1 / 23

Customer Experience: Building an Oracle Data Warehouse - PowerPoint PPT Presentation

  • Uploaded on

Customer Experience: Building an Oracle Data Warehouse . Argonaut Insurance by ADW team Saqib Mausoof Raimundo Reyes. Content. Introduction Argonaut Profile Argonaut Business Highlights Argonaut Systems Driving Factors Business Users & Data

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

Customer Experience: Building an Oracle Data Warehouse

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
customer experience building an oracle data warehouse

Customer Experience: Building an Oracle Data Warehouse

Argonaut Insurance


ADW team

Saqib Mausoof

Raimundo Reyes

  • 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
argonaut business highlights
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
argonaut systems
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
driving factors for adw
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
business users
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
business data
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



Financial Intelligence

Ext Tables


YTD migration

Daily snapshot

Monthly snapshot



2003 ->





2002 ->


1995 – >


1991 – >


1962 – Current

adw toolset
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
oracle pure extract
Oracle Pure*Extract


  • 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 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
oracle pure extract13
Oracle Pure*Extract

COBOL Copybook

Oracle DDL Script


Legacy Data

COBOL Code and JCL



SQLLoader Control


Oracle Table

oracle designer 6i
Oracle Designer 6i


  • 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


  • 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


oracle warehouse builder 9 ids
Oracle Warehouse Builder (9 iDS)


  • 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


  • 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

oracle discoverer 4i 9 i ds
Oracle Discoverer 4i (9iDS)


  • Easy connectivity to Oracle databases
  • Easy to learn
  • Seamless integration with the Oracle database security
  • Fully integrated with Oracle 9i AS and Oracle Apps


  • 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

oracle 8i 9i databases
Oracle 8i/9i Databases


  • 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)


  • Prudent to wait for 9i Release 2 before migrating business critical databases. In test instance

Products Evaluated


oracle 9i as application server
Oracle 9i AS Application Server


  • Integrated with Oracle database and applications
  • Supports J2EE and EJB
  • Efficient web cache
  • OID and single sign on solution for AuthZ and AuthN


  • Licensing issues regarding portal ownership
  • Jinitiator increases footprint of thin client

Products Evaluated


oracle 9i jdeveloper
Oracle 9i JDeveloper


  • 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


  • 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

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

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