KPI as a Service – TopPharm Implements Qlik-based Web Platform for Sell-out Data
“With the Qlik-based web platform, we offer our industry partners the ability to access their sales data flexibly and according to their individual needs at any time. This modern service strengthens partner relationships and helps solidify them.”
Kim Kläusler, Data Management Analyst, TopPharm Apotheken und Drogerien Genossenschaft.
The customer
TopPharm Apotheken und Drogerien Genossenschaft, with around 120 independent pharmacies and over 1,800 employees, is the largest independent pharmacy group in German-speaking Switzerland. Founded over 30 years ago, the cooperative emphasizes quality, transparency, fairness, and innovation to strengthen the profession of independent pharmacists in the long term. The focus is always on meeting the needs of partner pharmacies with the highest quality. Thanks to its democratic structure, each member has a clearly defined right to participate. The unique strength of the TopPharm group lies in its employees, who are the first point of contact for all health-related questions—personal, competent, and discreet.
The project team from TopPharm consisted of Stefan Dahinden and Kim Kläusler.
Solution at a glance
Industry Pharma | Function Providing sales figures for external industry partners |
Region Switzerland | Data Sources Microsoft-SQL-Server |
Utilized Technologies Qlik Sense, Qlik Mashup, iVIEW Dataflow, iVIEW Library | ROI & Time-to-value KPIaaS as a service to generate revenue and strengthen customer loyalty |
Initial Situation
Key Figures as a Service Offering
To provide better insights into essential sales metrics to its approximately 85 industry partners, TopPharm wanted to establish a web-based platform that allows external access to customized sell-out data based on master and sales data from a Microsoft SQL Server instance. The goal was to both strengthen relationships and create added value for industry partners.
“In the past, we provided our partners who participate in the annual industry discussions with semi-annual static reports on sales figures,” explains Kim Kläusler, Data Management Analyst, TopPharm Apotheken und Drogerien Genossenschaft. “However, we want to offer our industry partners more: the ability to independently access data monthly and tailor it to their specific information needs.”
It was crucial for TopPharm that the platform be easily and securely accessible to industry partners through standard browsers, with data stored exclusively in Switzerland. Other essential requirements included limiting data display to products negotiated with each partner, providing monthly evaluations with year-on-year comparisons, and offering filtering and scaling options for different display formats. Additionally, the cooperative desired export options, such as downloading reports in Excel and PDF formats. The design had to align with TopPharm’s corporate identity.
Solution
Informatec Impresses with Qlik and Cloud Expertise
Due to its long-standing positive experiences with Qlik in the context of data analysis for management and various departments, especially the “Purchasing and Industry” department, TopPharm decided to use Qlik Sense for this project as well.
TopPharm entrusted the implementation of the web platform to Informatec, a trusted partner with whom the cooperative has been successfully collaborating for several years. “Their data analytics expertise, particularly in working with Qlik, is outstanding. Their solution-oriented approach has convinced us time and again.”
Informatec, as a Managed Services Provider, also brings cloud expertise, offering a Managed-BI-Cloud environment with Swiss BI Cloud on Azure, where clients’ BI infrastructures are hosted in a Swiss data center—a key point for TopPharm, as the operation of Qlik Sense should be cloud-based.
IaaS + iVIEW
Based on TopPharm’s precise requirements, Informatec developed a tailored BI solution, fine-tuned in close collaboration with TopPharm. TopPharm found the cooperation with Informatec to be extremely professional and cooperative. “Informatec always had an open ear for our needs, and we felt confident in receiving a flexible, customizable solution tailored perfectly to us. We especially appreciated the opportunity for regular exchanges due to their proximity.”
Qlik Sense is operated as a Client-Managed installation in the Swiss BI Cloud. Informatec ensures smooth access and operation with an “Infrastructure as a Service (IaaS)” offering, which includes 24/7 operations per Service Level Agreement (SLA), incident management, as well as services such as backup & restore and sizing. The seamless data and distribution process of KPIs is ensured by the iVIEW Data Automation Framework, a Qlik-integrated toolkit developed by Informatec.
The iVIEW Dataflow module enables automated and controlled data processes (ETL), with monthly data updates triggered at the push of a button within seconds, thanks to a well-structured loading process.
The iVIEW Library module serves as a central directory for all Qlik applications, acting as a “Single Point of Truth” for Qlik content, ensuring efficient KPI management.
“When an additional industry partner is interested in KPIaaS, iVIEW Library makes it easier for us to set up an application for them,” says Kim Kläusler. Additionally, Informatec provided a guide to facilitate the independent further development of the solution.
Our iVIEW Solutions
To ensure a fundamental understanding of Qlik, Informatec conducted training sessions
The aggregated sell-out data for individual pharmacies’ articles from the current month, as well as the past 36 months, are provided monthly as CSV files via SFTP by an external service provider. These data are linked by TopPharm with the master data of the industry partners. The Qlik and iVIEW-based BI solution accesses this consolidated data set.
The front end was implemented in line with TopPharm’s corporate design using Qlik Mashup, allowing data visualizations and analyses from Qlik applications to be integrated into custom web applications. “With the Qlik know-how we built internally, we were able to design much of the frontend independently, allowing Informatec to focus on security,” recalls Kim Kläusler. Sensitive data protection is guaranteed by a secure login with two-factor authentication. A permission concept also regulates which users can access which data.
Applications and benefits
Added Value for Industry Partners
The web platform was initially made available to ten selected industry partners, providing data on around 940 products. “Each of these partners ultimately uses their own Qlik application, accessible via an individual URL and corresponding two-factor authentication,” explains Kim Kläusler. “Through a dashboard in TopPharm’s design, they can retrieve, filter, and export precise sales data for selected products, gaining valuable insights into sales figures and trends that help optimize their business strategies.”
The data can be filtered by time dimensions such as month, quarter, and year, as well as by product lines and pharmacodes, enabling tailored analyses. Available metrics include the cumulative sales values of an article from the start of the calendar year to the current date (YTD sales value) or rolling over the last 12 or 36 months (MAT sales value). Visualization in chart form, for example, allows tracking the sales values of nasal spray over five months or graphically comparing values of different products.
The basic package includes a defined number of freely selectable contract products, with options to add more contract and non-contract items if needed.
The feedback has been very promising, says Kim Kläusler: “We sent out a survey where partners could also share their wishes for further optimizing the solution. The response has been very positive. Industry partners appreciate the ability to easily access reliable, up-to-date sales data. The introduction of KPIaaS has further solidified our position as an innovative and reliable partner in the pharmaceutical industry.”
Future plans
TopPharm plans to continuously expand and improve the platform with the support of Informatec. In the future, filter options regarding the time periods to be considered will be expanded. Additionally, there are plans to also integrate sell-in data.
„Das Angebot von KPI as a Service stellt einen klaren Differenzierungsfaktor im Wettbewerb dar und ist entscheidend für den Aufbau und die Pflege starker Beziehungen zu unseren Industriepartnern im Pharmaumfeld.“
Kim Kläusler, Analyst Datenmanagement, TopPharm Apotheken und Drogerien Genossenschaft
Summary
Challenge
Providing a web-based platform to offer KPI (Key Performance Indicators) as a Service (KPIaaS)
Solution
Qlik Sense as Client Managed Installation, Qlik Mashup, iVIEW Dataflow, iVIEW Library
Advantages
- Web access to customized sell-out data
- Individual Qlik applications for industry partners with unique URLs and two-factor authentication
- KPIaaS as a service offering for revenue generation and strengthening customer relationships
- Strengthening TopPharm’s position as an innovative and reliable partner in the pharmaceutical industry.