upgrade IT infrastructure to support new market demands
address new and increasing customer requirements
make the company more agile and efficient
In traditional systems, RxSense would have needed to purchase more and more databases to keep up with expanding datasets and users. In addition, the limitations of traditional database architectures made each successive expansion progressively less effective.
To design a platform and analytic environment that delivers on the promise of expanded data access and insight, RxSense believed that it had to completely discard traditional data architectures like its existing Microsoft SQL solution and look at more flexible, affordable and scalable solutions based in the cloud.
Infostretch’s extensive digital skillset, and specific experience in healthcare was an important factor in the decision.
After working with the RxSense team, and taking into account all the factors involved, the Infostretch team recommended a cloud-based data warehouse and data analytics solution built on AWS and Snowflake.
Not only would this approach be able to handle and scale easily with RxSense’s growing data management needs, it would also enable RxSense to automate many of the manual business functions which were core to the problem.
The scope of Infostretch’s engagement included:
After completing the initial project discovery phase and a proof of concept, Infostretch laid out a five step execution plan for RxSense
Migration of Legacy ETL to Matillion Jobs
Migration of Data Analytics Reports
pointing to Snowflake
Automate Invoice Margin Calculation
Real-Time Data Ingestion into Data Warehouse
Providing Technical Support
Existing ETL jobs were being implemented using Microsoft SSIS and Informatica ETL.
With the new solution, all jobs had to be migrated to Matillion Jobs to extract, transform and load data into Snowflake. This included around 60+ jobs to be migrated to support different business functions.
Key steps included:
Approximately 25+ reports were developed to point to Snowflake Data Warehouse.
Core activities included:
Understanding existing queries used for reporting
Migrating queries to support Snowflake syntax
To date this had been a manual process – with contract rules maintained in Excel spreadsheets and invoice margins calculated by finance personnel for each contractor every 15 days. Infostretch developed a custom module using Matillion Jobs – automating the entire process from calculating the Pharmacy invoicing margin to sending invoices to contractors.
Developing the module included:
Understanding and incorporating invoice calculation rules
Design and implement the UI Portal to manage the rules
Prepare Snowflake queries to calculate invoice pharmacy rules the rules
Implementing Matillion Jobs the rules
As a result, there were no real-time updates being made to the data warehouse or in the reports. To mitigate this issue, Infostretch has reduced from daily transfers to twice an hour (or every 30 minute) jobs and is working on a proof of concept where Infostretch enables records to be streamed from RxSense’s transaction database system to a Kafka messaging system and ingested into the Snowflake data warehouse system.
This phase involved:
Implementing CDC mechanism
Configuring Source Kafka Connector the rules
Implementing transformation jobs the rules
Configuring Sink Kafka Connector – Snowflake the rules
Technical Achievements & Outcomes
50% revenue growth within first 12 months
Reduced customer on-boarding time – from 90 days to a few hours
Increased automation, agility and scale; easy access to performance data
Infostretch leverages a proven combination of technology, processes, and expertise to help enterprises accelerate the execution of their digital strategy. We deliver faster and more effectively by unifying expert professional services and best practices with pre-built software frameworks and products. Our 1,000+ development, testing and integration specialists have deep capabilities in DevOps, QE, app development, Cloud, AI, IoT and mobility.