Developing a data warehouse for US healthcare provider

In this case look-through, we will have a quick look at a recent project for a healthcare provider struggling with the optimization of its patients’ database and perceivable lack of business intelligence. Our experts took over the development of a data warehouse, which resulted in the availability of regular business intelligence reports (once an hour invariably).

Our client is a healthcare provider based in the US. The company is providing podiatry specialists who have special knowledge and experience in treating foot diseases.

The company is specialized in preventive foot care and treatment of disorders already identified. Its customers lean back on their own couch while trained medical professionals take care of their foot health.

Our client is dealing with a large amount of historical patient data that has to be regularly proceeded. This data includes the personal information of patients, their digital medical records, treatment/billing history, and more. Due to huge amounts of data to be regularly processed, the client was facing the challenge of comprehensive, advanced reporting. The generation of up-to-date advanced reports is both time and resource-consuming, therefore executing this process in production causes a high-performance risk considering the data volumes.

Our team has built a custom data warehouse to provide advanced reporting. Today, the healthcare provider successfully generates advanced business intelligence reports by demand.

In 2020, Abto Software took over the development of a data warehouse for a healthcare provider. The diagram shows the high-level architecture of the solution developed:

The team, provided by Abto Software, used the AWS platform for data warehouse development and hosting. For smart data storage, our specialists have used AWS Redshift. AWS Glue was chosen for further data ETL. Step Functions, also an AWS tool, were used as a workflow orchestrator.

The client decided to use Google Studio as a BI tool. The experts, provided by Abto Software, developed a set of data connectors to make the tool work with the developed data warehouse.

Let’s have a look at the main benefits of the developed DWH.

Main benefits of the built DWH:

1. Patient analytics

Thanks to the built data warehouse, the company is able to get to know its clients better in just a few clicks. Accurate analytics help in understanding the client’s preferences and segregate client groups.

2. Fully automated, up-to-date reporting

Thanks to the designed data warehouse, our client has access to precise, up-to-date reports. The business intelligence information that is relevant for the provider is updated once an hour invariably. Our client used to generate advanced reports manually. This process is completely automated now.

3. Appointment analytics

Appointment analytics is one of the main advantages of the developed DWH. Thanks to our team, the US healthcare provider can now easily analyze patient journey.

The information that might be accessed includes the following data:

• The frequency of appointments (the number of days between treatments)
• The duration of appointments
• Information about rescheduled or canceled appointments
• Information about the reasons for rescheduling or canceling

4. Website visitors’ and patients’ behavior tracking

The opportunity to analyze the behavior of users is another major advantage of the developed DHW. With the help of the system, the US healthcare company can make substantiated conclusions about the behavior of website visitors and patients.

The information that might be accessed includes the following data:

• The frequency of appointments (the number of days between treatments)
• Time required for engagement (the number of days between patient profile creation and engagement)
• LTV or Lifetime Value (the profit a company’s client brings during the entire time of cooperation)
• CR or Churn Rate

5. Accounting statistics

The DWH contains not only information about patients and appointments, but also financial information. Thanks to up-to-date reporting, the company’s accounting department can draw comprehensive conclusions about the company’s spending and profits, as well as make precise forecasts for the nearest future to make budget planning more efficient.

6. Sensitive data protection and HIPAA compliance

The DWH contains only anonymized data, which is enough for the generation of reports. The DWH is therefore HIPAA complied.

1. Minimized load on the product system

2. Minimized amount of work performed manually to generate comprehensive reports

3. Efficient analytics
The DWH is running sophisticated calculations to provide the required analytics. The customer’s product system, completely based on MySQL, isn’t able to cope with such complex calculations and such large volumes of data.

4. Up-to-date reporting
The DWH gets new production data once an hour invariably. This means the business intelligence reports contain data, which is one hour old maximum.

Speaking about the challenges, it should be said that there haven’t been any issues related to the project’s technical side. Our experts build a data warehouse that regularly downloads data from the product database and generates comprehensive reports for more efficient analytics.

Data warehousing for healthcare: Main trends and forecasts

Reducing the large workload of clinicians will surely be an important trend in the healthcare industry in the coming years. According to several studies, overwhelmed doctors and nurses can get twice more spare time thanks to the automation of certain work processes. Much faster data processing and smarter storage usage will provide for faster analysis of patient data. And, as a result, medical personnel will be more focused on the quality of patient care.

