Data Warehouse Implementation Using Azure PaaS Helps a Healthcare Organization Improve Operational Efficiency

Business Need

The healthcare organization with 10 locations in Southern California has been investing in people to deliver quality patient care outside of the traditional medical setting for over a century. They provide innovative and effective programs and services through a network of locations for children, youth, young adults, and families facing serious health challenges.

Microsoft Excel was their primary source to collect survey data from their sources that ultimately helped transform HR, business payroll, workforce management, and talent management in a single cloud human capital management (HCM). They followed a manual and complicated process to analyze survey data. The reports generated were Excel charts that were static and non-interactive.

The healthcare organization needed an integrated business intelligence solution that would help them gather, store, and evaluate the data provided by their customers in the centralized reporting platform. They were looking to eliminate manual data preparations and introduce interactive data visualizations and dashboards.

The reporting system was mainly personalized/itemized based on different groups. They used 41 tools, and 45 distinct metrics. The healthcare organization partnered with Synoptek to have a unique way of reporting user engagement levels by capturing KPIs over a period of time through regular feedback and gain intel on how to better direct users on how to use the organization’s technology and systems. Consolidation of the collection function was required to reduce redundancy, increase collections, and streamline and standardize the collections process at a high level for efficiency and effectiveness.

Solution and Approach

The client had data spread across various lines of business systems – including payroll, finance, health records, budgeting, human resources, and recruiting. They had a strategic need to implement a robust and organization-wide business intelligence solution on top of these operational databases to facilitate effective monitoring of business processes, and efficient decision-making based on historical reports, performance KPIs, and target/goal tracking.

The client engaged with Synoptek for implementing a BI solution with a major focus on data visualization and dashboards. The solution was divided into two categories:

1. Supervisor dashboard
2. Clinician dashboard
3. Leadership dashboard

The solution included the following implementations:

1. Data warehouse solution using Azure cloud services (IaaS/ PaaS)
2. Power BI reports and dashboards

Data Warehouse Solution

Staging Database

From the Electronic Medical Record (EMR) data application, raw data from the source systems was extracted using Azure Data Factory pipelines/SSIS, and the data was staged in the staging databases at a defined frequency.

Data Cleansing and Transformation

Data cleansing, normalization, re-duplication, formatting, business rules, filtering, and data conversion were performed on the raw data from the staging database.

Data Model/Dimension Modelling

Considering the reporting requirements and source tables, data models were designed with Dimension and Fact tables and a star schema.

Data Marts

Data marts, serving as a set of logical domain data based on business areas/departments, were used to facilitate efficient data management and reporting. The data mart had data categorized into Fact and Dimension tables to be used in cubes. The data was categorized in different data marts, including HR, payroll, and finance.

Power BI Reports and Dashboards

Depending on the need for access security, user rights and row-level security on tabular data model and Power BI reports level was implemented to restrict data access for individual user roles.

Clinicians and supervisors could view their data or the data of the teams they supervise or manage. All reports/dashboards have row-level security implemented as defined during the requirements phase.

Governance

Implementing a data governance program involved defining the owners or custodians of the data assets in the enterprise. Three key initiatives were implemented during this phase:

a) Master Data Management (MDM)

• By creating and maintaining a single copy of master data, the client can ensure accuracy and preciseness of important information, such as client and product data.
• Data from all the systems was sourced into a staging database. Further, using data mapping and studying the schema from both the sources, MDM was implemented by having normalized database schema, which stores data in a common format from all the data sources.

b) Data Lifecycle

Data lifecycle describes how the data stored in the data warehouse is managed over time.

c) Data Quality

Data quality helps put in place a process for identifying and resolving problems with business data.

Helpful to have a vivid report that is clear and user-friendly; helps with supervising. We spend less time talking about productivity. I am learning to focus more on trends in performance.

— Client Supervisor

Business Intelligence Tools and Technologies

• Azure Data Factory
• Microsoft Power BI
• Azure SQL Data Warehouse
• Azure Analysis Services

Data sources: Netsmart (EHR) , Ceridian Dayforce, Paycom

I find it helpful in facilitating a conversation with my supervisor. It reduces my anxiety because I know where I am at.

— Client Clinician

Phase 2

As part of Phase 2, Synoptek enabled the following:

• Finance Reports and Dashboard
• Contract Monitoring Report
• Staff Productivity Report
• Claim dashboard
• Utilization by Cost Center
• Summarized view of Annualized Budget, Utilization and Forecast

Business Results

Technology has really been a key driver in value for the client. The BI solution we implemented, in combination with projects related to desktop/laptop refresh, the stabilization of the network, and a right sized support model has enabled the client to achieve higher revenue levels.

For the BI solution specifically, the client was able to achieve a lift in revenue of $2.5M. They did this by getting better insights into the utilization of their team processing key data elements and driving better performance.

The BI solution developed by Synoptek provided a range of data analytics and reports to analyze and manage operational data.

• In just two months, the client achieved significant improvements in timeliness of documentation which has substantial compliance and fiscal impact. The average number of days for documents to be ready reduced from 14.2 days to 6 days.
• Most of the transformation occurred within the culture and value towards using data; by the end of the pilot staff had become data savvy and were using their dashboards to be more targeted and productive.
• Dashboards, reports, and KPIs help in effective monitoring of functional performance, better ROI, and reducing effort spent on manual reporting.
• By leveraging the information collected in its database, the solution allows the customer to gain a deeper understanding of customer behavior. Supervisor and clinician KPIs help in closely monitoring productivity and timeliness. Overall, this has resulted in improved operational efficiency and better services to customers.
• With data warehousing, users and business leadership have access to data from multiple sources as needed. This way, only a small amount of time is spent on the actual retrieval process.

“Technology has really been a key value driver for our organization. The BI solution Synoptek implemented, in combination with the projects related to desktop/laptop refresh, stabilization of the network, and a right-sized support model has enabled our organization to achieve higher revenue levels. With the BI solution, we now get better insights into the utilization of our teams, quickly process key data elements, and therefore – drive better performance. The clinicians are the same, the managers are the same, the managers’ managers are the same, the clients are the same, the insurance carriers are the same…the only thing that has changed is the technology, and that has made all the difference”.

– Client CFO