Challenge

As a data consolidation offering, REDPHARMA’s REDDATA service gathers and enriches transactional data from across 4000+ pharmacies, capturing all prescription and over-the-counter ticket sales. This measures the return and uplift of targeted end-customer marketing campaigns and offers. The data is processed, cleansed, and extrapolated for consumption by pharmaceutical producers, such a laboratories. The REDDATA analysis platform contains secure and anonymized data that is used for market and trend analysis. Various reporting options help customers understand their marketing environment, align campaigns, and develop seamless promotions. Using this wealth of data, REDDATA also produces national and localised statistics to identify product sales trends, and support fast decision-making.

David Nicholson-Jones, Analytics Architect with REDDATA, explains further: “Collating all the data with only a small data science team is a challenge. To attract new pharmacies to our model we need to showcase the benefits of analytics. One way to do this is by providing easyto-use dashboards, giving a detailed overview of a pharmacy’s position within a competitive landscape. We have been using Tableau to present our data in a meaningful way. Although this is great as a Business Intelligence (BI) solution, we found that as soon as our data volumes started to grow, performance went down.”

At the same time, REDDATA made a strategic move towards the cloud, and chose Amazon Web Services (AWS) as its platform. NicholsonJones looked for a way to split BI from data processing, while minimizing infrastructure costs.

Solution

After exploring various options in the market, the team liked the concept of columnar storage versus row-based storage, and Vertica’s approach in particular, according to NicholsonJones: “We were very impressed with Vertica’s detailed data optimization capabilities, actually working on the data ‘atoms’. In a demo, the Vertica team showed us how fast optimized columnar data stores can be, and how well they integrate with Tableau, which was a key selling point for us. We needed to automate our processes as soon as possible and could not afford full-time dedicated database administration, so it needed to be easy-to-use and intuitive. The Vertica team gave us a real feel for the value of the entire architectural design and data compression; a ‘penny-drop’ moment for us.”

Already convinced Vertica was the right solution, REDDATA was delighted to discover Vertica in Eon Mode. Eon Mode delivers a cloud-optimized architecture to easily scale resources up and down, isolate workloads, and optimize infrastructure costs without risk, restrictions, and added expense. The Vertica cloud-based deployment tools helped with a fast implementation schedule. The more he found out about Vertica in Eon Mode, the more excited Nicholson-Jones was about it: “Using Vertica in Eon Mode on AWS allows us to separate compute and storage, which helps with maintenance. Backups are easier, and we like the constant data synchronization. The flexibility to spin up nodes at a minute’s notice gives us an operational resilience that makes me sleep better at night.”

Vertica was soon introduced as the single data warehouse for all customer and partner dashboards and data science analytics. A collection of simple flattened tables with ‘feeder dimensions’ has replaced the need to manage multiple star-schema databases of varying granularity with external tools. The team only manages a core set of sources for data quality and populates the flattened tables with views using the chosen granularity – the result is full consistency across the board, with little extra effort. Nicholson-Jones: “We design it once, and can then focus on the data quality of our transactions and dimensions. We have a range of different granularity BI views for our pharmacies and laboratory partners, so each user only sees what’s relevant for them. This would be so much more time consuming to manage without the flattened table structure.”

During REDDATA’s development phase, running Vertica in Eon Mode on AWS was very helpful. The team could spin nodes up as required and shut them down again just as easily when the work was done. This helped keep AWS infrastructure costs as low as possible in the early stages.

Results

REDDATA can now focus on the deployment of in-depth dashboards for its pharmacy and laboratory partners. Tableau and Vertica are closely integrated, and the team is exploring Vertica’s machine learning capabilities to provide additional insight to its partners by data mining more variables into the models.

Nicholson-Jones is pleased with the benefits gained so far: “When we looked at other options, the pricing model was often CPU-based. Because we are still a young company it was hard to predict what we would need. Vertica’s flexible pricing model worked much better for us. Ease of management is very important to us, and we love the user-friendly Eon Mode GUI on AWS.”

He concludes: “With Vertica in Eon Mode all data warehouse management activities are automated, leaving our small team to focus on more advanced analytics that support our business goals. I love the product quality of Vertica, and the use of it is nearly always intuitive, which is impressive, given the complexity hidden underneath. Vertica has been great for getting us started with minimal resources and effort. It’s like getting a Ferrari to drive.”