tech talk presentation

8/10/2019 Tech Talk Presentation

1/17

Oasis Team Insurance Case Study

Iago Bacurau1 & Joao Moura2 & Pedro Moreira3

W. P. Carey SCHOOL OFBUSINESS

December 3, 2014

[email protected]

[email protected]@asu.edu
http://find/


2/17

Introduction ER Model Business Rules Database Population Star Schemas ETL Process Remarks

Agenda

1 Introduction

2 ER Model

3 Business Rules

4 Database Population

5 Star Schemas

6 ETL Process

7 Remarks

Oasis Team Insurance Case Study Iago Bacurau & Joao Moura & Pedro Moreira W. P. Carey School of Business 2 / 17
http://find/


3/17


Introduction

Kimballs book Chapter 16;

He discuss homeowner, personal property and automobile insurances;We focused on automobile insurance;

The policies sold cover natural disasters, theft and accident;

http://find/


4/17


ER Model


d i d l i l b l i S S h k
http://find/


5/17


Business Rules I

1 A customer can have zero or more policies;

2 A vehicle must be owned by one and only one customer;3 Every vehicle must have one and only one model;

4 A policy must have a customer, a agent and a vehicle;

5 A customer must have a SSN, address and name;

6 A customer must have a phone;

7 An agent can have zero or more policies;

8 A customer can have zero or more cars;

9 An agent must have name and address;

10 A policy can have one or more coverage;

11 Customers can not share the same SSN;12 A model must have a brand;

13 An address must have a city;

14 A city must have a state;

15 A state must have a country;


I t d ti ER M d l B i R l D t b P l ti St S h ETL P R k
http://find/


6/17


Business Rules II

Customers can not share the same SSN;

Figure:SQL Test of the business rule


http://find/


7/17


Business Rules III

A customer must have a phone

Figure:SQL Test of the business rule


http://goforward/http://find/http://goback/


8/17


Database Population I

Some fake data were generated by Python and PHP based scripts. To generate

the database population based on SQL scripts we used the website Mockaroo.

Figure:PHP script for Policy Coverage


http://find/


9/17


Database Population II

Figure:Python script for Policy


http://find/http://goback/


10/17


Database Population III

Figure:Mockaroo website

http://www.mockaroo.com/Oasis Team Insurance Case Study Iago Bacurau & Joao Moura & Pedro Moreira W. P. Carey School of Business 10 / 17

http://www.mockaroo.com/http://www.mockaroo.com/http://find/


11/17

a a a p a a a a

Star Schema I

Our business process is about the policies sales.

We will analyze the policies sales bywhomsold andwherethe policies weresold.

Figure:Star Schema


http://find/


12/17

p

Star Schema II

The purpose of the Period Snapshot dimensional model is to evaluate both

amountand totalof all policies soldby each agent per month.The grain is one row per agent per month.

Figure:Period Snapshot Star Schema


http://find/


13/17

ETL Process I

For the first star schema the ETL process was done using a PHP framework

called Laravel.

Figure:ETL PHP code


http://find/


14/17

ETL Process II

For the Periodic Snapshot the ETL process was done via SQL script.

Figure:Resultant table of ETL Process

http://find/


15/17



16/17

Remarks

Initial ER model required modifications;

ETL process is the most complex step;

There are many different ways to generate fake data;


http://find/http://goback/


17/17

Questions

Comments

http://find/

tech talk presentation

Documents