In conference rooms across the globe, sustainability is an increasingly meaningful buzzword. With concerns about climate change occupying a prominent place in the social and political discourse, manufacturing companies are reckoning with the pivotal role they place in the consumption economy, especially with regards to waste production.
At the same time, the accessibility of software platforms like Power BI has had a democratizing effect on data analysis. With its intuitive controls, user-friendly interface, and built-in visualization capabilities, Power BI encourages the sort of ad-hoc experimentation that can lead to big breakthroughs. But still, many often encounter a roadblock when they attempt to scale up their projects to a point where they can be relied upon at an enterprise scale.
These were the two dilemmas that confronted a major pharmaceutical company when it approached Thorogood about productionizing a series of Power BI dashboards that would help it monitor a variety of key performance indicators involving its sustainability initiatives. It had already built a small-scale, working prototype that used internal data to track the benchmarks, but in order for the tool to have lasting value, the company needed to scale it up to a point where it could take advantage of the full potential of Power BI and seamlessly integrate it into its workflows at a wider level. Most importantly, it needed to automate the data loading and preparation as much as possible so that the tool could become a robust enterprise solution and users would not waste countless hours on manual refreshes.
Using data to reduce waste
At the firm in particular, which manufactures a wide line of packaging-intensive pharmaceutical and home healthcare products, executives wanted to keep that waste line graph as level as possible and reduce its packaging waste as a percentage of sales even as sales grew. To achieve this, the company set out with three key targets. The first was to eliminate the use of so-called “bad plastics” – polystyrene and polyvinyl chloride – by 2025. They also wanted to reduce the overall use of plastic by 10 percent relative to sales. Third, they wanted to increase the use of recycled paper until they reached 100 percent.
To monitor these goals, analysts at the firm cobbled together a suite of dashboards in Power BI that fed off data from two different sources: its primary sales figures, and its packaging data, which includes the material used in packaging for each product. Multiply the weight of material used in each item’s packaging by the total number of items sold, and you get the waste footprint for each product in the company’s portfolio across its entire manufacturing line. Building off this foundation, a user could view each item’s contribution to the company’s overall waste as well as the size of its particular waste footprint relative to other products, leading the pharmaceutical giant towards building blocks of meaningful action steps that lead directly to the achievement of objectives. However, in order to achieve that, the firm understood that these sorts of analysis should not be reduced to a prototype stage but be converted into a finished product.
From experiment to finished product
The biggest challenge companies face in productionizing experiments like this Power BI tool is making sure that they operate in a way that brings net value. It simply isn’t feasible for an enterprise firm to expect wide adoption of a solution that has been designed according to the tastes of a small handful of individuals and requires copious amounts of manual legwork. A serious tool requires serious planning and development and careful thought at each stage.
When Thorogood began work on the project, it conducted lengthy information-gathering sessions designed to understand the exact business functionalities that the firm’s target user group would benefit from. Consultants then began outlining a system that would best leverage Power BI’s capabilities and the client’s data architecture to achieve a product that would serve as a keystone in its sustainability quest.
The first step was figuring out a system that would allow users to automatically refresh data in Power BI. This endeavor required it to reconcile data from multiple different sources. While the company’s primary sales figures resided in its internal systems, its packaging data came from a smorgasbord of local sources that included third-party contractors. Using Azure Data Factory, Thorogood automated the collection of packaging data to the extent that was possible, dramatically improving the ease with which that data could be mapped and standardized to work with its sales figures. From there, it used Databricks and Azure Data Factory pipelines to reconcile the data and move it into a SQL Server Database that would feed the Power BI application.
On the front end, Thorogood consultants took the client’s initial dashboard suite and began to shape it into a finished product. Drawing on their experience working with other Fortune 500 and leading CPG firms, they walked the company through the various ways in which the tool could be expanded using Power BI’s built-in functions. They developed the dashboards using Thorogood’s successful Iterative Dashboard Methodology approach, whereby consultant and client continuously workshop and refine all aspects of the application until it meets exact specifications.
A foundation for the future
The result was an enhanced, robust tool that quickly and intuitively offers users a comprehensive understanding of the past, present, and future of the client’s sustainability efforts. The backbone of the dashboard is a series of visualizations that display the company’s progress in each of its KPIs. Not only does the tool show how present waste levels relate to the past, it uses internal company forecasts to project the trend lines into the future. From there, users can drill deeper down into the numbers to identify factors that are significantly impacting present reality. They can see which product lines or individual items are driving waste and how the manufacturing habits of suppliers are driving their quest to achieve 100 percent recyclable plastic. Power BI’s inbuilt analysis capabilities allow users to run quick fact-finding missions that can lead to further action. Within the app, users can evaluate packaging replacements and changes through the execution of packaging analyses.
In all, this client’s experience offers an excellent example of the value of building a spirit of data-driven experimentation within an organization. It also shows the value that a company can achieve by taking the steps necessary to ensure that those experiments find a permanent home in its analytics operation.
