The Client
Founded in 1983 the client is a privately owned mid-sized company located in Fort Worth, Texas. The company offers clinical software, business intelligence, specialty pharmacy, and more to individual and chain pharmacies across the United States.
For over 30 years more than 10,000 pharmacies all across the US use one or more of the client’s suite of on-premises and SaaS software and services.
The Challenge
To grow its service offerings, the client needed data and intelligence across its presence in pharmacies. It’s legacy, on-premise data warehouse could not analyze data from multiple pharmacies or an entire pharmacy chain. It could not scale to accommodate additional customers and had no ad-hoc query capability.
Processing large volumes of data would have required additional, expensive hardware. For example, it’s existing environment took a week to process 3 billion records — a task that should have completed within a fraction of that time.
The client wanted to better serve their customers, analyze the data, and provide real-time recommendations back to the pharmacies and decided to look for a solution.
The Solution
The client set up a challenging timeline of six-weeks to come up with a proof-of-concept, a comprehensive comparison between various data warehouses, and a suitable data analytics platform.
Excellarate’s team working with the client evaluated Snowflake and Looker. The comparison included its legacy data warehouse provider, and Hortonworks’ Hadoop deployed on the Microsoft Azure cloud service.
The analysis conclusively determined Snowflake to be the way ahead in processing speeds and capacity for large files and records. The Hadoop ecosystem had a two-week head start. However, Snowflake caught up within a few days and was importing and analyzing data within a week. Working in parallel, Hadoop, and the client’s legacy teams spent at least three weeks getting those environments ready for data import and further analysis.
Snowflake was a clear winner across all three ecosystems used to collaborate, ingest, and process data in the shortest time frame.
For presenting the analysis, the team compared Looker to other popular data-visualization tools. Thanks to Snowflakes’ native integration with Looker, its efficient use of SQL, and avoidance of unnecessary joins, Looker achieved the desired query performance with a warehouse, half the footprint of that of others.
Additionally, Looker has an in-database application layer on which the client can grow Snowflake and Looker simultaneously. With Snowflake and Looker, a single interface allows for logging into accounts, defining new schemas and warehouses, and also permits to manage permissions. As a SaaS offering, the combination easily accommodates growth, and the client has minimal administrative chores. Data is always encrypted, at rest and in-transit.
The Outcomes
The client now offers a BI platform capable of running enterprise-level reports for its customers.
Within three years, this BI platform now hosts data for six pharmacy chains, ingests up to 20 billion records daily in less than an hour, and offers the option of running near real-time data loads.
Pharmacies are able to make better decisions with the following key reports that can now be generated with ease.
- Fiscal year based Sales reporting including Trailing Twelve Months,
- This Year – Last Year analysis, etc. across all the pharmacies in a chain
- Third-party related reporting
- Medication Adherence Scores’ computation
- Manufacturer reporting
- Inventory management and theft detection
- Increase in pharmacies efficiency by monitoring prescription fill time, time spent at various stages of a prescription, etc.
- Drug cost monitoring and predictions
- Inadequately reimbursed insurance claims
Excellarate’s team successfully implemented the solution that now allows the client to offer its customers,
- Access to pharmacy data that makes sense in their business context. For e.g. reports such as below-cost dispensing, visibility into prescription referrals, controlled substance dispensing summary, etc.
- Ability to quickly access and analyze their prescription data
Actionable growth ideas through retrospective, near real-time, and predictive analytics - Ability to provide a source of accurate data and creation of reports and tools for both internal use and external Rx service vendors
- Faster and regular visibility to the various factors that impact their pharmacy business
- Unparalleled speed, scalability, and reliability to gain insight into data that leads to immediate action
- Utilizing the tool’s data-gathering capabilities for their contracted manufacturer’s reporting via the client’s advanced tool for gathering, organizing, and sending data to their data aggregators
