About the Client
Founded in 1990, the client is a laboratory sciences company based in Nashville, Tenn., that provides science-driven testing and consulting services for clients such as healthcare providers, pharmaceutical companies, professional and amateur sports organizations, leading college and university athletic programs, Fortune 500 corporations, and government agencies throughout the United States.
The client used commercially available software products for managing business operations. IntergyTM from Greenway Health was used as an EHR and practice management solution, while McKesson’s Horizon LabTM laboratory information system (LIS) was used for managing patient test results.
The two systems did not have any integrations to each other, and the client faced challenges in extracting reports related to its Account Receivables (AR) from the Healthcare Payers such as Blue Cross Blue Shield, United Health etc. It took the client several days to download data in excel sheets, run the analysis and determine the extent of AR.
At the same, approx. 6000 new lab tests and results were flowing in daily across the several labs operated by the client. Overwhelmed by the effort needed to run the AR reports, and the need to keep up with the near constant flow of test results, the client realized a need to revamp the entire process and use technology to help solve the problem.
They were looking for data platform experts to help create a common data management platform for staging the data from both systems, clean the data and create a data warehouse and finally build a business intelligence and reporting landscape for better insights and intelligence.
The client was very clear that the solution had to be architected and deployed in the Cloud. With deep expertise in cloud enabled data management and analytics, Silicus was the client’s preferred technology partner.
Cloud Platform Evaluation
The client’s in-house team was familiar with Oracle data management solutions, since Horizon LabTM was built on Oracle. The rest of the client’s IT infrastructure was on Microsoft Azure cloud. There was a decision to be taken between using Oracle Cloud vs Microsoft Azure. Silicus was able to show Azure’s cloud services superiority – subscription models, PaaS features etc through proof of concepts (PoCs).
With Azure finalized as the Cloud platform, the client’s next requirement was to leverage as many out-of-the-box features and services offered by Azure. They did not want to get into manually provisioning and managing VMs, engineering high availability and resiliency etc. They wanted a “BI in the Cloud” platform with minimal management overhead. Silicus architected and deployed the data management platform in Azure, leveraging PaaS components.
Architecting the Data Flow
The challenge was to absorb data in different formats from the in-house systems and bring them to a common staging point.
Silicus built a data extraction service that would run daily in the early hours and move data from IntergyTM and HorizonTM to Azure Blob storage. Azure Blob storage was leveraged for this since it was cost effective and versatile.
The data is then moved from Azure Blob storage to a staging area within Azure SQL data warehouse. The data warehouse was architected leveraging the Kimball dimensional schema. Parallelly, a copy of the same data is made in Azure Data Lake for persistent storage, Azure blob storage was configured to persist data for a limited timeframe, after which it gets overwritten.
Azure data Lake is cost effective, and can store data of any size, shape, and speed, and do all types of processing and analytics across platforms and languages. It removes the complexities of ingesting and storing all of data while making it faster to get up and running with batch, streaming, and interactive analytics.
From Azure SQL Data Warehouse, SQL scripts would run relevant queries, and present the insights and intelligence via Power BI dashboards and reports.
PolyBase for Big Data Processing
Azure SQL Data Warehouse was selected for storing the processed data in preparation for reporting and analytics. While Blob storage was the ideal staging point for data, the sheer volume, variety and velocity of data presented a data processing challenge. Silicus leveraged the out-of-the-box PolyBase feature to load and process large data sets from Azure Blob Storage, in a scale-out fashion for better data loading performance, taking the process from hours down to minutes. PolyBase allowed the system to process large external data sets in a parallel processing architecture.
Reporting and Visualization
Data processing was done on Azure SQL Data Warehouse to create the data format as per requirements. Power BI reports would execute at 8 AM every day and reports / dashboards would be available on with key decision makers showing the latest status on AR, updated to the previous closing of business operations.
Azure Blob storage, Azure Data Lake
Azure SQL Data Warehouse
Uncovered AR of $590 MN within 1 week of Go-Live
When deployed, the solution helped uncover the true extent of the Account Receivables (AR) the client was owed by the large Healthcare insurance companies. For the first time, the client was able to get an accurate report showing their AR was $ 590 million Dollars!! The report even showed which were the healthcare payers that owed them the most money.
Project completed in 3 months
Silicus architected and delivered the solution in just 3 months. This sort of turnaround was unheard of in data platform development projects. Our expertise and experience helped the client gain insights and intelligence on their performance in record time.