Healthcare
Data Engineering
About the Client
The client is a full-service healthcare staffing agency and franchising organization providing medical and non-medical homecare ranging from infants to seniors and supplemental care staff to their corporate customers. The organization encompasses a unique healthcare franchise and two assisted living franchises. Headquartered in the Chicago area, the client has over 360 franchised and company-owned locations nationwide.
With its three premier services, the client provides the tools and support required to provide quality care and lucrative investment opportunities to the franchises. The organization operates under multiple revenue streams enabling franchises to uncap earning potential. Over the last two decades, the client has expanded from a local business to a national brand providing medical and non-medical homecare to its clients 24/7.
Business Challenge
The client is committed to providing high-quality quality care to the communities they serve through its franchises. The organization is experiencing an accelerated growth period, with 17 franchisees expanding their footprints into 28 new territories across the US. With such important expansions, apt record and information/data management with regard to franchises was imperative.
With changing times, the client aspired to modernize its on-premise data warehouse. However, data silos posed a significant challenge. The organizational data mining and amalgamation method needed more structure, uniformity, documentation, and standardization. Unstructured data were manually entered into systems. The data needed to be pulled from multiple sources, processed, loaded, and analyzed.
The client needed to extract terabytes of data relating to employee information, franchise performance, and staffing skillsets, to name a few. Thus it aspired to build an advanced data warehousing solution that was:
Robust
Reliable
Scalable
Additionally, the client sought a single source of truth to be accessible from several of their internal APIs.
Trigent solution
Being a forward-thinking company, the client desired to make informed, data-driven decisions. It partnered with Trigent to design an integrated solution that provided a uniform and structured view of all the franchises. Trigent’s data engineering experts understood the current architecture, data flow between systems, and issues faced. Trigent participated in a rigorous knowledge transfer and understood the client’s overall portfolio and operations to design an architecture that addressed its present and future needs.
Data Pipelines
Data was collated from several existing sources, such as master files, ABS Database, and Great Plains database, leveraging Restful APIs and processed to address issues with regard to structure, availability, and accuracy.
Trigent’s team of seasoned experts developed the architecture leveraging automated Microsoft Azure and Azure Data Factory (ADF). This enabled the client to have an ELT pipeline with incremental loading.
ADF was the pipeline orchestrator used to land data into the data lake. CI/CD approach enabled the developers to work on a shared repository of code. The automated build process for quick detection of error codes resulted in a faster development lifecycle and a lower error rate. Data lake was created to store a single source of truth. The centralized data repository could be accessed directly by the applications. Azure Data Lake Storage Gen2 was leveraged to organize the data and have granular access controls. The data was segregated into hot/recent, cool/cleansed, and archive/raw data tiers.
Cloud Warehouse Solution
Trigent leveraged Azure SQL (serverless) to build the warehouse solution for cost optimization. The SQL Server Management Studio was used to configure, manage, and administer all components within Microsoft SQL Server. Terabytes of data were hosted and processed. Moreover, highly performant queries were run in parallel to capture, store and analyze voluminous data and deliver real-time insights. The processed data then underwent dataset standardization and was bifurcated into different dashboards or data marts such as finance, customers, franchises, and employees, to name a few. The team also developed a data dictionary with regard to every dashboard. Parquet was leveraged to build flat columnar storage data formats.
Conceptual Architecture
In addition, Trigent put a Quality Assurance cycle in place wherein all Stored Procedures (SPs) were thoroughly tested. The team re-wrote the erroneous SPs and removed existing bugs from the system. The new SPs created in the warehouse were more straightforward, easy to understand, and logical. Moreover, infrastructure provisioning in Azure was done by leveraging Bicep. CI/CD was employed for ADF and for DB.
The robust system enabled the client to generate customized reports with regard to location-specific performance and employee database, to name a few, and make informed decisions. Power BI was used to generate customized reports. Furthermore, the team facilitated the audits of the process with logs of all ongoing activities.
Client Benefits:
The robust solution enabled the client to:
- Process large amounts of data without delay and maintain 100% accuracy while processing the data.
- Develop the capability to quickly recognize the performance of several locations and make informed decisions, thus earning an approximate revenue of more than $4,000,000 from its top quartile franchises.
- Apart from informed decisions, the USP of this entire exercise was in enabling the client to take actionable insights to derive the best out of this exercise.
Technology Stack:
