2023-03-03 Meeting Agenda

Owned by Nathan Southern
Last updated: May 11, 2023

Date

Attendees


Peter Antley 

Nathan Southern 

Jefferson Braswell 

Sean Bohan 

Chudwick, Susan R 

Mike Nurse

Dale Harris

Reggie Scarpa

Rambabu Adabala

Joseph Nibert

Hicham Bourjali

Michael Payne

Allen Thompson

Brian Hoffman

Patti Norton-Gatto

Mason Wagoner 

Kevin Petruzielo

Ken Sayers


This is a weekly series for The Regulatory Reporting Data Model Working Group. The RRDMWG is a collaborative group of insurers, regulators and other insurance industry innovators dedicated to the development of data models that will support regulatory reporting through an openIDL node. The data models to be developed will reflect a greater synchronization of data for insurer statistical and financial data and a consistent methodology that insurers and regulators can leverage to modernize the data reporting environment. The models developed will be reported to the Regulatory Reporting Steering Committee for approval for publication as an open-source data model.

openIDL Community is inviting you to a scheduled Zoom meeting.

Join Zoom Meeting
https://zoom.us/j/98908804279?pwd=Q1FGcFhUQk5RMEpkaVlFTWtXb09jQT09

Meeting ID: 989 0880 4279
Passcode: 740215

One tap mobile
+16699006833,,98908804279# US (San Jose)
+12532158782,,98908804279# US (Tacoma)
Dial by your location
        +1 669 900 6833 US (San Jose)
        +1 253 215 8782 US (Tacoma)
        +1 346 248 7799 US (Houston)
        +1 929 205 6099 US (New York)
        +1 301 715 8592 US (Washington DC)
        +1 312 626 6799 US (Chicago)
        888 788 0099 US Toll-free
        877 853 5247 US Toll-free
Meeting ID: 989 0880 4279
Find your local number: https://zoom.us/u/aAqJFpt9B

Minutes


I. Introductory Comments - Peter Antley

A. Welcome to Attendees

B. LF Antitrust Policy

II. Agenda

A. Decoding tables

  1. Working on these over last week
  2. Halfway through developing ETL & data layer
  3. PA: Pulled up a diagram to illustrate this
    1. Using code from GT2 - restricted however by its inability to offer in-line editing 
    2. Adding some features on top of this. 
    3. Taking edit package from GT2 and recycled UI & adding new features
    4. UI - uploading data + edit packages 
    5. GT2 & SDMA do not do decoding
    6. Utilizing GT2 as outbound table and loading into PA stat (stat table) STG  (stage)
  4. Spend week working on decoding tables - primary key, codes from stat plan and description, a value relating back to the code. (Codes listed in Peter's slide)
  5. Arkansas, PA & NJ - unique ways to do their coding - so to accommodate this Peter wrote ~5000 lines SQL. Wrapping up these tables today
  6. Mason is working on a simple script to load this table - so we can get into data testing before UI is ready
  7. PNC Stat View - utilizing a bunch of joins - foreign keys tied to codes. PA Policy view + PA Claim View. Will have all primary keys, codes and descriptions. 
  8. In querying table - it will have to do all kinds of operations before it loads
  9. Reporting Layer - will only have two tables - will be a decoded stat plan that has original codes + 2 tables personal auto policy/claim materialized view
  10. No blockers - should be pulled together next week. Then documentation will follow.

B. Questions regarding this

  1. Mike Nurse
    1. Looking holistically at all lines of business - any idea how many tables overall? From a maintenance perspective - a lot of work, no?
    2. PA: most of stat plans are ~ same length. Most of stat plan values are codes, and every code we have needs to be decoded. 
    3. PA: Current stat plan hasn't changed in stat time. Want to make additive changes w/o breaking changes
  2. Ken Sayers
    1. Maintaining tables - how will this be done? Broken down into smaller pieces logically? Time managed? Should not be constant, correct?
    2. PA: More info to come on this in the near future
    3. MN: There may be more changes than we anticipate. MN: w/his team, data changed in multiple locations = multiple efforts
    4. KS: Some of maintenance done automatically - script can be run routinely so not a great deal of hands-on work
    5. JN: Planning on using flyway that maintains whole history of scripts that are run - a way to ensure that databases are identical. Versioning, updates etc. all maintained in the tool.
    6. PA: for now we must simplify decoding tables - one table for code. 
    7. PA: many codes are specific to stat lines - other codes will be shared/recycled wherever possible to economize.
  3. Rambabu Adabala
    1. How is carrier data maintained tabularly - 
    2. PA: each carrier has their own HDS with corresponding tables
    3. PA: Data will follow idl message format - then check pkgs to make sure data is valid. Will change data from a positional string to a columned basis
  4. Dale Harris 
    1. Will tables work in Snowflake?
    2. DH: PA: yes although the extraction pattern will differ a bit 
    3. PA: possible (maybe) to write the extraction pattern generically and give it more versatility
    4. PA: Exploration may need to be done re: running it in snowflake, though on many levels this would be a strong fit.
    5. PA: Amazon Aurora serverless may be the best solution... but could create other issues
    6. PA: group can workshop this in the forthcoming AWG.

C. Modifications to the above table discussed with Peter, Mason & team, and agreed on.

D. Looking ahead - PA

  1. Will keep moving forward as planned
  2. Hope to see an extraction pattern running and executing off of tables by next Friday (3/10) but definitely by 3/17/23



Discussion items

TimeItemWhoNotes




Action items

  •