240 likes | 368 Vues
Automated Claims Validation System. Prepared for: State of Indiana Department of Workforce Development 05/09/2011. Presented by Team One : Kiran Gadde Ken Johnson Chris Magdelinskas Michael Wulczyn. Overpayment Statistics. Causes of Overpayments. If the Claimant:
E N D
Automated Claims Validation System Prepared for: State of Indiana Department of Workforce Development 05/09/2011 Presented byTeam One: Kiran Gadde Ken Johnson Chris Magdelinskas Michael Wulczyn
Causes of Overpayments If the Claimant: • Has returned to work • Earned previous wages were lower than claimed • Received substantial severance pay • Is already collecting unemployment compensation • Has not met the eligibility criteria of the State • Has run out of unemployment benefits • Is not a legal resident of the state • Files a fraudulent claim
ACVS UI Changes Note: Changes are additive to ease adoption and reduce disruption to workflow
Data Movement and Schema Adaptability PowerDesigner • #1 Data Modeling tool on the market today • Highly intuitive interface • Reverse engineer a DB in 4 clicks • Supports over 100 databases Sybase Replication • Heterogeneous log based Replication • Is quickly reconfigured to adjust to source DB schema changes • Very robust and proven in multiple implementations
Sybase IQ Columnar Storage Fully Addresses ACVS Query and Load requirements: • Millions of rows per second load rates • Sub Second Response times with adhoc queries and multiple users • Enterprise class reliability : IRS, TransUnion, US Intelligence Community • Ease of administration and compatibility : ODBC, JDBC, SQL • Ability to operate on commodity hardware and operating systems • Partner Ecosystem: FuzzyLogix • Low TCO solution for present and projected requirements
Risk Mitigation Planning • Data sources may change over time • External stakeholders may not cooperate to the extent required • Potential for schedule over runs • End users may not embrace the system
Automated Claims Validation System Thank You! Kiran Gadde Ken Johnson Chris Magdelinskas Michael Wulczyn
By the numbers Unemployment Insurance Overpayments
Federal UI Improper Payment Data Source: US Department of Labor
Supporting Technical Slides Key Technology Attributes
Column Based Architecture Conventional Database • Data is stored horizontally • Querying without indexes and views is extremely I/O intensive • Building indexes and views is a huge time and resource drain • Database footprint must be dramatically expanded to make the environment efficient for querying • c1 • c2 • c3 • c4 • c5 • c6 • c7 • c8 • c9 • … r1 r2 r3 r4 r5 SYBASE IQ • Data is stored vertically – Each column is stored separately • The data is the index • Retrieve only columns used in the query – Reduce System I/O dramatically • Allocate a thread for each column individually – Process the query in parallel • c1 • c2 • c3 • c4 • c5 • c6 • c7 • c8 • c9 • … r1 r2 r3 r4 r5
Row vs. Column Storage Example Is gender a baseline factor in Indiana’s unemployment claims? How many females in Indiana have applied for unemployment insurance? Row-based Database 800 Bytes x 10M 16K Page Claim Filed = 500,000 I/Os Employed State M NY Y M CA N F CT N M MA Y M CA Y – – – 10M Rows • Process large amounts of unused data • Often requires full table scan 800 Bytes/Row 10M Bits x 3 Column/8 Bits 16K Page = 234 I/Os Column-based Sybase IQ Claim Filed Employed State M YIN M NIN F Y NY M Y CA 1 1 0 1 0 1 0 1 1 1 0 1 10M Rows + + 2 = 10M Bits
Summaries Aggregates 1-2 TB Same INPUT Data: “Conventional DW/ DBMS” is 3x-10x larger than IQ-M DW Indexes 0.5-3TB Base table (“RAW data”) (no indexes) Aggr/Summ: 0-0.1TB Indexes: 0.05-0.3TB 0.9-1.1TB Base table(FP):0.2-0.5TB TCO Differentiation: Compression 2.4 – 6.0TB LOAD INPUT DATA: 1TB -Source: flat files, ETL, replication,ODS 0.25 -0.9TB LOAD • Conventional DBMS • IQ Multiplex
Real World Compression Example Sybase IQ Data Compression: Saves money – Saves time 4x to 10x less storage