Using the Optimization Tool in Alteryx

As any user can attest, Alteryx is a fantastic and powerful tool. With well over 100 pre-built tools you can quickly and easily clean messy data, perform calculations, or analyze spatial information. Because Alteryx offers so many tools, it is reasonable that some of them will serve a specific niche and are little-known and little-understood.

Introducing: the Optimization tool, perhaps the most intimidating tool in Alteryx Designer.

The Optimization tool is used to, well, solve optimization problems. These are a class of problems where the goal is to maximize or minimize some value subject to one or more constraints. Microsoft Excel’s Solver add-in is very similar to the Optimization tool, so if you know one then learning the other should be a breeze.

In general, the most difficult aspect to using the Optimization tool is not understanding what optimization is nor understanding what data to connect to what anchors. Rather, the difficulty lies in understanding and formulating your optimization problem clearly from the onset. With a clear direction in mind, the rest of the pieces should fall in place smoothly.

Through the rest of this article we will:

  • Take a quick look at a simple linear optimization problem to gain an understanding of how they work
  • Walk through the various configuration options and input/output anchors for the Optimization tool
  • Step through an example using Alteryx

Optimization Problems

The simplest type of optimization problem is known as linear optimization. These are problems where the objective function—that is, the function to be maximized or minimized—is a linear function of the form:

optimization objective function

The c values above are known as coefficients and represent values associated with their respective decision variables, noted as x.

Our goal is to maximize or minimize the objective function according to one or more constraints that form a feasible region of possible solutions.

Example Problem

To make some extra money, you do lawn care around your neighborhood. You offer two services: lawn mowing and hedge trimming. You charge a flat fee for each service—$40 for lawn mowing and $25 for trimming—so your goal is to optimize your work to maximize profit. On average, it takes 60 minutes to mow a lawn and 30 minutes to trim hedges. You do not want to work more than 6 hours per day. Furthermore, each lawn uses one gallon of fuel while trimming hedges uses up one gallon. You can carry 9 gallons of fuel for the day's work.

Although this certainly sounds like a textbook problem, this is exactly how optimization problems are posed in the “real world.” Our first goal is to model the problem by listing each of the key pieces of information in the correct format.

Decision Variables

These are the variables that are used to obtain the maximum or minimum that we wish to find.

  • L = Number of lawns to mow per day
  • H = Number of hedge trimming jobs per day

Pay close attention when determining your problem’s decision variables. A poor or incorrect choice here can make your life very difficult, while a correct choice will let the rest of the problem flow smoothly.

Coefficients

Since our goal is to maximize profit, the coefficients for our decision variables are the dollar values for mowing a lawn ($40) and trimming a hedge ($25).

Objective Function

Our goal is to maximize profit, therefore:

  • Max Profit → Profit = 40L + 25H
Explicit Constraints

Explicit constraints are those that are mentioned directly in the problem statement. Here, they are:

  • Hours Constraint:      1L + 0.5H ≤ 6
  • Fuel Use Constraint:  1L + 1H ≤ 9
Implicit Constraints

Implicit constraints are those that are not written directly in the problem statement. Rather, they are often common-sense constraints. Here, we have:

  • L ≥ 0
  • H ≥ 0

For this problem, the number of lawns, L, and the number of hedges, H, must be non-negative integers.

In this simple example, our linear optimization problem is two-dimensional, thus we can draw a diagram to more clearly see the interplay between variables and constraints.

linear optimization graphical solution

The feasible region of possible solutions is bounded by our explicit and implicit constraints. In the image above, it can be seen below the red and blue lines for non-negative values of L and H.

The optimal solution will lie on one of the vertices of the feasible region. For this problem, that is either:

  • L = 0, H = 9
  • L = 3, H = 6
  • L = 6, H = 0

Plugging these values into our objective function, we find the following profit values:

  • 40*0 + 25*9 = 225
  • 40*3 + 25*6 = 270
  • 40*6 + 25*0 = 240

From these values, we can conclude that the optimal solution to this problem is when L = 3 and H = 6, with a value of $270. In general, the optimal solution to a linear programming problem such as this will be the point of the feasible region in the farthest top-right corner.

Using the Optimization Tool in Alteryx

Alteryx’s Optimization Tool, located in the Prescriptive tool palette, allows users to solve linear programming, mixed integer linear programming, and quadratic programming optimization problems. If you have a problem similar to the example we discussed above, it is likely that the Optimization tool is worth a try—it makes solving these types of problems much easier than developing a complicated macro to iterate through the problem.

Alteryx Optimization tool icon

Configuring the Tool

In the example above, determining the decision variables was the most challenging step. In my mind, the most difficult step in using the Optimization tool is understanding what data needs to flow into each anchor and how that data should be structured.

Input Anchors

The Optimization tool includes 4 input anchors, all of which are optional (only matrix-based models use input anchors):

  • O: Contains a list of variables, their coefficients, lower bounds, upper bounds, and data types.
  • A: A matrix of variables, their “costs,” and variable constraints. The way the data is structured here is dictated by your choice of matrix from above: constraints in rows, variables in rows, or SLAM.
  • B: A matrix of model constraints by variable and overall.
  • Q: Used for quadratic programming problems to specify the quadratic portion of the problem.
The Configuration Menu

There are combinations of configurations, depending on your specific needs. Your selection in the “Select input mode” drop-down will dictate the appearance of the configuration menu.

Select Input Mode – Specify the model as matrices

This is the default setting, and most likely the one you will use. It requires users to connect to the O, A, and B (and optionally, Q) anchors.

