Data Warehouse for Fertility Clinics

Custom cloud-based data warehouse for business process automation
Industry:

Services

Data warehouse development
Custom software development
Data protection
Cloud migration
Database optimization
Data source integration
1

Project overview

Abto Software was approached by a US company, which helps medical practices to optimize the process of providing full cycle fertility services (clinical, operational and administrative). 

The company’s expertise includes:

  • The facilitation of cooperation between clinics
  • The introduction of modern technological solutions for administration and accounting
  • The facilitation of collaboration with third party providers, including pharmacies and laboratories
  • The purchasing of equipment, consumables, insurances, and more 

Our client has felt the need for a custom-built, reliable data warehouse, which would provide the company’s employees with real-time quality data and reporting. Until then, all data was stored in the product system and required manual processing, which was rather inefficient and created business risks.

Cooperating with Abto Software has resulted in the successful separation of sensitive business data and implementation of a modern, cloud-based data warehouse. 

2

Main goals

The project’s main goals can be reduced to the following:

  • DWH development
    To provide the client company with a single source of truth, we developed a cloud-based data warehouse, which now provides for seamless analytics and reporting.
  • Business automation
    To automate time consuming processes associated with financial reporting, we prepared data marts, which help save man-hours and cover reporting faster.
3

How the solution works

We delivered a cloud-based data warehouse providing for efficient collection and integration of information associated with customer management, accounting and marketing processes, and related business modules. The solution is a highly reliable, flexible and cost-efficient platform, which provides real-time insight.

 

The collected data includes:

  • Appointment data
  • Medical data
  • Financial data about transactions and refunds
  • Business analytics
4

Our contribution

Our team covered the following tasks:

  1. Data ETL
    At this project stage, our team:

    • Extracted the .bak and .trn files from the external storage to the AWS S3
    • Moved and restored extracted .bak and .trn files in the MSSQL Server
    • Exported data from the MSSQL Server
    • Loaded data to the AWS Redshift stage layer

    For fast data integration, we utilized Glue Jobs using Python.

    For regular data extraction, we utilized Step Functions as a workflow orchestrator.

  2. Data warehouse development
    At this development stage, our engineers build a data warehouse by utilizing Redshift Procedures covered by Glue Jobs.
  3. Reporting layer development
    We prepared data marts and aggregates to provide convenient reporting. The client has access to the tables in AWS Redshift, which can be worked with using Microsoft Excel. To simplify data access, we integrated Azure AD by utilizing AWS SSO. The user can access necessary information aggregated in AWS Redshift through an account in Azure AD.
  4. Platform support
    At the very moment, the client is already successfully utilizing the custom-built data warehouse. In case discrepancies occur, our specialists are there to provide immediate support. 

Here you can have a look at the DWH architecture:

abto software
5

Main challenges

Main challenges faced during the project:

  • For the software development, our team had to dive into the specifics of the stored data and the business from the inside, which required some dedication and time
  • Due to data sensitivity, our engineers had only limited access to the source information

Tools and technologies

Tech stack:

  • Python

Tools

  • AWS S3
  • Microsoft SQL Server
  • AWS Lambda
  • AWS Redshift
  • AWS Glue Jobs
  • AWS Step Functions
  • AWS SES
  • Amazon EventBridge
  • Azure AD

Timeline:

  • January 2022 – June 2022

Team:

  • 1 Delivery Manager
  • 1 Data Architect
  • 1 Data Engineer
  • 1 DevOps engineer
6

Value delivered to business

Our team successfully delivered a custom-built DWH, which provides for real-time quality data and reporting. With the new DWH, the client is no longer depended on time-consuming manual work and can now rely on the collected data.

The benefits achieved with the new data warehouse:

  • Versatile architecture
    The client is a company providing services to professionals specializing in fertility support, which means they’re owning multiple subsidiaries. Thanks to its very versatile architecture, our solution can be utilized by multiple companies, despite the database differences.
  • Resource efficiency
    Our team has automated various processes, including reporting. If before accounting specialists and managers had to spend about 3-4 days to finish the reporting, now the whole process takes even less than 2-4 hours. 
  • Quality data
    Our engineers have developed an additional data quality control subsystem – a framework for uploading platform inspections, which is completely integrated into the regular dataflow. As soon as the control system detects discrepancies in the stored data, it informs responsible employees for taking further action.
  • HIPAA compliance
    The client provides services to clinics dealing with large amounts of sensitive medical data. To ensure the safety of collected personal data, we delivered a solution, which is HIPAA compliant.

Category:

Contact Us

To find out more about Abto Software expertise, request a quote or get a demo of your custom solution.

  • Clicking this button, I agree to the processing of my personal data.