MS Hurricane Zeta POC Architecture Discussion (KenS)
ETL Discussion (PeterA)
AOB:
Future Topics:
IWG: Senofi's work looking at the Blockchain Automation Framework and why they went with Fabric Operator.
Notes:
ETL Discussion (PeterA)
From policy and claim data, dta calls can be answered, reports can be created
12 months ago utilizing Mongo (which doesn't do joins)
Used node.js to decode stat messages, make the human readable so business users can work with them
EPs on single table design too much, changed data store to relational DB, settled on postgres
Postgres 15 - latest stable version, standard to styart with
More resilent load instead of recoding
putting together how to make load happen, edit packages
before HDS need to load it in and run edit package and make sure data is good before we load the dfata
PA's proposal:
take multi line stat data and load it
table called AAIS stat AU fo auto (personal and commercial)
mature form, instead of going outside of GT2, do editing inside
taking GT2 code, stand up ETL platform, take test data, do some hello world analysis
final form - get error correctedwhere data meets error threshold, approve for submission and moved into HDS
tables used around personal auto
stat table has coded policy and claim (P&C)
will require 22 reference tables
vw: pa stat_vw view for policy AND view for claim
policy data, + code+id's + description
views materialized
pre=aggregates data, dont deal with
are we using common canonical standards? comes im as stat data and run rules
decoded and readily available
Goal - AIS - 2 week sprint - get as much built as possible, updating EP
GT2 on the agenda for next AWG
Schema Migration Procedures as a future topic
Joseph:
good exp with flyway - db migration and versioning control for DBs
create sql scripts, give file name, flyway will load them in order, if someone changes scripts flyway does checksum, way to have master management for db table
serialization of DDL? Yes
well known package - good addition
KS - put in the ref imolementation, governance function to be consistent, want to get all onto the same page
FZ
governance and requirements
MS Hurricane Zeta POC Architecture Discussion (KenS)
project working with MS Emergency Management and a large carrier and third party to solve diff problems
understand requirements that arent stat reporting
prove openIDL can be used to repro hurrican zeta data call
use large carrier data, create report that matches
AAIS would host node for POC
POC will show
first part not big reach - subset of stat plan, live production data, repro production report
openIDL can provide on demand reports across carriers related to insurance coverage for affected properties - way to connect insurance of properties to those that are damaged
same data used for openIDL can be used to gather pictures and extra info from policies
Participants
AAIS
Senofi
Carrier
Crisis Track
openIDL Testnet
Loading data into HDS
using address for some functionality, esp for damage to a specific property
property ID # introduced (CrisisTrack, only they would know)
enough info to ID address and damage lookup, not enough to know what address really was
before data can be loaded into HDS, make sure front end process to clear the address, exactly what returning damage info for
how many and which are damaged is the base report
concern about exposing info
hard to fault regulator wanting to understand the risk and whats covered with a catastrophe coming, but need to better understand the data flows
Without address, the counts could be interesting depending on how resolved
pockets where one carrier is majority is a problem
same constraints on carrier info exposed, not too much from one carrier, etc.
consent
other considerations
industry does this and state says "70% of properties has insurance, 30% dont", then pull in mortgage info
see the need
JB - only reg would see this, but protecting that, excellent and timely for benefits of openIDL - great application
KS
any desire to participate let KS know
Is crisis track charging? KS believes yes, no specifics yet
North Dakota
ran report for Jan for insured VINs vs registered VINs
46% of registered VINs were insured
some data quality issues
21% of VINs submitted were not registered (maybe out of state, formatted poorly, bad VINs, etc.)
offered to carriers to tell them which VINs not registered, telling each carrier which are not registered
interesting feedback loop
data quality issues, matches not happening
which VINs are not registered, tell them what they can do
will run again in March for Feb data and April for March data