Scroll Top

A Healthcare Agency Derived Actionable Insights and Made Informed Decisions by Leveraging 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:

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:

Technology Stack: