About the Client
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.
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:
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 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.
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.