Calculating Columns Using Complex Algorithms: Optimization Problems

As a Power BI analyst, you may be faced with interesting mathematical optimization problems. There may be times when you need to solve basic, yet critical, optimization questions without having expertise in advanced mathematical concepts. Linear programming (LP) has made its way into data analysis with its simple requirements and immediate applicability. To show you how effective and easy it is to apply LP techniques to real-world cases, you will see how to solve a simple demand optimization problem for a manufacturing company using both Python and R in Power BI.

This chapter will cover the following topics:

• The basics of linear programming
• Handling optimization problems with Python and R

Technical requirements

This chapter requires you to have a working internet connection and Power BI Desktop already installed on your machine (we used version 2.114.664.0 64-bit, February 2023). You must have properly configured the R and Python engines and IDEs as outlined in Chapter 2, Configuring R with Power BI, and Chapter 3, Configuring Python with Power BI.

The basics of linear programming

LP algorithms are used in all areas where optimization, and therefore the economy of resources, is critical to the continuation of activities. To understand what this is all about, you need some basic math concepts. Let’s brush up on some of the geometric concepts we learned in college.

Linear equations and inequalities

We have all encountered the term linear equation at least once in our lives. A linear equation, in its simplest sense, is a mathematical relationship between two variables, x and y, of the form ax + by + c = 0, which identifies a straight line on the Cartesian plane:

Figure 14.1: Representation of the linear equation 2x + y = 2

Here, a and b are the coefficients that control the slope and direction of the line on the graph, determining how steep it is and whether it slopes up or down. c is the coefficient that adjusts the position of the line, determining where it crosses the vertical axis. Together, these...

Handling optimization problems with Python and R

As you’ve probably noticed, the large community that develops Python packages never stands still. Even in this case, it has provided a module that helps us solve linear optimization problems. Its name is PuLP (https://github.com/coin-or/pulp) and it is an LP modeler written in Python. It interfaces with the most common free and non-free engines that solve LP, Mixed-Integer Programming (MIP), and other related problems, such as the GNU Linear Programming Kit (GLPK), COIN-OR Branch and Cut (CBC), which is the default one, and IBM ILOG CPLEX. Using PuLP is fairly straightforward. Let’s put it into practice right away with the problem from the previous section.

Solving the LP problem in Python

The code that will be explained to you in this section can be found in the Python\01-linear-optimization-in-python.py file in the Chapter 14 folder of the repository.

First, you have to install the PuLP module in your environment...

Summary

In this chapter, you learned how to solve the simplest optimization problems by first turning them into mathematical models and then solving them using specialized Python and R packages. You applied what you learned to a real-world demand optimization case in Power BI.

In the next chapter, you’ll see how statistics can add a little spice to your business analysis.

References

• George B. Dantzig and Systems Optimization (https://web.stanford.edu/group/SOL/GBD/GBDandSOL.pdf)
• Lecture Notes in Linear Programming modeling (https://www.hds.utc.fr/~dnace/dokuwiki/_media/fr/lp-modelling_upt_p2021.pdf)

Test your knowledge

1. What is linear programming and how is it applied in data analysis?
2. How can linear optimization problems be formulated mathematically?
3. How can linear optimization problems be solved using Python and R?
4. How can multiple solutions provide the same optimal value in linear optimization problems?

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://discord.gg/MKww5g45EB