Leveraging your code with APIs from Excel 3.0 to Office 365 Azure Power BI and Flow - CFS2021
Alright. Let's get started hello. Everyone I'm, Dan, Fowler I'm the founder and president. Of frontline systems, website. Solver, comm and. We're going to talk about leveraging your code and your users investments, actually with. A series of api's and, about, how those api's change. So. Here's what we'll cover in this session. First. We're going to talk about leveraging, your code and I really do mean reusing. As much of your code as you can over, time, now. Technologies. Do change API is change. And. You, will have to write you know new code to deal with those api's but if you approach it right you, can leverage, a lot of your code. Across. Those changing technologies, we'll be talking about that and one of the one. Of the obvious ideas. That I just want to make sure everybody thinks about is is. Creating, your own layers of abstraction and. Using. Your own internal, API is consistently. An. Even, more important issue in many ways is preserving. Your users, investment. In your app that is almost. Any application this, goes well beyond like Excel or power. Bi or any things we'll be talking about your. Users put, a lot of effort into your app they. May use it to create, and store data and you, and the way that you store that data and the ability to recover it later is important your, users, may create you know code or scripts or something like that in. Our, case people. Are building, in many cases very ambitious, workbooks. With lots of formulas, in Excel and we, want them to be able to that's, an investment they make that we'd like it to pay off for them over time and we're talking about a lot of time. We're. Gonna be you'll, see that there's a sort of a 30-year, recap, here, where we've been able to preserve some, users investments, over that. Whole three decade period. There. Was a focus in this session on office jeaious and rest api's. For. Excel because their new release. Of api's is very topical and has enabled some of the things that I'm showing you in. This session. But. We'll be talking more generally about, you, know how you approach things as a developer. So. The story, here the is is his. Hours from, solver, in 1989. Now this is really a long time ago 30, years ago it's, at. The time this was done Lotus, 1-2-3 was, king of the hill and Microsoft. Was a relatively, small Challenger, for. For. Lotus and windows. 3.0, had not just, been had not yet been launched it was launched in 1990. And so. The diagram on the left of the screen here is actually. From the spec that we, and when Microsoft, program managers wrote in, 1989. For what the solver, would look like and one of the reasons we wanted to do this is because Lotus. King, of the hill had, this solver, in Lotus. 1-2-3 Jie, way back at that time, over. On the right is actually a dialog that still exists, in desktop, Excel there. Is a still version. Of the solver available, in desktop Excel but, nothing. Like this is available, in Excel online however. Something. Better is available in Excel online looks like this is analytic. Over actually there is a free. Solver app in Microsoft, app source from, us that you can use in Excel online but. This is a picture. Of this, full analytic, solver product which is a very large, and ambitious product. That does everything. That solver, did squared. And cubed and lots of other things Monte, Carlo simulation, data, mining forecasting. Actually, a fold suite of machine learning capabilities. Is all in there, and. Not. Only that but throughout, this journey in, the, process of doing what we're showing you here we. Built, out something, on a juror which, we call a raise on server and I dig into this a little more and because, we did, all this work over the last few, years we have more. Than 500,000 about 550,000. Cloud, spreadsheet, analytics, users most of them on Excel online some. Of them on Google sheets but. We've, been able to do what we've done because we, have this as your hosted, service. So. Let. Me switch to a demo machine here, make. Sure that comes up and show, you a real quick demo without explaining, very much of how. This works, we're going to, to that and and at, a later point but. What you can see here first of all is an Excel work, a workbook. With the model and it's a solver, model. Notice. That it is in the browser, this. Is Chrome so we're running Excel online and. The, problem we're solving here is the classic airline crew scheduling, problem that is I've, got crews starting, out at three, different cities Salt Lake and Dallas in Chicago, I've. Got a number of flights that I need to cover with working, crews, but. When a crew flies from one city to another then they're in position, they either, have to work, on a flight, just to somewhere else or else they have to travel. As passengers, to. Somewhere else and get, back home at the end of the day so.
So. Looking, at this this. Excel model the decisions, we need to make are you know which paths, so. What you see in, you. Know in each of these rows is a is called a rotation, like. The first one is the crew acts. As a as a working. Crew going, from Salt Lake to Dallas and again going back to Salt Lake and, the second one they are working. From Salt Lake to Dallas and they fly back as passengers, from Salt Lake, over. Here essentially I need to have one crew exactly. One crew on every. Every. Flight that I'm flying now, this is a regular spreadsheet, what-if model so I can you know turn these things I can make the decisions and say alright well but. That crew aren't there and we'll just pick. The first choice for the Dallas crew and the first choice for the Chicago. Crew and we, get something we get a cost. To. The airline of what. We're going to pay these people but, we don't really have what we want there are zeros meaning we don't have crews on some. Flights and we have two crews on one flight, so that's not a good idea at, all so. Let's. So. What so the problem here now that we're going to try to solve is to find the. Optimal, set of choices here, so. That all the all the flights. Are covered and we, do so at minimum cost to, the airline so. We've set this up already we're saying we want to minimize this cell that computes total cost and, we, want to change these you, want to find values for these cells, here. And we and. The. Main constraint, is that, we want all these cells to come to me exactly one so. We can do that by clicking. Here and this now, goes. Off to this cloud, server brings, back a solution. Plugs. It into the spreadsheet here so you, can see that we have now covered all these flights so this was just a calculation. On the spreadsheet at the end all. The flights have exactly one crew and we have, minimized our cost at 8775. So. That's the sort of thing that solver does and we'll dig into that a little bit further but. Now let. Me actually go to analytics.
Over On the ribbon here, and. And. Let's. Just talk about how we would actually use this I mean it's nice to see this as a spreadsheet in Excel but to, operate to use this in an operational, sense we would have to you, know like get these assignments, out to the crew right through, an operational, you know business, system of some kind, so. I'm going to I'm going to show you a little bit of magic. Here I think. Two. Things one is this is an excel and it's bound pretty tightly to Excel and Excel is not so easy to carry around in a operational. Business system so, the first thing I'm going to do is I'm going to take this model the. Excel model and I'm going to turn it into something. Independent, of Excel which is a web page and, so. It's just created, that web page and, mcafee wants to scan it for security. But then let's just open this page. Now. It's a boilerplate HTML. Something. About the Excel. Model is appearing, in this upper pane there's. A button here you. Might say well maybe we can actually can we solve this model even though it's only a single. Standalone webpage so. We'll go ahead and make that request and sure enough if, you can see down here we get back a solution, to. The problem it's the solutions coming back in JSON but if you recognize, those assignments, we, have the same crew assignments, in, this standalone webpage. So. Now if I had that so. Somehow I'm solving this model completely outside Excel through. A standalone would pay could be the basis of a mobile app, but. Now how, are you going to get this out into the field so again, I'm just going to show, you this right now without explaining it too much but I Here I am in Microsoft, flow and. I have a little flow that's going to use a sort, of hosted version of that web page and, I'm. Going to I'm gonna notify the true and I will look at this flow a little later but, right now we're just going to run it, and. All it does is, it. Actually. References. The results of that model, and of finding that optimal, solution and then, it sends, emails out through through. The exchange server and since it's in an email to me and I, am you know this is my real life ul you, can see that this email just arrived and this is this, and these are my assignments, for the week, so. All that works and, we you. Know we're some distance, away from desktop, Excel. Okay. So. Switching. Back, so. Let's talk a little bit about you know how we how, we made all that happen but, but there's more, so. First, I want, to just give you an idea the. Depth of the interaction.
Insurance On stock portfolios going, all the way down to minimizing. Damage from a radiation. Beam aimed at cancer, you, know aimed at a tumor and trying, to strike that that the, tumor cells and avoid all the the normal, healthy cells to, minimize the damage that's actually an, optimization, problem that. Is used in practice, so. But. These things tend to have in common you'll notice you know minimizing, and maximizing appearing. Again and again and choosing, or similar. Words like scheduling and allocating, resources, and. And, that's that's the nature of an optimization, problem and if you you know you google on Optima see problem you'll get something like this on the, web and so. This is just from a webpage so. What we're doing is we. Are choosing values for a set of decision. Variables x1. To xn and. And. We. Have certain, constraints, that are. Functions, of, the. Decisions, that we make these. Are things like our we have limited inventory or we have you, know. Only. So much dollars, that we're going to allocate who it's not portfolio we have only so many surgeons, and operating rooms and so, on and then, there's something we want to maximize or minimize like, maximize, profits, or minimize costs. So. Think. About this in spreadsheet terms the x1 to xn are going, to be input cells numeric cells on the spreadsheet and f. Of, X and G of X are going to be formula, cells so, if we look at a worksheet. Here this is a different. Kind of example the so called product mix example, where. We're trying to make three, different products, TV, sets and stereos and speakers out of a set of common parts, and. So what, we want. To achieve is x1. And X well, just put it on the worksheet, on the display, like this, x1. To x3 are, the decisions, we need to make how many TVs. And how many stereos how many speakers to make and, and. Then we have formula, cells that are to the constraints, where. We where we simply compute how many parts get used when we make certain products and then, we have something you want to maximize, which is profits so. That's I just want to make sure you have in your mind what an optimization. Model is all about. So. Let's, talk about a look, at some code so one of the things we obviously have to do is retrieve, you. Know values, from cells there and in. A moment we'll reciate formulas, from cells so. In, c++, this is a little code fragment. That, shows how we used. To do this and let's. See how do we how do we have people, here who recognize. Something, in this code like Excel offers or. Excel MRF so we got a few people yeah sure, enough so this goes way back you can see the date in 1994. And. That's when it was actually, copyrighted, written. A few years earlier than that this.
Populate This report, from. An internal API, and. So we're we're referencing app dot results dot objective. Neutral. Value 0 that's. That's, I think, obviously a JSON. Reference, right now just D referencing something, that's part of our internal API so. That's, that's the what, I'm trying to illustrate here is that that, establishing. And consistently, using your internal API s is. A very good practice, so. One more now one other aspect of this is preserving, users investments. Over. Time. Make. Sure this machine doesn't, better. Sleep on me there, we go, so. So. Again users have created millions of workbooks that. Have. Solver models in them and so. What we well what we did is we there's, there's a bunch, of information we need to have about the, structure of the model and we, essentially, serialized, that you know we extend the Excel workbook format. Through the resort of the facilities, that are there and we persist, the information. About. Like this is a simple thing you could try yourself if you go, into a cell that, has a solver, bottle in it and you type equals solver underscore, pre in a in, a cell you will get the precision value all. The way from the basic Excel solver up to the present, and analytic solver some. Of the ways that things are stored are more more, subtle than that but. This one is fairly obvious. But, that is paid off over time for for, a, really. Large number of users this. Is why a southern model still. Works today, 30. Years later. So. Recapping. Stage one. So. We've we've been able to make it so that our computational, code the things that they actually do like the simplex method of the GRG nonlinear, method for, optimization. We've been able to reuse that over many versions of our, software products, and we've, done that by you know by isolation, right and by, using, internal, api's so. We have something in called, the solve API internally. I'll tell you that we haven't been perfect and there, are times when we haven't fully consistently. Used our own. API as we've sort of reached around those internal. Layers and we've paid a price every time, for. Doing that but, that is certainly a good practice and then. Thinking. About it thinks the most important lesson. I can convey, here is thinking. About what. You're. What's. Going to happen to your users, investment, in your software users data your users models. Or whatever and. And. Really, think long term about that, we've. Been able to it's, been very advantageous for, us to to, leverage, the Excel workbook format. Okay. Now I'm gonna I'm. Now I'm gonna go quite a bit deeper here and, get. A little more tactical. Or a little bit mathematical. I guess and, so, to take this to another level of, you. Know of leveraging. Code over time so. Something else that we do in analytics, solver is we do Monte Carlo simulation, now, some of you may be familiar with this some may not but, the basic idea is very simple we're, dealing with we, have quantities, in a spreadsheet. Model or it could be a model in a different kind of language but. There are uncertainties we're dealing those may be on certain interest rates may be on certain arrival, times of people coming in to bank. Tellers or on certain arrival, of calls and a call center all, sorts of things like that and we, want to model. That situation, and and first of all just understand, the. Some range of things that can happen that's what you get for money Carlo simulation, and then typically we want to do something about, to mitigate risk or. To maybe even take advantage of, the of, the uncertainty, and that basically. What we're going to do is we're going to have certain inputs, that, are drawn from probability, distributions, so they're not fixed numbers but they're they, are drawn from a certain distribution of numbers and we're, going to calculate the model thousands of times. Okay. So, this, is an example of, a, model where we do that this. Is where. We have custom. Functions so. We have we. Have a distribution. Where we're actually, driving. From number no-shows let me actually switch to, this. Other computer and. Show. You, so. This is. This. Is that Monte. Carlo simulation, model and in this cell we have this function called sine log normal and, if we actually double, click over here I, mean. Sort of will depict that probably, just redistribution. We're going to sample from it and. Then down here where we're. Interested. In that total revenue value, what the model basically does is it simulates a flight taking off many times when. Some passengers, are no-shows you. Know they have a resident they have a seat but they don't show up and they kind of want to get the value of their ticket back and.
So We could oversell the flight which are actually doing here we're over selling by ten to ten seats, or. We could sell only that many and we can explore what happens across. Not, just one take off but like a thousand, takeoffs so. To. Just show you what happens when we run this the idea here is we're going to say, we want to run the simulation for a thousand, trials drawing different. Samples every time for the number of no-shows so. We're, running this again in Excel online and going, out to remote server and that was a thousand, Monte Carlo trials. Coming. Back here and I want you to think just a for a second about what, would have happened if we had, how. Fast this would be if we were trying to make Excel recalculate, a thousand, times. Is that that would be challenging and just to under underline that a little bit I'm just gonna go into the parameters, here and I'm, gonna change it from a thousand, trials to 10,000. Trials. So. Now it's 10,000, trials and we'll. Just run it again to see and see how much longer it takes. And. It, doesn't take any longer at all to speak of right, and, you, know so on so we're going to talk a little bit about how we how. We achieve that. And. That, let and that that. Leads us to other. Ways that we leverage our code, so. The. Primitive way to do simulation, in Excel is to calculate over and over again you know may make Excel do that and the, issue is that Excel is an interpreter, for a language the from the excel formula language and an. Interpreter is anybody. Ever. Written that kind of interpreter or compiler maybe not but, if you think about this for a while it takes longer to figure out that there's. A one that. There's a plus sign there's b1 and we're supposed to add them and then get those two values and, then actually do a forty-foot add you know that. The overhead of doing that is about an order of magnitude, it's about 10x you know 10 interpreted, code is usually about 10x slower than compiled. Code so. Excel. Is an interpreter if we ask you to calculate. A thousand, times it's going to interpret, and calculate, a thousand times, so. If. We could, do something about that, and. It's specifically what we what we did is we we. Built our own interpreter. For the excel formula language so we can look at and interpret the formulas, and since. We know we're going to do with Monte Carlo simulation, and, we're going to do exactly the same calculation but. With different random samples, a thousand. Or even 10,000 times we. Can interpret once alkylate. A thousand, or 10,000 times and, by. Doing so we actually the end the end user experience speed-up. Is like a hundred x it's like two orders of magnitude so. This is what we call the polymorphic, spreadsheet, interpreter, and I, want, to elaborate too much and where that name comes from but, I'll show you a little bit of code so. This is actually, the the function, in C++ where, we're. Because. We have our own Excel, interpreter, and this is this code. Is independent. Of any Excel API. We're. Actually doing. This this calculation, and right at that middle point where it's pointing, where. There's a for loop int if, I equals 0 fi lesser than n forms so, informs is the number of top-level, cells that we're going to look at that. We're interested in getting results of if you think about that. Model which I can probably just depict, for. You over there there's, actually, only one cell, that we want the top of the value of and that's the the table of revenue, so. That loop. Is going. To be executed one. Time. Okay. And then in in the middle of that loop we're going to call this eval real Veck function, which is going to essentially. Do an array, evaluation. Of all the formulas in the model one time much faster, we actually have code here where if this should fail because. The user interrupts it or or there's, a we get run out of memory then we fall back to conventional. Excel calculation, and if you can see in the very bottom, line there we, have four entity, ie running. From 0 to n trials and n trials might be 10,000, so. The fallback is much lower but. Now the real part of the point here is that this is code that we can use that, doesn't, depend on external. API of any kind. Now. It turns out that optimization. And here I will have to gloss over some of the some. Of the math of it if you will but but. Optimization, has this problem in. A much bigger way ok, so, the. Problem with with. Optimization, and, I, can see that that's somebody in. The. Microsoft people who who. Handled. My slides changed, some words. But. Will overlook, that ok a primitive, way to do optimization. In Excel is to calculate over, and over again an.
Effective Way to do it is again to interpret formulas, so. The issue is that optimization. Is a search for the ideal, you know the optimal, combination of, values of possibly possibly. You, know a few, dozen cells but it could be hundreds. Thousands. Or and for some of our current, customers, it might be a million or two million different, decisions every. One of those represents. A dimension. In a multi-dimensional space, that we're searching, to find the ideal combination, so. So. Doing. You know just, asking Excel to calculate over, and over again and wandering, around is, not an effective approach, an. Effective approach is to interpret the formulas, and then when we compute, values we're also going to compute, gradients. Derivatives, or gradients, which are going to tell us the direction in which things are changing, as we. Adjust to the decision variables and, it. Also we. Also by looking at the formulas algebraically, we can actually learn some more things about. Just, how hard this search is going to be like, over on the left that's that's, a smooth convex, function, really easy to find the maximum and the middle one is kind of harder because that's a non convex objective. Function we have to search and then, over on the right is the effect of using. If functions, and choose functions, in your Excel model, with. So you have many constraints, and you you ended up creating something that is very much like a maze and you have to find your way through it it. Makes it quite difficult so, but. By interpreting. The formulas, we gain a whole lot in, our ability to solve these things so. This is another little bit of code from inside the sign, interpreter, where, we are we. Are computing, for an expression like a1 times b1 we're. Computing the function value, in the middle there where it's kind of pointing at that mul function, call but, then just below that we compute the gradient of, a1. Times v1 with. Respect to all the decision variables by, calling this other function and, the. Advantage, here is again like orders, of magnitude speed if we have a thousand. Decision variables its order. If it's, roughly a thousand, times faster we. Have a million decision variables it's not even worth talking about you know it's. It's many many times. Faster. So this is something that we do inside. Our code now all that code is, is. You. Know leverageable, across time and we and it's been worth our while to to. Build this whole sigh interpreter, because, we've been able to use it over many, years actually and over. Frontline. Has, about. 9,000. Plus, organizations.
UI Code as well for Excel desktop and cloud and we've. Done this you, know in the ways that we have already talked about. Ok. Now, we. Want it we want to help people we helped, our users preserve. Their investments, and continue to use their business but we'd like to go further we would like to enable, them to leverage their investments, so they they built a work you know they built this elaborate, model in Excel and we won't help them leverage it outside or beyond Excel so. How are we gonna do that so. The first thing question is what would our users like to do above. And beyond Excel so. One. Thing is they, would like to you, know you build a model it's a typical situation in a large organization, is you, know the some analyst builds the model and figures out you know how to solve the crew. Scheduling problem and at, certain point they say well we're now gonna we're gonna try to put great we're gonna put this into production so they bring it a developer. And the. Developer looks, at this Excel model and says well I don't know I can't work with that so. I'm gonna start over I'm gonna write the thing in code and call, some library. Hopefully. They call ourself STK, because then they can just load the Excel model, but. But you know you run into that kind of situation so, you'd like to make it you you certainly want to enable that scenario and there was one of the first scenarios, we did enable, you'd. Also like to deploy, models. For, client-side. Custom, applications, and if you recall a minute ago we were, sitting in Excel online and, we say create, a web, page and. We created a really, what is a skeleton, web app application. A single page app. Well. Which can also be the basis of a mobile app and we're. Interested I'm going to talk about how we do, that but. But. Both of those. Approaches. Require. Us to write code, and people you know would rather have. Low code or no code solutions. So we're going to talk about doing that too and and, I want to show you how we can deploy an excel model just, point and click into. Power bi where. It can run and we can take another look at that Microsoft. Flow example, and and. See exactly how that, you. Know that little bit of magic happened, at the beginning.
In Two routes here, so it has that and then a dragon, crew. Cost it, has there an assistant has it it, goes off it makes a you know REST API call to the raise on server saying. I've got an instance. Of this model I want, to solve it bring, the solution back and then and, then graphically, represented, if you, think about what we had in Excel, it's. You, know the left-hand chart is just ones and zeros so those are these selected, routes and over on the right we're, covering all the flights right, we know the constraints, are satisfied, and. Just to show that this is truly a live model if I take away that. That, crew cost and I'm going to substitute the other the. Other data set that I have here but, that in its place it will run again and come, up with a different optimal, solution, where. We've rearranged. The selection of the routes, so. That's something nice that you can do in power, bi. And. Let me actually switch back. Here. So you saw that happen, from it Excel the power bi and, I was talking a little bit about going from Excel into a business process, using. The flow and this picture is sort of a general purpose picture I actually got this with, permission from one, of the Gartner analysts who's talking about this and, at, the bottom is sort of a generic you, know it's actually an order order, of fulfillment, business. Process which, might include automated, steps and manual. Steps and at, a certain point we're making decision. To accept or reject the order and we, might do. That in an operational, business process, by putting, something on a screen in. Front of, somebody who makes that decision you, know on the you, know in the order entry center or on the you know on the shop floor if, it's if we're delivering related thing and. We could show that in power bi or in tableau but. We can also make. This fully automated you know in some cases we, we could just decide. Based, on criteria, to. Accept or reject the model based. On based, on use of an analytic model so. What we're doing in, in, Microsoft, flow a moment ago is sort of like, this and what, I'd like to do now is just show you.
Back. On this machine. Here. We are in flow, and this time let's look inside this, flow it's extremely. Simple so. We reading the flow and it's only got three, steps. We. Manually trigger it this. Is a sort of magic part, we. Invoke an instance, of the, of, this race on model and actually if I go back here and. Find. That same, instance, it's one of my oldest, ones. This. One right here this, is an instance of this you know airline crew scheduling model. So. I'm. Embarrassing. This here and the magic of flow is that we built a very simple custom connector around, our raised on REST API right. And so. The. Magic of flow is if we go in here and actually like, create, or edit this. This step where we're you know we're going to, send. An email. Flow. Will flow knows, what comes back in JSON, in the response and will tell us oh would you would you like the status or would you like the objective. Total, final value like we already have here, or whatever you want you, know I you, just click, and it will appear in this. Now. In this flow and, so. If we if, we actually test it again here, and. I'm gonna perform the trigger action manual manually, of course. I'll. Let it run it's. Just going to do the same thing again, but. Now we can actually look in, Microsoft. Flow we can look and see what happened, this. Is the most recent, instance. And. We can actually see what happened at each step so, like in here we can't you see that the that, the the. Result came back and we, can actually see how it composed, the. Email down here so. Those are some of the nice things you can do with. With. Microsoft. Flow. At. A more general level you, know we have found ways to leverage. Our users investment, you know we've enabled them to to. Get from, Excel into par, bi and Microsoft flow and other things in a in a, much faster effective. Way. Okay. So we've done these things we've we've we. Can say that sovereign, models from way back in time still work risks. Our models and early our earliest Monte Carlo simulation, models, still work, and. They work not only in Excel but in part of the eye and power apps and flow and also, if you want to use them from a programming, language and so. We've, done this by. Careful. About seeing that how our users work is persisted, and. Leveraging. XML, and JSON of course and then reference, leveraging. The Excel SS storage format which, has been good.
To Add, a j'l methods and. Has, been helped to do that so. Final. Takeaways, these. Are the this is really the same slide that we saw near the beginning. Work. To preserve your users investment, work, to leverage your own code and. Leverage. Your development team so. That's it and let's. See I have two minutes and 46, seconds, left. And so, do we have any questions. Stunned. Silence. Okay. Okay. It's it's actually for people, might may be seeing this on video later it's 6:00 p.m. and we're, all ready for dinner I think so thank you very much.