2023-03-13 Architecture WG Meeting Notes

Owned by Nathan Southern
Last updated: May 11, 2023

Date

ZOOM Meeting Information:

Monday, March 13, 2023, at 11:30am PT/2:30pm ET


Join Zoom Meeting

https://zoom.us/j/7904999331

Meeting ID: 790 499 9331

Attendees:

  • Sean Bohan (openIDL)
  • Mason Wagoner (AAIS)
  • Joseph Nibert (AAIS)
  • Dale Harris (Travelers)
  • Ken Sayers (AAIS)
  • Yanko Zhelyazkov (Senofi)
  • Satish Kasala (Travelers)
  • Peter Antley (AAIS)
  • Faheem Zakaria (Hanover)
  • Tsvetan Georgiev (Senofi)
  • Ash Naik (AAIS)
  • Dhruv Bhatt (Hartford)



Agenda:

  • Update on ND POC
  • Update on openIDL Testnet (Jeff Braswell)
  • Update on internal Stat Reporting with openIDL (Peter Antley)
  • Update on Infrastructure Working Group (Sean Bohan)
  • MS Hurricane Zeta POC Architecture Discussion (KenS) 
  • Architecture Decision Capture Process (KenS) - Discussion
  • HDS architecture decisions (PeterA) - Discussion
    • Schema Management via Excel vs SQL and Flyway
    • Database vs Data warehouse
    • Expiration dates on codes
    • mutable vs immutable codes, (why would we recycle a code)
  • openIDL contributor engagement (James Madison) - Discussion
  • AOB:
  • Future Topics:

Notes:


TimeItemWhoNotes




Documentation:

Notes: (Notes taken live in Requirements document)

  • ND POC (KenS)
    • Second data call today
    • All carriers have data in 
    • Second report for VINs not registered
  •  openIDL Testnet (JeffB)
    • pickup in action: attempts to get carriers on board
    • coord and orchestration
    • contributor engagement
    • Hanover - map data to auto stat plan format
    • Travelers & Hartford
    • coming weeks and months: significant progress
    • work together on mult fronts
  • Internal stat reporting (PeterA)
    • decode tables, artifacts, schema management
    • updates - JamesM requested a detailed presentation on data layer - March 27
    • how can we offload some of the work onto business poeple and changing dimensions over time if at all
    • phys join of tables, claim and policy views, modeling of steps
    • expanding documentation significantly
    • every other month since june is settling into mature
    • decode -stat plan and personal auto - foundation done this AM
    • carriers - need to put stat data into HDS, produce stat records per stat handbook
    • only need to load stat records and can start reporting
    • (peter screenshare)
    • https://semver.org/
      https://github.com/openidl-org/openidl-main/tree/etl-pka-flyway/openidl-hds-ddl/ref/pa/tables
    • https://github.com/openidl-org/openidl-main/blob/etl-pka-flyway/openidl-hds-ddl/ref/pa/tables/V0.0.1.6__pa_stat_stg.sql
    • utilizing JS to write out sql, sql scripts managed by flyway
    • work with proper keys for joins
    • Dale - James thought we might put results in excel so excel tab for each table so tables create definitions
    • documentation - practice of updating data model
    • coverage codes - json, states, can upgrade json from excel, converted from json to excel, config doc + js file
    • tools - bolt ons on top of db
  • Joseph: FlyWay
    • <demo>
    • containerization from dev side
    • FlyWay - great db management tool, as a container
    • FlyWay has versioning schematic - they went with semantic version
    • maintains version control
    • containers will make it easier
    • operationally what the data model will drive
    • educating carriers to do this
    • ran earlier, can stop it, migrates all of these
    • can do it in source control
    • Flyway effectively does checksum on every file, maintains data integrity
    • parallel for EP schema? 
    • EP written by dev, could set up repo and put in flyway to keep EPs, but each EP should be kept in Git somewhere, not re-ran, on ledger best ref point
    • 2 parts - creation of EP schema, other is
  • Mason analytics
    • peter task: write simple sql tables for lines of business
    • get idea of where we are, what completed, map out getting tables up and running will look like
    • personal auto, went thru, 33 tables needed to bring over to use
    • GT2
    • most tables from statplan directly
    • some stat plans have mult lines of business
    • some repeatability in categories, found things in stat plan, not which need table to be integrated
    • more breakdown of how each stat plan is looking to keep account of
    • 201 tables out of all stat plans 
    • completed 99 of 201
    • view of how this is looking for timing and expectations
    • code tables - take codes and give description
    • for each code that comes in we need a ref table, and we need a way to decode, load, make script for each table and view, put in flyway, allow easy managed db across all these areas
    • codes across diff business lines the same? very few to none
    • none can be skipped w/o thorough review
    • coverage codes - lot of copy-paste
    • will speed up a lot as they get going, not as much overlap as you think
    • transaction code would have secondary key as line of biz? 
    • opportunity to do analysis across lines of business
    • look out for it in a few places, def opportunity for cross-lines
    • have to get to next level beyond what Mason showed - data dictionary for all of them
    • intern request
    • can do it for tables named the same across lines, see where they lined up
    • version zero - can change anything we want and what a good releaser looks like
    • goal for this week - get auto coverage report gen again based on high end ETL decode job
  • Architecture Decision Capture Process (KenS) - Discussion

Recording: