Data Source Design and Certification
© 2011 CapTech Ventures, Inc. All Rights Reserved. Legal Notices.
A national healthcare client assists employers and health plans by providing managed care solutions for costly and debilitating medical conditions and diseases. As a result, the client stores individual claims, membership, and provider information in order to track and report on patient’s care progress and the effectiveness of the service provided to the customer.
As part of an enterprise scale project aimed at aligning the sales, underwriting and delivery of Disease Management programs, the client needed to analyze new data sources for their inclusion in the Data Warehouse (DW) and implement certifications on existing data. Business conditions required an aggressive schedule, and the client requested expertise from CapTech’s Data Integration and Data Quality service offerings.
CapTech delivered source data profiling and source to target mapping for the Data Integration track. Claim, Membership, and Provider source file documentation was analyzed to determine its integration into the existing DW model. Sample data files were loaded in SQL Server using Informatica PowerCenter to confirm the analysis and map new code values to existing standards. This track culminated in obtaining Data Stewardship approval from the client, which allowed data model changes and ETL development to proceed.
CapTech's Data Quality solution implemented certification processes to quantify the quality and completeness of the data for the following subject areas: Claims, Membership, Provider, Case Activity, and Medical Management. This included checking for missing data, valid dates and codes, referential integrity, and other specific data value ranges identified by the client. Results were logged and prepared for comparison to data quality thresholds to determine its readiness for publishing.
CapTech overcame the challenges of implementing a large number of data certifications in a very compressed time frame by developing a metadata-driven process for both the development and testing phases. Microsoft Excel VBA was utilized to generate process metadata from the requirements documentation. Stored Procedures were developed to process this metadata and generate code in Stored Procedures to certify the data. The metadata was processed in a similar fashion to generate test data and execute much of the unit testing.