US Health Care Insurance Provider
A healthcare payer engaged krtrimaIQ to streamline inbound and outbound information sharing in healthcare specific formats and develop a data analytics solution for improving operational efficiencies and customer experience.
SQL_Server
SQL_Server_Services
qvera
python
powerbi
tableau
Contents
  • Objective
  • Business Impact
  • Solution
  • Technical Architecture
  • Results
To improve the operational efficiency in Health insurance

Objective

A US health care insurance provider has established contractual agreements with a diverse group of 12 vendors, providing vital services such as claims processing, telemedicine consultations, and member onboarding. The organization faces a significant operational challenge involving the daily generation, upload, and download of approximately 100 files. This task is crucial for ensuring seamless service delivery while adhering to strict CMS guidelines. Initially, 60 percent of the file generation, uploading, and downloading processes were manually executed. In response to this challenge, krtrimaIQ was engaged to develop an automated solution capable of efficiently handling the creation, download, and upload of a multitude of files, in alignment with the intricate requirements of both vendors and CMS.

Business Impact

The payer moves around 100 files a day to and from 12 vendors handling claims, telemedicine and member onboarding, and CMS rules govern the format of most of them. Six in ten of those file movements were being done by hand.
  • The daily file traffic runs itself. Generation, upload and download happen on a schedule instead of off somebody's checklist, hourly or monthly as each vendor requires.
  • Vendor formats built to specification. The standard healthcare exchange files, 834, 837I and 837P, are produced to each vendor's own requirements and checked once loaded.
  • Nothing moves untracked. What went where and when, visible to business users in Power BI, the trail a CMS audit expects to follow.
  • One warehouse behind about 30 reports. Roughly 120 tables were restructured into a star schema, so analysis stopped running against the systems doing the day's work.

Solution

The client's challenges were systematically addressed through the following key solutions:
Automation of File upload and download
  • Implemented a highly configurable SSIS framework to automate the upload and download processes.
  • The framework supports flexible scheduling, allowing for hourly, weekly, monthly, or specific day-of-the-month execution.
  • Employed logical grouping via independent SQL jobs, with 12 distinct groups managing the diverse file requirements for the insurance provider.
Creation of vendor specific EDI Files (834,837I,837P)
  • Utilized Qvera to generate vendor-specific 834 files, loading 834, 837I, and 837P data into the database.
  • Developed six files in adherence to the unique specifications of the six vendors.
  • Conducted sanity data checks after loading 834 and 837 files into the database.
Creation of CSV, Fixed width, pipe delimited, tab delimited files
Implemented SSIS packages to automate the generation of four categories of files.Insurance provider generates files into 4 categories
  • Member or eligibility files
  • Provider files
  • Medical claims files
  • Pharmacy claims files
Auditing file movement to and from insurance provider
  • Developed a comprehensive framework that tracks all files moving to and from the insurance provider.
  • Created Power BI reports to enable business users to monitor file movement effectively.
Creation of Data Warehouse
  • Implemented a data warehouse to address the analytical requirements of the insurance provider.
  • Formulated a resilient star schema data warehouse model, complete with ETL pipelines facilitating daily data refresh. This model seamlessly incorporates approximately 120 tables and millions of records sourced from the operational database.
  • Transitioned the operational database, boasting around 120 tables and millions of records, from a de-normalized structure to a star schema, featuring six Dimension tables and a single Fact table.
  • The data warehouse currently sustains a suite of approximately 30 Power BI reports catering to diverse business user needs.
Technologies Used :
SQL_Server, SQL_Server_Services, Qvera, Python, Powerbi, Tableau

Technical Architecture

image

Results

The implemented solutions resulted in the complete automation of file generation, uploading, and downloading processes, addressing the needs of the insurance provider. Vendor-specific EDI files were generated according to specifications, and a robust data warehouse was created to fulfill the reporting requirements of the insurance provider. The overall outcome enhanced operational efficiency and streamlined the organization's interactions with vendors and compliance with CMS guidelines.
Contact Us