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 did not have a proper consolidated data store for reporting. The reporting was performed manually using Python and PowerPivot, sourcing the data from views that combine the data from the operational data store and historical data. These data stores were on two separate SQL Server instances on Azure. The client had duplicates between historical data and the operational data store causing reporting errors. Consumers of the data did not know the data quality issues until they were uncovered by XTIVIA.

Technical Solution

XTIVIA created a target data model for a consolidated data store that holds the data from the current operational data store and historical data. The target database is SQL Server VM hosted on Azure. During the analysis, the XTIVIA team removed the duplicates while performing a one-time load into the target data store. Data was profiled to check for other data quality issues such as completeness, validity, and format conformity.

The XTIVIA team created an Azure Data Factory pipeline; one for truncate load and another for incremental data load based on the last updated timestamp. The Data Factory pipeline is scheduled to run every four hours. SQL Stored Procedures were used to implement the incremental load logic, and log the operational metadata (job ID, start time, end time, rows read from the source, rows loaded in target, data comparison result, etc.).

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

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.