This project was to integrate Chronic Conditions Warehouse (CCW) Medicare claims and member data obtained by NYDOH from the Research Data Assistance Center (ResDAC) into the Member, Issuer/Plan, and Claims consolidated ODS. The overarching project includes the expansion of the data model to accommodate information included in the CCW files that is not available from other sources. The member data files will be used to derive Issuer, Plan, and Member Coverage records. The Medicare Claims Files (HHA, Hospice, Inpatient, Outpatient, SNF, Bcarrier, DME, Member) are loaded into Stage 1,2 and 3. Loading of historical data. The data had to be extracted, cleansed, validated, and transformed into the Oracle Database. Data Profiling and comparison of data profiles for the data across different years.

The Vendor Load Management (VLM) team deals with the acquisition of data (From Vendors – Claims, Lab Results, Provider Roster, etc. and From the State – Claim History, Member Transition, HRA, etc.) The Types of files that our team would deal with are EDI (for vendor encounter submission, Flat Files, MS Excel Spreadsheets). Our team would first load the data to a staging database (VENDORLOADMGMT) and then load it to the target database (ODW).

The CDJ report is used to capture all cash disbursements, interest, capitation, adjustments, refunds, and voids for encounters and value-added services. It will be used to reconcile the detailed claims data reported by the vendors in their ANSI ASC X12N 837 Health Care Claims transactions for professional, dental, and institutional claims verifying all encounters have been reported and that the total paid amount is consistent with the vendor invoice.

The objective of the project was to design and develop a new data warehouse that will replace the current Enterprise Data Warehouse. The ETL Process involved extraction and migration of the data from DB2, SQL server, sales force, and Flat files, implementing the business logic, and populating the data into the target DataMart. This project consists of three stages - staging, dimensional, and fact.