Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Balancing a professional MBA program and work is not easy. Learning accounting, corporate finance, economics, Excel programming and statistics is challenging for someone who hasn’t taken a math course since undergrad. Thanks to my Alteryx team and community, I am not in it alone, and this shining example is worth sharing:
The first half of this summer semester was dedicated to a course called Decision Modeling, which taught us how to construct everything from linear programming models to Monte Carlo simulations… in Excel. My spreadsheet capabilities are intermediate at best and it seemed counterintuitive to spend my workdays marketing a platform that helps liberate data workers from spreadsheets, only to spend my evenings at a Top 40 MBA program learning how to work in Excel.
Early in the semester, our chief data and analytics officer (CDAO), Alan Jacobson (@AJacobson), helped me think through what I viewed as complex mathematical formulas as logical steps to reach a decision, evolving my thinking as a problem solver. Later in the semester, Shaan Mistry (@ShaanM), a product manager here at Alteryx, helped me apply that thinking within the Alteryx Analytic Process Automation Platform. For our final project, my team and I set out to construct a model that would help one of my group member’s companies assign office attendance by employee number.
We started by “following the rules” and constructed a linear programming model in Excel, creating a chart of more than 1,000 decision variables with binary values for each employee number, per workday. We applied constraints by limiting the workweek to five days and by calculating total workspace in square feet (provided by the company), dividing it by Denver city social distancing guidelines—150 square feet of space per employee. The objective function was simple: Maximize the sum of all decision variables.
We immediately ran into limitations with Excel Solver, which could only compute a maximum of 200 variables. We addressed this by installing Open Solver, an open source solution, as a plug-in to compute our 1,000+ decision variables. Even then, the optimal solution produced by Open Solver did not evenly distribute the total days in office across all employees (e.g., some employees were only assigned one day at the expense of others that were assigned five days), requiring an additional layer of constraints. Enter, Alteryx.
Shaan helped us first build a standard analytic workflow in Alteryx Designer for this specific use case, applying the same business logic deployed by Solver in Excel in a repeatable format. The workflow collected employee, workday and max occupancy data, prepared and blended the data, and then leveraged two Multi-Row Formula tools to assign every employee a day, repeating at the max capacity constraint of 150, and then map that capacity to a specific day. Next, a Filter Tool removed any additional days created (beyond the five-day workweek), which populated two Summarize Tools added for quality control—ensuring both the number of employees going into the office and the total number of days met our constraint parameters—and ultimately, produced our final output.
Shaan then constructed this into an analytic application that would allow any organization to specify their unique return to work requirements—total number of employees, maximum capacity, percentage of non-working space and total square footage—to generate the same output format based on their own, unique information.
The analytic application allowed for dynamic inputs to achieve dynamic results. In a real-world application, the creator of the Alteryx workflow and analytic app could then seamlessly share it across the enterprise or automate the output to notify employees of their office assignments. In this use case, we used the basic capabilities of Alteryx Designer to apply business logic to an analytical process. As the number of variables, constraints and considerations increase, the natural progression in Alteryx is to build either an optimization model using the Optimization Tool based on R (language comprehension and coding not required) or a predictive model using the suite of Predictive Tools, based on the company’s desired analytic output or understanding.
Huge thank you to Shaan Mistry for supporting this project and helping me expose 50+ MBA students to the power of the Alteryx APA Platform.
