Pharmaceutical data analytics suite for a US multinational

Solution

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.

BI platform redevelopment

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 new features in detail

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:

• Share of Market reports indicating percentage of sales
• Launch reports demonstrating sales dynamics since product launch
• Open Access Demographics reports based on demographic statistics by country
• Weekly reports in addition to monthly and quarterly reports
• Maps support for displaying reports by countries and regions
• Extended metrics for calculations (e.g., growth compared to prior period)
• Milestones highlighting in data tables and charts
• The right axis for displaying an additional type of metrics (e.g., sales as numbers and as a percentage)
• Report aggregation for export in the presentation format

Among the implemented modules there were those for:

• Usage analytics for facilitated administration
• Data restrictions administration for data visibility management, e.g. by countries, products, etc.
• Data upload permission management for authorized users to upload authorized types of data on their own
• Questionnaire data support for collecting data from surveys (for example, regarding specific medicine popularity and efficiency) and measuring it against sales data
• Forecasting for generating forecasts and providing recommendations in line with the set parameters
• Data extraction for viewing data as archived CSVs via any alternative data visualization app

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.

A data management application

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.

Data management made accessible to non-technical users

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.

Third-party integrations

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.

Continuous improvement

Our team continues to extend the application functionality. Over the last years, we have implemented the following:

• A market definition engine to enable no-coding editing of market definitions
• A scheduled job trigger to run scheduled tasks and send emails with reports automatically
• New file formats, including flat files, parquet files, archived files as sources (zip, rar, 7z, tar, gz)
• Data output archiving and delivery to end clients’ FTPs and cloud storages (Amazon S3, Azure Blob Storage)
• Automatic data parsing and recognition from uploaded CSVs and XLSs
• QC reports for automated data validity checks
• Divided permissions between the development team and clients for content access authorization
• Horizontal scalability for scaling up when the capacity of servers increases and scaling out when the number of servers grows

A data visualization application

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).

The storyboard

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.

Migration to the cloud

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 an AWS cloud migration strategy. 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).

Data security

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.

Migration payoffs

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.

The KPI management platform

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.

The subsystems

1. An application for displaying data, customized dashboards, and charts. Each dashboard can be divided into groups of charts by setting up modules.
2. Two admin panels: one for configuring data widgets to be displayed on a dashboard; the other for managing users, access levels, permissions, authentication, and localization parameters, as well as for customizing the solution according to clients’ needs.
3. A service application used for:

• Configuring database connection strings.
• Checking the integrity of connected databases, as well as the operability of other system components with complete information on whether the service is running with all the necessary settings and files in place.
• Resetting user cache for dashboards to speed up launch and prevent lagging.
• Managing, importing and exporting system settings.

Essential features:

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.

Results

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 AWS 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:

• Redeveloping the core market analytics platform and enabling 10x faster data processing.
• Migrating the customer’s infrastructure to the cloud, which resulted in user growth, reduced infrastructure costs, and the ability to deploy new environments in 20-30 minutes.
• Developing a new KPI management solution, further improving and facilitating database generation, providing insights into employee performance, and introducing market forecasting algorithms.
• Introducing CI/CD practices, which doubled deployment frequency, allowed deployments with 5x fewer errors, and reduced project efforts by hundreds of person-hours.
• Enabling on-demand team scaling up and down depending on business needs, with the onboarding time reduced to 1-3 months.