Another trend to mention is also the use of cloud data storage. In the coming years, the medical records of patients will be embedded in mobile devices. Attending physicians will be able to easily receive up-to-date information about the current state of health of patients in a few clicks. Hence, patients will access high-quality care.

Data warehousing has great business value:

A DWH improves BI

Using different data sources for a data warehouse helps you collect more up-to-date data. In turn, this helps reduce the error rate. Business analysts get the ability to constantly correlate new data with previously collected data. This comparison helps leaders base their decisions on hard facts.

A DWH significantly improves the data quality and consistency

A database of consistent, up-to-date, and historical data improves the performance of business analysts. Consequently, leaders receive more accurate information about important business processes like accounting, for example. Consistent data collected from different departments helps in understanding trends. This allows recognizing mistakes and possible growth points.

High ROI

From a revenue point of view, data storage is expensive. But after a time, a corporate data warehouse can help a company grow exponentially.

More efficiently used time

A DWH allows leaders to access critical data from various sources in one place. This means a DWH helps to make important business decisions much faster.

The industry of healthcare is on the rise. The market is expanding, and the competition is growing accordingly. Those companies focused on constant growth must provide high-quality services. We are strongly convinced that introducing advanced technology is the best way to grow in today’s fast-paced world.

A DWH (data warehouse) is a complex data management system used to optimize internal business processes. A DWH is used to centralize and consolidate large amounts of data. This provides business owners with various growth opportunities. Accordingly, both the business and the client win.

As was mentioned above, in 2020, our team carried out a project for a healthcare provider. The system is still being actively used by the customer. Thanks to the collaboration, the company could optimize its internal business processes and become more efficient. And, as a result, the company was able to improve the quality of the services provided and attract more customers.

Do you need a data warehouse to cover your internal business needs? Please feel free to contact us for a comprehensive consultation!

What is a DWH?

A DWH is a complex data storage system for storing structured data. The DHW’s main task is the execution of high-speed queries necessary for faster and easier decision-making. Data warehouses have been used in numerous industries for decades. In fact, they have become the storage standard for business.

Data warehouses are mainly used for:

• Consolidation of structured data from many disparate sources
• Fast analytical queries from relational databases

How a data warehouse actually works?

With the help of data warehouses, companies can consolidate information into a single database. This allows business analysts to execute high-speed queries.

Here’s how it works from the technical side of view:

Step 1: Data extraction
The collection of data from multiple disparate sources into so-called intermediate databases.

Step 2: Data conversion
The transfer from the mediate database to the integration layer for aggregation and transformation into an operational data store (ODS).

Step 3: Data uploading
The transfer of data to the data warehouse.

Step 4: Scheme definition
The determination of a suitable scheme to be used for SQL queries.

What are the areas of application?

Before building a DWH, it’s important to figure out the exact type of queries that will be performed. To reduce the complexity of disparate data sources, a DWH can be segmented into data marts.

Common areas of application for corporate data warehouses:

• Online transaction processing (OLTP). For example, money transfers are executed on a high-frequency trading platform
• Online analytical processing (OLAP). Typically, analysts use OLAP to generate comprehensive business intelligence reports
• Predictive analytics. An OLAP system can be optimized to generate business scenarios

Corporate DWH: When to consider the development?

Developing a corporate DWH is a costly and challenging project. Despite this, the use of a custom DWH pays off by minimizing the risk of sensitive data loss.

A DWH is needed in the following cases:

1. The company uses external data sources. Patient notes, for example.
2. The number of used data sources exceeds 3-4. In fact, such a quantity is the norm of controllability.
3. There are several consumers of the same data. The DWH can be a source of information for an unlimited range of consumers.
4. The amount of the data collected exceeds certain given limits. For example, one of the leaders in BI, Power BI by Microsoft, limits a project to 100 GB of data for a Premium subscription.

What are the main challenges and pitfalls to consider before implementing a DWH?

1. High cost of deployment
2. Long terms compared with the implementation of a ready-made solution
3. Inefficient architecture when working with an IT team without the field knowledge and expertise needed for the project
4. Leakage and/or cyber attacks