Prescriptive Analytics, a buzz word like predictive analytics, aims to find the “best” solution to a problem, given a number of constraints (business rules). Within Alteryx, there is a handful of sample workflows to help understand different prescriptive optimization problems, located under help – sample workflows – predictive tool samples – prescriptive analytics. For today’s blog, I would like to explain from a theoretical sense what an optimization problem is about, without getting into too much detail with the underlying mathematics. Following this, I will show how to configure the Alteryx optimization tool and structure the input data.
The Maximizing Profit Example:
The best way to understand this tool is by giving an example. Hypothetically speaking, you oversee accepting or declining projects within a business. What you want is to maximize the profit that you will receive within a given budget. You have 6 projects you are contemplating between, with a budget of $1,000. The projects are as such:
| Cost of Project | Benefit of Project | ROI | |
| Project A | $1000 | $500 | 50% |
| Project B | $900 | $485 | 54% |
| Project C | $700 | $250 | 36% |
| Project D | $500 | $240 | 48% |
| Project E | $300 | $200 | 66% |
| Project F | $200 | $80 | 40% |
| Project G | $100 | $10 | 10% |
Formula to fit the budget:
Project A + Project B+ Project C + Project D + Project E + Project F + Project G <= $1000,
Or otherwise using algebra, you can replace the project with the $ amount: 1000A + 900B + 700C + 500D + 300E + 200F + 100G <= 1000,
But you also need to remember that we want to maximize the value, otherwise can be expressed as; 500A + 485B + 250C + 240D + 200E + 80F + 10G = as big as possible given formula 1.
What we notice is that clause 1 gives us a list of results:
| Benefit | Rank | |
| Project A | 500 | 2 |
| Project B + G | 495 | 3 |
| Project C + F + G | 360 | 5 |
| Project C + E | 450 | 4 |
| Project D + E + F | 520 | 1 |
As the above table shows, by spending $1000 the maximum benefit you could receive from spending 1000 is $520, a 52% return by combining project D, E and F. For this example, it took me 3 minutes using a calculator, but real problems could have 1000’s of data points and more constraints.
The Alteryx Optimization Tool Inputs:
The Optimization tool can be configured in 3 ways, however the method I prefer is the manual mode with pre-existing data. To configure the tool using this method, you will need the input anchors O, A and B.
O Anchor:
The O Anchor specifies the variables (string) that you will be evaluating (for our instance the project name), and the coefficient (number) (for our instance the ROI). In this input you can also specify:
- The lower bound (lb): The minimum that is required (by default it is 0)
- The upper bound (ub): The maximum that is required (by default infinite)
- Type of Problem (type): Relates to what your decision variable is, and it can either be Continuous (C), Binary (B), or Integer (I). (By default it is C)
A Anchor:
The A Anchor sets up the matrix, or in other words it lists all the variables, either in rows or in columns, and states the constraint (in this instance the cost), and may give a descriptor of what it may be (For instance, maybe you need to have a project from each division of your business, so you would specify here which division it comes from). This descriptor is Boolean with 1/0 as true/false like predictive modelling. Having more than one descriptor for each variable can become problematic if you are working with integer or binary problems.
B Anchor:
The next configuration options you will need to set is to set the constraints, or otherwise the business rules in the B anchor. While it is optional to have a field which labels the constraint, I would recommend including this in conjunction with the direction of the constraint and the (max/min) value for that constraint.
Q Anchor (Optional and not relevant)
The last anchor labelled Q is useful for quadratic objective functions, but this concept is above the scope of today’s blog, and an optional anchor.
Configuring The Alteryx Optimization Tool
Within the tool itself, there are 6 different options to configure:
- Select the format to the workflow (for my sample I have demonstrated “Specify the model as matrices”
- Select the problem type. Linear relationships are ones which deal with continuous variables, a mixed-integer which is the same as linear but you can also have binary or integer variables, and a quadratic which deals with curved solutions.
- You should select a solver (from my experience Glpk is preferred)
- Select whether it is a maximize or minimize problem
- You should select how you structured the A anchor (in my example, it was a dense matrix with variables in rows)
- You can also map the O anchor but I do that manually with renaming the headers using the select tool.
What About the Output?
The output of the optimization tool has 3 anchors,er
- S: This gives you the solution to the problem, for our example would state which projects to go with and the benefit of the projects.
- D: This gives a description of the problem you have set.
- I: This gives you a report which is not especially useful.
My Example In Alteryx:
The following images showcase this simple example in Alteryx.
Conclusion:
This is a simple example of using the Optimization tool, to maximize profits given a budget constraint, but it shows the potential of this tool with aiding decision making. If you want to know more, have a read of the Alteryx documentation.