When this input mode is selected, you then have to select options for the following:

  • Select problem type:
    • Linear program
    • Mixed Integer program
    • Quadratic program
  • Select solver:
    • Glpk (for linear and mixed integer)
    • Symphony (for linear and mixed integer)
    • Quadprog (for quadratic only)
  • Maximize Objective:
    • Defaulted to no (i.e. a minimization problem)
  • Select constraint mode for input Anchor A
    • Dense matrix, constraints in rows
    • Dense matrix, variables in rows
    • Sparse (SLAM) matrix
  • Display field mapping for input Anchor O
    • A yes/no toggle, defaulted to no
    • When set to no, you must use specific field names for the mapping to occur correctly
    • When set to yes, you can name your fields anything you like and map them manually
optimization tool configuration menu
Select Input Mode – Enter the model manually

This option allows you to enter in all variables, coefficients, and constraints as a comma-separated list. Using this setting avoids the need to create data sets, as none of the input anchors are used.

optimization configuration manual
Select Input Mode – Specify the model from a file

This option allows you to connect to a special optimization file that contains your data. Again, no input anchors are needed when using this option.

The only new option here is:

Solving the Problem

We have now worked through an example manually and discussed the configuration options of Alteryx’s Optimization tool. Now we will finally work through the same example problem in Alteryx.

For this example, I will be using the matrix input method, as that is the most common way to use the Optimization tool.

To recap, our problem is the following:

  • Maximize profit from mowing lawns and trimming hedges
  • Limit time spent to no more than 6 hours per day
    • Each lawn takes 1 hour to complete
    • Each hedge takes 0.5 hours to complete
  • Limit fuel use to no more than 9 gallons per day
    • Lawn and hedge jobs both use 1 gallon of fuel

The Starting Data Set

To begin, we will use a Text Input tool to create our data set. I like to put each variable—the two different job types, in this case—on the rows and list the value (Price) and costs (Hours and Fuel) as separate columns. This offers a simple and easy-to-read view of the data that we are working with.

optimization model starting data

For the next step we will use a Formula tool to identify each variable’s lower and upper bounds (where applicable) and data type. For this problem, we know that all values must be non-negative, thus our lower bound is 0. There is no specified upper bound, so for this problem we can ignore it. Finally, we know that jobs come in integer units (1, 2, 3, …) so the data type is set to “I” for integer.

optimization model bounds
The O Input

Now that the data is ready, we must split it into two streams: one for the O input and one for the A.

The O input should contain our variables, coefficient, lower and upper bounds, and data type columns. In older versions of Alteryx these were required to have specific names (“variable” or “ub” for example), but in newer versions you can manually map any field.

Using a Select tool, deselect any constraints columns—Fuel and Hours, here. Connect the Select tool to the Optimization tool’s O input anchor.

optimization o input
The A Input

The A input contains information on each variable’s constraints. To set up this input, all we need to do is connect a Select tool and select only the variable and constraint fields. Thus, for this problem, we will only have three columns (Variable, Hours, Fuel).

The B Input

We have saved the B input for last because it does not come directly from our initial data set. Instead, we will create a new data set using another Text Input tool. This new input has three columns: a string constraint label, the direction of the constraint, and the right-hand side (value) of the constraint.

In this problem, we have two sets of constraints:

  • Total hours must be less than or equal to 6
  • Total fuel must be less than or equal to 9

Thus, our B input data set looks like this:

optimization model b input

Connecting it to the Optimization Tool

Now that we have created the O, A, and B data sets (we don’t need a Q data set because this is a linear optimization problem), we can configure the Optimization tool itself.

connecting to the optimization tool

Connect each of the three data sets as in the image above. Next, click on the Optimization tool to begin configuring it.

The default settings are:

  • Select input mode: Specify the model as matrices
  • Select problem type: Linear Program
  • Solver Solver: Glpk
  • Maximize Objective: Off
  • Select constraint mode for Input Anchor A: Dense matrix, constraints in rows
  • Display field mapping for Input Anchor O: Off

The first three settings are exactly what we need for this basic linear programming problem, so leave them as-is. Since our goal is to maximize profit, make sure to turn that setting on. The data we constructed for input A has each variable in its own row, so change this setting to “dense matrix, variables in rows.”

Finally, turn on field mapping for input A and select the appropriate field for each option.

configure the optimization tool

Reading the Output

We have now done all of the hard work. We constructed the data, added necessary calculated fields, and split the data into our O, A, and B input streams. We finally configured the Optimization tool according to our model’s needs.

Connect a Browse tool to each of the three output anchors: S, D, and I.

  • S: Displays the “objective value,” which is the value we were attempting to optimize; displays the value for each variable required to reach that objective value.
  • D: Shows a description of the model as a whole. Includes information such as the problem type, objective value, number of constraints, and more.
  • I: An interactive report that combines information from the previous two outputs. This is a really interesting view worth checking out—see the image below.
optimization tool output

After all that work, we finally have an answer: mowing 3 lawns and trimming 6 hedges gives us a total profit of $270. The Slack column in the bottom of the output tells us how much of either constraint went unused. In this case, we used up exactly our 6-hour and 9-gallon limits.

Closing Words

This blog post ran a bit long. You can literally take graduate-level courses on this subject (speaking from experience, they are not easy), so there is a great deal of information that I had to leave on the cutting room floor.

If you need additional help, check out Alteryx’s built-in prescriptive analytics samples. There are some excellent problems available for you to really get your hands dirty with the optimization tool.

optimization samples

If you have any questions, comments, or anything else, we would love to hear from you. You can contact me directly at john.emery@tessellationconsulting.com.

Subscribe to Our New 'Data Cap' Newsletter!

Want a monthly insights from the Tessellation Team with tips, tricks, and secrets to improve your analytics?

Sign up below and we'll deliver articles, events, and how to's straight to your inbox.
Comments are closed.