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 subsidiary of the Fine Chemical and Catalysts division of Johnson Matthey plc. The pharmaceutical manufacturer 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. They collect and analyze manufacturing operational data, for example, to allow them 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 assure the identity, quality, and purity of its pharmaceutical ingredients for customers and regulatory authoritie0073 such as the U.S. Food and Drug Administration (FDA) and others as part of 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 was scattered across the business: including in the company’s bespoke enterprise resource planning (ERP) platform, inside legacy departmental databases such as SQL, Access databases, and standalone spreadsheets. When that data was needed for decision making, excessive time and resources were devoted to extracting the data, integrating it and presenting it 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 conflicts between the data. And it was sometimes hard to tell which version of the data was correct and which wasn’t. It didn’t stop there. Even once the data had been gathered and presented, it was slow and difficult to make changes ‘on the fly.’ In fact, whenever a member of the Macfarlan Smith team wanted to perform trend or other analysis, the changes to the data needed to be approved. The end result being that the data was frequently out of date by the time it was 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 nonconformities or other undesirable 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 scenarios, 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, different 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.” Solution: Interactive Data Visualizations One of the Macfarlan Smith team had previous experience of using Tableau and recommended Mills explore the solution further. A free trial of Tableau Online quickly convinced Mills that the hosted interactive data visualization solution could conquer the data battles they were 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 investment, 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 cloudfirst strategy, but has to be certain that any hosted solution is completely secure. Tableau Online features like single sign-on and allowing only authorized 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: Where is 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: looking at deviations from the norm, such as when a heating 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 nd 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 walkthroughs and live online training. They also participated in a two-day ‘fundamentals 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 company 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’s 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. 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: • New interactive dashboards can be produced within one hour. Previously, it used to take days to integrate and present data in a static spreadsheet. • 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. • 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. • The company has one, single, trusted version of the truth. • Macfarlan Smith is now having discussions about its data—not about the issues surrounding data integration and data quality. • New users can be brought online almost instantly—and there’s no technical infrastructure to manage. Following this initial success, Macfarlan Smith is now extending Tableau Online out to financial reporting, 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.”
Questions for Discussion
1. What were the data and reporting related challenges Macfarlan Smith facing?
2. What was the solution and the obtained results/ benefits?