Services
Platform and Technology Capabilities
Home / Insights / Case Study / Data Warehouse Implementation Using Azure PaaS Helps a Healthcare Organization Improve Operational Efficiency
Services: Data warehouse, Azure PaaS with Microsoft Power BI and Azure Data factory, Azure Analysis Service and Azure Data warehouse
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.
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:
The solution included the following implementations:
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, normalization, re-duplication, formatting, business rules, filtering, and data conversion were performed on the raw data from the staging database.
Considering the reporting requirements and source tables, data models were designed with Dimension and Fact tables and a star schema.
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.
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.
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:
Data lifecycle describes how the data stored in the data warehouse is managed over time.
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
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:
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.
“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