Optimization Problem in Power BI using R scripts: Part1

There are some traditional problems in most of industries like how we can reach maximum profit, sales, production or how we can minimize our cost and loos. These types of problems can be seen in different areas such as microeconomic and company management, such as planning, production, transportation, technology . Although the modern management issues are ever-changing, most companies would like to maximize profit or minimize the costs with limited resources.  To explain what is optimization problem let me provide some examples:

Production Company

Imaging that we have a company which produce two different products like Product 1 and Product 2 they want to know how much of product1 and product2, they should produce to maximize theri profit. they know that if they produce one tonne from product 1, they will earn 20 NZD and if they produce 1 tonne from product 2 they will earn 30 NZD. so we need to know how much of product 1 and product 2 we need to produce to maximize the profit.Despite this objective they also have some constraint like 1- Each tonne of Product 1 consumes 30 working hours, and each tonne of Product 2 consumes 20 working hours. The business has a maximum of 2,700 working hours for the period considered. it is humman resourece constraint. 2-another limitation is about the production hours, to produce one tone of product 1 weneed about 5 hours and for product 2 we need 10 hours. But we just have 850  machine hours. That is a constraint regarding the availability of machines that produce products. the linear programming help us to solve this problem (I will show how to solve it in next post via R codes). Other examples would be selecting the product mix in a factory to make best use of machine- and labor-hours available while maximizing the firm’s profit.  Picking blends of raw materials in feed mills to produce finished feed combinations at minimum cost.

Transportation

A publisher has orders for 600 copies of a certain text from San Francisco and 400 copies from Sacramento. The company has 700 copies in a warehouse in Novato and 800 copies in a warehouse in Lodi. It costs $5 to ship a text from Novato to San Francisco, but it costs $10 to ship it to Sacramento. It costs $15 to ship a text from Lodi to San Francisco, but it costs $4 to ship it from Lodi to Sacramento. How many copies should the company ship from each warehouse to San Francisco and Sacramento to fill the order at the least cost? Or determining the distribution system that will minimize total shipping cost from several warehouses to various market locations

  Scheduling

Schedulingschool buses to minimize the total distance traveled when carrying students. Scheduling tellers at banks so that needs are met during each hour of the day while minimizing the total cost of labor Developing a production schedule that will satisfy future demands for a firm’s product and at the same time minimize total production and inventory costs

Allocating Humman Resource

Allocating police patrol units to high crime areas to minimize response time to 911 calls.

Solution

There are many others areas and industries that have the same problesm. in all of above examples we have an Objective which we want to minimize or/and Maximize that. also we have some constraints that we should consider while we solving the problem. one of the great approach to cope with these types of problems is to employ “Linear programming”.

the main structure of the linrar programming is as below

Min/Max :Objective (like maximise the profit, production, or sales)

Constraints: c1 (like number of the labour in a company, cost limitation and so on)

c2

c3 and so on.

There are many software that help us to solve thes type of problems like MATLAB GAMS, and R

Also there is possiblity to do this in Excel using the SOlver component there (which has some limitation on number of constraints) In this new series, I am going to show how we can solve an optimization problem using R and Power BI. First I will talk about the main concepts on Linear programming in next posts via an east example.

http://wps.prenhall.com/wps/media/objects/2234/2288589/ModB.pdf https://en.wikipedia.org/wiki/Linear_programming https://web.sonoma.edu/users/w/wilsonst/Courses/Math_131/lp/default.html

Leila Etaati

Trainer, Consultant, Mentor

Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.