Multi-Site Primary Care Practice Azure Data Warehouse Design & Implementation

Organization

Our client is a multi-site primary care practice founded in 1988, dedicated to improving people’s health and quality of life in Greater Atlanta Communities. They have state-of-the-art facilities across seven counties in Metro Atlanta and surrounding areas. With more than 70 providers and 200 medical staff members, our client provides complete medical care, including acute and chronic illness treatment, preventative health procedures, and diagnostic services.

CHALLENGE

Our client lacked a centralized, consolidated data store for reporting purposes. Reporting processes were performed manually using Python and PowerPivot, with data sourced from views that combined information from both the operational data store and historical data. These data sources were hosted on separate SQL Server instances in Azure.

The client faced challenges with data duplication between the historical data and operational data store, leading to reporting inaccuracies. End-users were unaware of these data quality issues until XTIVIA identified and brought them to light.

TECHNICAL SOLUTION

XTIVIA designed a target data model for a consolidated data store, integrating data from the existing operational data store and historical sources. The target database was implemented on a SQL Server virtual machine hosted in Azure. During the analysis phase, the XTIVIA team eliminated duplicate records while performing a one-time data load into the consolidated store. Additionally, data profiling was conducted to identify and address other quality issues, including completeness, validity, and format conformity.

The XTIVIA team developed an Azure Data Factory pipeline consisting of two components: one for truncate-and-load operations and another for incremental data loads based on the last updated timestamp. The pipeline is scheduled to execute every four hours. SQL stored procedures were utilized to implement the incremental load logic and log operational metadata, including job ID, start time, end time, rows read from the source, rows loaded into the target, and data comparison results.

XTIVIA assisted the client with configuring the Azure Data Factory and required connectivity to data sources and target. We assisted the client’s data SMEs in performing the data validation by comparing the data sourced using the current-state process and data sourced from the consolidated data store. The data mismatches were explained with the reason and how the new data store has accurate data compared to the current state data stores. Indexes were created where applicable and reorganized automatically after every load.

BUSINESS RESULT

Our client realized the following benefits from XTIVIA’s implementation of the Data Warehouse on Azure:

• A consolidated data store with improved data accuracy
• Improved user confidence and trust in the data
• Comparable or better performance of the reports

KEYWORDS
Azure Data Engineer, Azure Data Factory, Data Warehouse on Azure

SOFTWARE
SQL Server VM, T-SQL, Azure Data Factory, Azure Key Vault

HARDWARE/PLATFORM
Azure, SQL Server

XTIVIA needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime, read our Privacy Policy here.