Data Warehouse for Fertility Clinics
Services
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.
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.
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
Our contribution
Our team covered the following tasks:
- 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.
- Data warehouse development
At this development stage, our engineers build a data warehouse by utilizing Redshift Procedures covered by Glue Jobs.
- 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.
- 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:
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
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.
Contact Us
To find out more about Abto Software expertise, request a quote or get a demo of your custom solution.
