10+ years of cooperation resulting in a revamped BI platform, a suite of data management and visualization apps, a KPI management solution, and migration to the cloud.
Our customer is a US-based multinational company providing advanced analytics, technology solutions, and contract research services for the pharmaceutical market. The customer’s data assets comprise over 500 million patient records of more than 50,000 patents, as well as several dozen petabytes of proprietary data. Applying pharmaceutical data analytics technologies, they aim to enable a more efficient healthcare system in cooperation with their clients.
Over time, the customer started experiencing the following business challenges:
In an effort to address these challenges as well as to constantly improve their service offering, the customer was looking for a reliable long-term technology partner. Back in 2008, they were referred to Itransition by one of our long-standing customers.
After a trip to our office where we demoed the PoC of a reporting system based on their requirements, the company awarded us the contract in recognition of our proven expertise in healthcare data analytics.
For more than 10 years, Itransition’s team has been collaborating with the customer on a range of tasks, serving as a dedicated development center. For convenience, we break down the scope of our engagement into several phases.
We started by redeveloping the customer’s flagship pharmaceutical market data analytics platform that comprised a family of tools for generating structured and customizable reports from hundreds of multi-format data sources. The legacy platform was no longer adequate to meet the needs of pharmaceutical companies and flexibly adapt to emerging market demands. Itransition’s task was to reengineer and enhance it at all levels, including the UI, data processing, and report generation.
Itransition delivered the new BI solution based on the ASP.NET MVC framework, utilizing Microsoft SQL Server as a database engine. Our specialists redeveloped the platform’s functionality, ensuring code quality, ease of maintenance, scalability, and flexibility that the legacy system lacked. Our work on improving the solution resulted in 3-5x faster SQL querying with less time needed for clients to view reports, as well as reduced RAM and CPU usage.
Since the initial release, several versions of the platform came out, including two major releases. The delivered solution is now used by many of the world’s leading pharmaceutical corporations which benefit from handling multi-source and multi-format data in a standardized and well-organized way.
The revamped solution is capable of automated processing of pharmaceutical market data and contains a host of reporting and data sharing features.
Added report types and features included:
Among the implemented modules there were those for:
Later, the solution became part of the customer’s new modular suite and was divided in two parts — a data management app and a data visualization app — which were both developed by Itransition from the ground up. These applications can be used as a single BI solution or separately as standalone tools.
The legacy BI platform utilized Microsoft SQL Server Integration Services (SSIS) as an ETL engine, which presented a few problems impacting product release frequency. First, the configuration of ETL processes was outdated and slowed down data processing. Second, the tool didn’t support several data source formats and required custom implementation. As a result, data processing could take days, while some sources couldn’t be processed at all. In addition, there was the need for a standalone web interface to facilitate ETL processes for non-developers.
Our engineers were tasked with developing a data management app to mitigate these issues. The resulting solution enabled 10x faster data processing and less RAM, CPU and disk space usage, thus reducing hardware costs. As the customer’s proprietary application, it also significantly cut software license costs.
The new data management application gave users with minimal technical knowledge the tools to load, transform, and prepare data for visualization and reporting within minutes.
The solution processes end clients’ data source files in numerous formats (CSV, XLS, TXT), cleans and transforms data, creates and applies business rules, and prepares different forms of outputs, which can be databases or flat files. Users can then deliver the data to other destinations, such as cloud storages and FTP servers, as well as to other teams.
The application has its own web interface and a control panel in the frontend, with service instances running on the Microsoft SQL-based servers in the backend. A user configures and starts ETL processes, while virtual machines, working as nodes, execute these processes and return the current status information to the control panel. The nodes process data and generate databases for the SQL server.
We integrated the data management application with Salesforce via the free Salesforce bulk API in place of the paid tool that the teams had been using to connect to the SQL server.
Apart from that, we also integrated the solution with several database engines (Oracle, Microsoft SQL) and Apache Hadoop, ensuring distributed storage and processing of large datasets.
Our team continues to extend the application functionality. Over the last years, we have implemented the following:
Itransition was also responsible for the complete redesign and rebranding of the web part of the legacy BI solution, creating the foundation for a new data visualization app.
This solution can display data coming from the databases generated by the customer’s data management app or be used separately in conjunction with alternative data management engines.
We created the app with the React library, providing a user-friendly interface for data analysis and reporting with the possibility to export the results, and reducing the total number of steps required for maximal performance. We also implemented a new engine for charts and maps presentation (Highcharts/Highmaps).
We implemented storyboards to make it possible to apply and display simultaneously those parameters and metrics that were unsupported previously (e.g., countries, currencies, products, measurement units, sales growth, percentage growth, etc.).
Users can now save any number of charts or tables as widgets and display an unlimited number of them on a storyboard. Users just need to select a report, apply settings, and save the generated chart on a storyboard. We also introduced the option to export data visualizations as PPT, PDF, and XLS by using pre-uploaded templates in the same formats.
The standard menu was replaced with tile-based navigation with a hierarchical view. Storyboards were equipped with dynamic configuration and filtering options. We expanded the list of filters for viewing data by countries with stats for fastest-growing companies, top companies by volume/value, etc. Our team also enabled highlighting special events (e.g., reaching a certain point in sales) on charts with an accompanying text message.
For users to keep up with any changes to the reports, we added a new subscription option to enable email notifications. There are also dedicated Help and Resources sections with tutorials and videos.
To keep up with the growing number of the platform’s users and maintain the solution's performance, we advised the customer to migrate their solution from their on-premises server to the cloud.
As a Select Amazon Consulting Partner and DevOps practitioners, we had access to the expertise and resources to leverage Amazon Web Services (AWS) as the customer’s chosen cloud migration platform. This project phase kicked off in 2018.
Our DevOps engineers audited and evaluated the customer’s existing infrastructure and prepared a migration roadmap for the AWS migration. Then, we designed a scalable and secure cloud infrastructure and deployed it to AWS using Terraform.
Our team created a virtual private cloud with private and public subnets, defined network gateways, and configured security settings. The subnets are interconnected and comprise a development environment, a production environment, and CI/CD servers. The development and production environments were deployed via Amazon Elastic Compute Cloud (EC2). To manage and control AWS workloads, we used AWS Elastic Load Balancing. We also leveraged Amazon RDS for Configuration DB and Client data DB servers.
For server monitoring, our team used AWS CloudWatch, which collects logs, metrics, events, etc., thus providing a unified view of all the AWS resources. To improve solution performance and ensure fast transferring, we applied Amazon CloudFront (CDN).
To ensure the security of large volumes of sensitive data that the customers works with, we leveraged Amazon Simple Storage Service (Amazon S3) for scalable and secure data storage. All critical data is backed up through AWS tools, including Data Lifecycle Manager and S3 versioning. We also utilized Amazon RDS, which creates and saves automated backups of database instances. Our team also created AWS Lambda functions that allow archiving, backing up, and restoring specific databases hosted on RDS or S3.
For enhanced security, we used AWS Systems Manager Parameter Store, which stores critical data such as passwords and license codes as encrypted parameters. For secure configuration of managed instances and password reset, we utilized AWS Systems Manager documents (SSM documents) and AWS Systems Manager Run Command.
The migration helped the customer grow the user base and reduce infrastructure costs while ensuring high availability, scalability, and security. It also allows the customer to quickly deploy new environments (within 20-30 minutes), as well as streamline server recovery within 30-60 minutes in case 1 or 2 servers are down and within 60-120 minutes if the entire system needs to be restored.
Since 2018, Itransition has been collaborating on a new KPI management platform with the customer’s dedicated teams located in the USA, UK, India, and Bangladesh. The platform provides actionable insights and makes part of the customer’s modular pharmaceutical data analytics suite alongside the data management and visualization applications.
The KPI management platform is capable of processing data structures beyond those supported by the core data visualization application. It targets enterprises with heterogeneous data sources of varied structures and makes it easier for them to process these sources within a single database.
The platform also facilitates database generation with no need to involve developers creating VBA scripts for data transformation, so that everything can be configured by the app user or admin. This decreases the customer’s expenses on database generation and support.
The solution is built on .NET and Angular and deployed as a SaaS with the ability to release backward-compatible features to support client-specific configurations.
Full-cycle data management, from collecting data across sources through generating databases to configuring charts for data visualization.
Customizable configuration options to meet each end client’s specific needs, with the possibility to set different permissions for each user.
A scalable data warehouse engine for processing large volumes of enterprise-grade data. Examples include pharmaceutical data on sales and profits, the types of diseases along with the number of people suffering from them over a certain period, visits to doctors of particular specializations, etc.
A built-in list of data visualization formulas such as Market Share, Growth, Evaluation Index, Reach, and Frequency, with the possibility to customize them and add new ones.
Sales managers’ KPIs and performance calculation, including a manager’s percentage of sales relative to all other sales managers, the share of a specific product in the total sales of a particular manager, etc.
Forecasting and risk assessment algorithms, running on the available data and indicating potential risks per area. The solution provides summary data on a product in a given country, with the cells that require particular attention highlighted with color — the brighter the cell, the more attention it requires. There are several algorithms available for calculating the color index.
Report send-out to end clients, either scheduled or triggered by a data variance such as sales decline. Users can configure various notification algorithms, for example, for collecting sales data for a specific brand. Reports are recurrent and customizable, i.e. they can be set up for a specific case, for example when a report should be sent only if sales have decreased compared to the previous month.
Itransition’s engineers were in charge of developing the majority of architectural components of the solution, including Data Service, Export Service, and Gateway Service, and wrote the server part of the application, which is used in connection with the UI app. Itransition also took the lead in creating the source code for new features.
Our dedicated development team consists of:
To create the data management and visualization apps, we engaged functional developers, database developers, and the client delivery team. In the first phase, our team consisted of 2 developers and 2 QA engineers running manual testing. Over eight years, the team has grown to include 11 developers and 6 QA engineers allocated for automated testing. Our KPI management solution team also grew tenfold within a year after the project start.
To ensure smooth onboarding, our new team members study the project documentation and then complete a test task related to the specific solution they will be working on. On average, the onboarding takes 1-3 months to get familiar with the key project aspects. After that, the newcomers embark on their production tasks.
While developing the customer’s pharmaceutical data analytics platform, Itransition introduced automatic deployment and continuous integration practices. This allowed the team to improve efficiency, to double the deployment frequency, reduce deployment errors to one fifth of their pre-automation numbers, and decrease project efforts by hundreds of person-hours.
When deploying the customer’s infrastructure to AWS, we set up and configured CI/CD servers, leveraging Bamboo in combination with Jira and Confluence, which ensured flexible release options, faster feedback, and reduced risks. We used Confluence to document all of the project processes and requirements, and Jira for project planning and tracking. CI/CD implementation allowed our DevOps engineers to automatically build and deploy the code, quickly identifying gaps and working more efficiently on the whole.
In terms of the project methodology, it evolved from Kanban to Scrum with 2- and 4-week sprints, which featured sprint planning, daily meetings, and backlog grooming. Sprints are followed by sprint reviews, demos with the overview of implemented features, and retrospectives to evaluate the overall progress and define any possible areas for improvement.
We also adopted quarterly releases as a way to plan tasks in detail and keep them in sync with the roadmap estimates.
Cooperating with the customer since 2008, Itransition has been providing a range of services that spanned technology consulting, UX/UI design, development, data migration, QA and testing, and DevOps.
We developed and improved the customer’s solutions that make part of their flagship pharmaceutical data analytics suite used by corporations globally, and introduced new practices that streamlined the development process and made it more efficient.
In its role of the customer’s dedicated development team, Itransition has contributed with the following:
Medical data analytics solutions by Itransition drive valuable insights from a pool of health data and help improve care and performance. Get a free quote now.
Providers can see a range of benefits from healthcare BI. Find out how to unlock them with Itransition.
We reflect on the most up-to-date applications of big data in medicine across the care cycle and explore the most relevant know-hows of the technology.
Learn how precision medicine can transform cancer treatment and organ replacement and find your way around precision medicine software.
We discuss several approaches to healthcare data security during the pandemic and offer some tips to ensure it. Read on to learn more.