←
Go back to case studies
Tyler Nagano
Data Engineer Consultant
March 12, 2024
By the Compass Team
category
Analytics Automation
Reporting
industry
Healthcare
technologies
Alteryx Designer
Expanding upon the base model created in Alteryx, the Compass team created a set of workflows to add in additional sources of data, apply business rules to the calculations, and perform validation of results. Applying the business rules captures all of the logic that was previously built only in excel, but now the Alteryx workflows read in template Excel files to apply the rules. This allows for a centralized location of all rules applied which makes it easy to track all changes that are being made. This new solution can be easily rolled out to all products and provides easy maintenance of the solution moving forward.
Our client is a multinational healthcare wholesaler who relies on efficient supply chains to properly service their customers. The client had a base model in Alteryx which calculated common supply chain numbers such as EOQ (Economic Order Quantity) to optimize the level of stock of products they kept on hand to minimize costs while handling demand. To keep up with changing business needs additional rules were needed to be applied on the calculations to suit the business. These adjustments were performed in excel and were slowly becoming unmaintainable. The client new that a new approach could make this much more maintainable and were looking for new scalable solutions.
The multinational healthcare wholesaler was experiencing difficulties with maintaining their supply chain optimization model. The original model was built in Alteryx and calculated a few key metrics which the business relied on for optimizing the stock levels of their products to meet demand. The knowledge to maintain the model slowly eroded and new business requirements were pushed onto the team which needed to be incorporated into the final results. To implement the business requirements the team had turned to Excel to apply the appropriate changes to keep up as they were comfortable with this tool. These changes required a lot of different logic to be applied in various steps with lots of additional files created by different business units to apply properly. The knowledge to make these changes were primarily done by a single individual making a single-point of failure if the individual decided to move to a different organization.
On top of the single-source of failure, the business logic implemented was slowly becoming unmaintainable and visibility into the exact logic were increasingly becoming difficult to follow and understand without significant expertise. These calculations and business rules are critical to the business and have saved millions of dollars in holding optimal stock. Therefore, the company knew that they needed to find a new solution to handle the increased complexity and implement a maintainable solution moving forward to continue saving money on optimal stock levels moving forward.
Finally, the main system the client used to set the optimal stock levels is open for changes to be made by many people in the organization. This sometimes causes discrepancies between the optimal value and what someone may have changed in the source system themselves. Therefore, the client had build a validation tool in Excel to compare optimal model values with values in the system, but this tool was also losing its maintainability too.
The major challenge facing our client was the increasing complexity and lack of maintainability of the business logic being built in Excel. Without significant experience using the file, it was difficult to understand what transformations were being performed. Without a major change this would become unmaintainable.
To work with the business rules within the Excel file required significant expertise because there were many tabs and various connections to implement the logic. In addition, the knowledge was siloed mostly in a single individual who made the necessary updates when the business needed changes. If they left the organization no one would have complete understanding of the model.
The client faces challenges with source system accuracy as many individuals have the ability to manually make changes to the system. Therefore a way to validate the results of the model with the values in the system would be beneficial to ensure that the optimal value of stock is being purchased.
To solve the problem, the Compass Analytics team first dove deep into the previously built Alteryx workflow and Excel files with the new business logic. With the exploratory portion finished, the team decided on building a set of 3 workflows: A data cleaning and calculation workflow, a workflow to apply business logic and a workflow to validate source system results with model results.
The data cleaning and calculation workflow took various inputs from multiple source databases, excel files maintained by other teams and internally maintained files. After joining all the data together, the stock quantity calculations are performed and different business flags are created based on characteristics of the products.
The business rules workflow takes the output of the first workflow and multiple inputs from template files in excel defining key business logic. The information contained in the template files are used to apply the appropriate business rules to apply rounding of values among other transformations. Multiple reports giving insight into high level metrics such as ABC classification are also calculated and outputted to give quick summaries to executives.
The main output from the business rules workflow is used in the validation workflow. This workflow combines source data values and model results to check if there are any differences between them due to other people in the organization updating the optimized source values.
In addition, at the end of the business rules process the results are archived to allow for the business to keep track of changes over time and have a historical record of the values generated by the model.
The new solution developed by Compass Analytics provides the multinational healthcare wholesaler with an easy to maintain solution for running the model calculations and applying business rules while generating insightful reports. This solution will lead to continue maintainability and continue to save millions of dollars annually on setting optimal stock levels.
Converting the business logic from various interconnected excel formulas to a structured Alteryx workflow with Excel template files allows for easier maintenance and ability to quickly adapt to changing business needs. The simplified solution allows for the business to quickly compare different options leading to greater insights into optimal stock levels.
In-depth knowledge of the inner workings of the excel file with business rules is no longer needed to understand and adjust parameters in the system. Clear documentation and simple templates provide immense flexibility leading to a faster business a quicker time to insights.
Robustness is build in to the solution by automatically comparing key values in the model results and the system, providing a list of entries with changes made and formatting in the proper form to be able to reupload back to the system. The validation portion ensures that the optimal stock levels are bought leading to decreased costs over time.
John Molson Supply Chain and
Business Technology Association
.png)
.jpg)
