Data Warehouse Core Common Models Progress and Future

Data Warehouse Core Common Models: Progress and Future Direction Jim Tepin

Health and Human Services Data Warehouse Redevelopment Project

HHS Data Warehouse Redevelopment Project ● Best Practices o Data Audit Trails; Common Tables; Physical Data Model Standards; Person Matching; Address Cleansing ● Common Standards o Physical Data Base Design & Security Role Standards ● High-Level Architecture o (including) Statewide Central “Lookup” Database ● Data Sharing / Central Views / Audit Compliance o Security Architecture Design ● Common Models – Address, Citizen, Events

Gartner State of Michigan Data Warehouse Strategy

Target State 5 DRAFT Infrastructure: DW Architecture (Cont’d) Option 1 – Single Unified Data Warehouse Data Sources Agency 1 Apps. Agency 2 Apps. Subject A Agency N Apps. External Sources Subject B OLTP Apps. Subject C Foundation Layer § Single unified Data Warehouse for all participating ETL Departments / Tools Agencies BI needs § Follows the best practice hybrid model § Nothing bypasses the Foundation Layer § Logical View Data Mart Optimization Layer § No Department / Agency versions of data or independent data marts are part of this End Users For internal use of State of Michigan only. © 2006 Gartner, Inc. and/or its affiliates. All rights reserved. 29 September 2006 Page 5

Target State 5 DRAFT Infrastructure: DW Architecture (Cont’d) Option 2 – Multiple Data Warehouse Data Sources Each Data Warehouse includes common data that is acquired independently Agency 1 Apps. Agency 2 Apps. Agency 1 Agency N Apps. External Sources Agency 2 OLTP Apps. Agency N § Shared Data Warehouse Infrastructure for those who elect to use it – slight variation of status quo Department/ Agency ETL § Data warehouses remain completely under the control of each Department / Agency Dimensional views of Department / Agency Data Warehouses End Users For internal use of State of Michigan only. © 2006 Gartner, Inc. and/or its affiliates. All rights reserved. 29 September 2006 Page 6 § Data sharing is achieved on a Department / Agency to Department / Agency basis

Target State 5 DRAFT Infrastructure: DW Architecture (Cont’d) Option 3 – “Master” Data Warehouse Data Sources OLTP Apps. External Sources Agency 1 Apps. Agency 2 Sources Agency N Apps. ETL Processes Master Data Warehouse Subject A Agency 1 Subject B Agency 2 § Master Data Warehouse contains a subset of common data identified as being widely useful For internal use of State of Michigan only. © 2006 Gartner, Inc. and/or its affiliates. All rights reserved. Agency 3 29 September 2006 Page 7 Agency N+1 Example Department / Agency Data Warehouses

Target State 5 Infrastructure: DW Architecture (Cont’d) DRAFT Option 3 – “Master” Data Warehouse Challenges: Strengths: n Deciding what should be included in the MDW is n Provides Department / Agency control n For the defined subset of State-wide data a single foundation data model supports consistent results (a single version of the truth) n Provides for sharing of the most widely needed data n Provides a moderate degree of reuse and leverage of the technology infrastructure and staff n Potentially lower total cost of ownership than Option 2 very challenging AND this will change over time causing rework n Provides NO WAY to guarantee consistent results across all Departments / Agencies as there are no built-in controls to ensure the shared data source is used n Adding additional data types and relationships can n n n For internal use of State of Michigan only. © 2006 Gartner, Inc. and/or its affiliates. All rights reserved. be complex, costly and slow A centralized data warehouse team must be created to manage the Master Data Warehouse 360 degree view of citizens and resulting outcome analysis may only be partially supported Limited consistency of results and measures across Departments / Agencies achieved Substantial redundancy of technologies, tools, staff and data acquisition through duplicated effort Substantially larger total cost of ownership than Option 1 Potential single point of failure 29 September 2006 Page 8

DRAFT Common Address Model For internal use of State of Michigan only. © 2006 Gartner, Inc. and/or its affiliates. All rights reserved. 29 September 2006 Page 9

HHS Common Address Prototype Addresses across agencies (CSES, DCH, DHS, Judicial) were gathered, analyzed and cleansed. Results: • Total Records: 132. 6 million • Unique Raw Records: 34. 2 million (74% reduction) Lansing Subset: • Total Records: 2. 3 million • Unique Raw Records: 575 thousand (75% reduction) • Unique Cleansed Records: 158 thousand (93% reduction) Reductions above are based on record counts. A common model can also employ various technical means consistently (I. e. compression) to conserve disk space

Common Address Model - Goals ● Data Architecture o Common location of both raw and cleansed addresses. o Secure o Central/Common Orientation ● Process Architecture o Simple Integration o “Open” o Leverage Available Tools ● Compliance o HHS standards compliant o Audit compliant

Common Address Physical Model – P_SOM_COMMON

Common Address Physical Model – P_SOM_LOOKUP

P_SOM_Lookup Database ● Common Area for System Codes & Values ● Common Area for Federal Standards Codes (FIP, NAICS, etc. ) ● Great Starting Point for Enterprise DW

Common Address Physical Model – P_SOM_Control

Common Address Demonstration

Common Address – Internal Processing

Common Address – On the Horizon ● Integration with Common Citizen ● Security Mechanisms ● IQ 8 – Delivery Point Validation

Common Citizen Model

Common Citizen - Physical Model

Citizen Events Overview: ● Merged View of Various “Events” ● Very Extensible (i. e. date of birth) ● Tend to be the relationship of a person, an organization and a time element. ● Can be “one-time” or over a duration. Intent: ● Micro-analysis. ● Macro-analysis.

Citizen Events Model

Citizen Event Sample Event Description Begin Date End Date Completion Description UOM Amount Description 2 UOM Amount 2 Quarterly Wage Record 2001 -07 -01 2004 -03 -31 Reported Earnings Wages Paid 85. 75 ? ? Quarterly Wage Record 2002 -01 -01 2002 -09 -30 Reported Earnings Wages Paid 284. 93 ? ? SSI Rate Change 2005 -03 -30 2005 -06 -10 Application for SSI is pending Gross Payment Quarterly Wage Record 2005 -04 -01 2005 -09 -30 Reported Earnings Wages Paid SSI Rate Change 2005 -06 -10 2006 -05 -20 SSI application denied Gross Payment 0 Current Payment 0 RSDI Rate Change 2005 -11 -03 9999 -12 -31 Disallowed claim Gross Payment 0 Net Payment 0 SSI Rate Change 2006 -05 -20 9999 -12 -31 Closure of SSI record Gross Payment 0 Current Payment 0 0 948. 17 Current Payment ? 0 ?

Citizen Events On the Horizon ● ● Prove the concept. Integrate with Common Address Establish Security Architecture Business Intelligence Competency Center