Transcript Customer Experience: Building an Oracle Data Warehouse Argonaut Insurance http://www.argonautinsurance.com by ADW team Saqib Mausoof Raimundo Reyes 11/5/2015 Content Introduction Argonaut Profile Argonaut Business Highlights Argonaut Systems Driving Factors Business Users & Data ADW Tool Set        Oracle.

Customer Experience:
Building an Oracle Data
Warehouse
Argonaut Insurance
http://www.argonautinsurance.com
by
ADW team
Saqib Mausoof
Raimundo Reyes
11/5/2015
1
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
11/5/2015
2
Argonaut Profile
11/5/2015
3
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
11/5/2015
4
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)





11/5/2015
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
5
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
11/5/2015
6
Business Users
Actuarial



Statistical Analysis
Data Mining
Risk Assessment
Accounting

Financial Analysis
SEC reporting

Sales and New Business acquisition

Marketing
Underwriting




Premium estimation
Loss Ration Analysis
Reinsurance
Dividends
Claims


11/5/2015
Reserve allocation & analysis
Claims analysis by state
7
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
11/5/2015
8
ARGONAUT DATA WAREHOUSE FEED
Ext Tables
Daily
SQL
Financial Intelligence
Daily
OWB
ADW
Daily snapshot
Monthly snapshot
YTD migration
OPE
OLAP
ORA-STG
2003 ->
ORA-FIN
AIS SNAPS
2002 ->
1995 – >
AIS ORACLE DATA
1991 – >
OTRC MAINFRAME DATA
1962 – Current
11/5/2015
9
ADW BI Framework
11/5/2015
10
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
11/5/2015
11
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
11/5/2015
12
Oracle Pure*Extract
COBOL
Copybook
Oracle DDL
Script
OPE
Legacy Data
Mainframe
ASCII
Data
Oracle
COBOL Code
and JCL
SQLLoader
Control
Oracle Table
11/5/2015
13
Oracle Pure*Extract Application Builder
11/5/2015
14
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, sitespecific 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
11/5/2015
15
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
11/5/2015
16
Oracle Warehouse Builder
11/5/2015
17
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
11/5/2015
18
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
11/5/2015
19
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
11/5/2015
20
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
11/5/2015
21
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.
11/5/2015
22
Conclusion . . .
Wrap up
Q&A
Thank you!
11/5/2015
23