Problem
The Background
Macfarlan Smith has earned its place in medical history. The company held a royal appointment to provide medicine to Her Majesty Queen Victoria and supplied groundbreaking obstetrician Sir James Simpson with chloroform for his experiments in pain relief during labor and delivery. Today, Macfarlan Smith is a sub- sidiary of the Fine Chemical and Catalysts division of Johnson Matthey plc. The pharmaceutical manufac- turer is the world's leading manufacturer of opiate narcotics such as codeine and morphine.
Every day, Macfarlan Smith is making decisions based on its data. The company collects and ana- lyzes manufacturing operational data, for example, to allow it to meet continuous improvement goals. Sales, marketing, and finance rely on data to identify new pharmaceutical business opportunities, grow revenues, and satisfy customer needs. Additionally, the company's manufacturing facility in Edinburgh needs to monitor, trend, and report quality data to ensure the identity, quality, and purity of its phar- maceutical ingredients for customers and regulatory authorities such as the U.S. FDA and others as part of current good manufacturing practice (CGMP).
Challenges: Multiple Sources of Truth and Slow, Onerous Reporting Processes
The process of gathering that data, making decisions, and reporting was not easy, though. The data were scattered across the business including in the compa- ny's bespoke enterprise resource planning (ERP) plat- form, inside legacy departmental databases such as SQL, Access databases, and stand-alone spreadsheets. When those data were needed for decision mak- ing, excessive time and resources were devoted to extracting the data, integrating them, and presenting them in a spreadsheet or other presentation outlet.
Data quality was another concern. Because teams relied on their own individual sources of data, there were multiple versions of the truth and con- flicts between the data. And it was sometimes hard to tell which version of the data was correct and which was not.
It didn't stop there. Even once the data had been gathered and presented, making changes "on the fly" was slow and difficult. In fact, whenever a member of the Macfarlan Smith team wanted to per- form trend or other analysis, the changes to the data needed to be approved. The end result was that the data were frequently out of date by the time they were used for decision making.
Liam Mills, Head of Continuous Improvement at Macfarlan Smith highlights a typical reporting scenario:
One of our main reporting processes is the "Corrective Action and Preventive Action," or CAPA, which is an analysis of Macfarlan Smith's manufacturing processes taken to eliminate causes of non-conformities or other unde- sirable situations. Hundreds of hours every month were devoted to pulling data together for CAPA-and it took days to produce each report. Trend analysis was tricky too, because the data was static. In other reporting scenar- ios, we often had to wait for spreadsheet pivot table analysis; which was then presented on a graph, printed out, and pinned to a wall for everyone to review.
Slow, labor-intensive reporting processes, dif- ferent versions of the truth, and static data were all catalysts for change. "Many people were frustrated because they believed they didn't have a complete picture of the business," says Mills. "We were having more and more discussions about issues we faced- when we should have been talking about business intelligence reporting."
The Solution: Interactive Data Visualizations
One of the Macfarlan Smith team had previous expe- rience in using Tableau and recommended Mills explore the solution further. A free trial of Tableau Online quickly convinced Mills that the hosted inter- active data visualization solution could conquer the data battles the company was facing.
"I was won over almost immediately," he says. "The ease of use, the functionality and the breadth of data visualizations are all very impressive. And of course being a software-as-a-service (SaaS)-based solution, there's no technology infrastructure invest- ment, we can be live almost immediately, and we have the flexibility to add users whenever we need."
One of the key questions that needed to be answered concerned the security of the online data. "Our parent company Johnson Matthey has a cloud- first strategy, but has to be certain that any hosted solution is completely secure. Tableau Online fea- tures like single sign-on and allowing only autho- rized users to interact with the data provide that watertight security and confidence."
The other security question that Macfarlan Smith and Johnson Matthey wanted answered was this: Where are the data physically stored? Mills again: "We are satisfied Tableau Online meets our criteria for data security and privacy. The data and workbooks are all hosted in Tableau's new Dublin data center, so it never leaves Europe."
Following a six-week trial, the Tableau sales manager worked with Mills and his team to build a
business case for Tableau Online. The management team approved it almost straight away, and a pilot program involving 10 users began. The pilot involved a manufacturing quality improvement initiative: look- ing at deviations from the norm, such as when a heat- ing device used in the opiate narcotics manufacturing process exceeds a temperature threshold. From this, a "quality operations" dashboard was created to track and measure deviations and put in place measures to improve operational quality and performance.
"That dashboard immediately signaled where deviations might be. We weren't ploughing through rows of data-we reached answers straight away," says Mills.
Throughout this initial trial and pilot, the team used Tableau training aids, such as the free training videos, product walk-throughs, and live online train- ing. They also participated in a two-day "fundamen- tals training" event in London. According to Mills, "The training was expert, precise and pitched just at the right level. It demonstrated to everyone just how intuitive Tableau Online is. We can visualize 10 years' worth of data in just a few clicks." The com- pany now has five Tableau Desktop users and up to 200 Tableau Online licensed users.
Mills and his team particularly like the Tableau Union feature in Version 9.3, which allows them to piece together data that have been split into little files. "It's sometimes hard to bring together the data we use for analysis. The Union feature lets us work with data spread across multiple tabs or files, reducing the time we spend on prepping the data," he says.
The Results: Cloud Analytics Transform Decision Making and Reporting
By standardizing on Tableau Online, Macfarlan Smith has transformed the speed and accuracy of its decision making and business reporting. This includes:
1) New interactive dashboards can be produced within one hour. Previously, it used to take days to integrate and present data in a static spreadsheet.
2) The CAPA manufacturing process report, which used to absorb hundreds of man-hours every month and days to produce, can now be produced in minutes-with insights shared in the cloud.
Chapter 3 • Nature of Data, Statistical Modeling, and Visualization 207
3) Reports can be changed and interrogated "on the fly" quickly and easily, without technical intervention. Macfarlan Smith has the flexibility to publish dashboards with Tableau Desktop and share them with colleagues, partners, or customers.
4) The company has one, single, trusted version of the truth.
5) Macfarlan Smith is now having discussions about its data-not about the issues surround- ing data integration and data quality.
6) New users can be brought online almost instantly-and there's no technical infrastruc- ture to manage.
Following this initial success, Macfarlan Smith is now extending Tableau Online to financial report- ing, supply chain analytics, and sales forecasting. Mills concludes, "Our business strategy is now based on data-driven decisions, not opinions. The interactive visualizations enable us to spot trends instantly, identify process improvements and take business intelligence to the next level. I'll define my career by Tableau."
Task
A. What were the data and reporting related challenges that Macfarlan Smith faced?
B. What were the solution and the obtained results/ benefits?
C. Your final thoughts on this case?