# Leveraging your code with APIs from Excel 3.0 to Office 365 Azure Power BI and Flow - CFS2021

Show Video

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.

Is Very old code it's actually a, real credit to Microsoft, I think that this code still. Works in. Desktop. Excel, for Windows only, but. It's. Been usable for many years in desktop Excel but this is useless and Excel online you can't use this code at all there. Are there's no Excel operas there's there's nothing like this so. That was 1994, so let's jump forward by. Let's, see 14 years to 2008. And now. We have some more code and this there. Should be some people here maybe, a few more people who can recognize you. Know like dispatch pointers, and, variant. Types and stuff like this so, this is calm and, this is of course using calm from, C++, instead. Of using it from high. Level scripting language like VBA, but. This code is doing almost, the same thing it's just it's retrieving, the formulas, instead of retrieving the values from. A possible, possibly. A multi area cell. Range on the worksheet now. The. Code that we saw a moment ago was you know was usable for a long time but not usable, in Excel, online and that's true again here, ok this code is, code that you, know we, can still use it but only on the desktop. And only for Windows not. Useful in Excel like on, the other hand let's. Look at this code which, is from, even earlier in 1991. This, is actually the top level. Completes. The complete function call but calls, a bunch of you know major functions, like phase one and phase two but, this is the original code for the simplex method for the linear programming, that. Was in the Excel, solver you know way back. Someone. From Microsoft. Saw. This and said dump tableau I don't know if you should say that and, so just to explain the tableau, is something, as in linear, programming it's a matrix of coefficients. And, has nothing to do with tableau. Software the company, and that's, one thing but the interesting, thing about this code is that this, code is, fully usable in Excel online and why is that I mean you can probably guess, right away that it's, pure computational, car and there are no API dependencies, in here. So. Now let's jump ahead in time some more to, 2015, and look, at some. Newer code which is is. This is now dependent on the newer. Office. ASAP is, and, there should be in a way to in front of Excel that run but. The point I want to illustrate here is first of all this is this, is pretty. Clean code. And it's a it's one of the nice things about the current JavaScript API is and. You can kind of guess what's happening here if, you're at all familiar with solver it will produce, something called the sensitivity, report and that's what we're creating here and you can see we're putting, out the title and we're you know we're just saying. What solver engine was used and we're, beginning to format, you know cells, and so on but we're getting the data to.

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.

So. About, race on so race on is you know stands for a restful analytic, solver object notation it, has two key attributes, it's, we. Work, in a world where there's other vendors of optimization, and the simulation, and data mining software and several, of them offer high-level. Modeling languages, so. Raised on is our high level modeling language but the, way it's differentiated. From all the other modeling, languages is twofold. One is it contains the entire excel, formula, language as a subset, raised. On sort of on the level of MATLAB if you've ever used something like that and if, you had ever come from like our our. Domain you would hear about things like gams and ampulla names and so on these, are high level modeling, languages but raised on it's, a similar high level language but you can do essentially anything that you could do in Excel use, all 500, Excel or whatever the number is built-in functions, in, race on and the second thing is that race on is embedded in JSON and valid. In JavaScript, so, if we look at just. A little segment of a web page you. Know and it's doctype and HTML, and head and script and then we drop into javascript we, start writing some JavaScript, we, actually define something really use you know the server endpoint, and then. We, say raise on model :. Open. Brace and the rest of it is pure raise on because. Raised on is JSON it's all you know valid constant, and inside. Raise on you, can see things like some. Product, d9 f9m, mult, the 11 of 15 that all works because. Arrays on contains the entire excel foreign language. You. Can also actually write raise on without using, you know any reference to excel so this is actually that airline. Revenue. Management model, then you know the model of no-shows the simulation, level written. Out in arrays on not referencing, these cells just referencing, symbolic, names, so, you can do that as well so. Let, me now actually, show. What happens when what. We get out of this and, a, key point is that we can translate from Excel. Models into razor models, so. Let's go back to. To. Our airline crew scheduling, example, we. Ran this in Excel online we could run it in excel for Windows and then we ran it through solver STK but. Now and, we actually turned it into a single web page that, was sort of stand-alone we, can also just. Say create app raise, muncom and all this does is pretty much generate. The model and then load it into the raise on editor so that you can you, know use it and run. It directly. From here. But. What. We want to do now is another, option. That you may have seen on that. On. That pulldown menu which is create app power, bi now. The only thing is that this takes about a, minute. We're. Running these different Microsoft tools and nodejs and, so on and it takes them you know that's agonizing, amount of time when you're live, presenting, or watching, so, if you'll bear with me we've. Done. That step already create a power bi and that, creates a ppi via this files, what, power bi calls a custom visual and so we're gonna go into a, power. Bi, we. Should here make sure we're still alive because it's been sitting here for a while and. We'll, edit this report, and. I'll make one point. Let. Me actually bring a desktop, Excel for just a second, to show you this, is that same model in desktop, Excel but, I've got one more thing in here which is I'm marking. Certain, cell. Ranges, as things. I want to be able to change, and when I'm in power of the I so, this is you know this, is data binding, from. Excel, into power, bi so. Now. I'm gonna import that custom visual so we'll import it from a file it's on this local machine of course it'll warn me and. I'm importing, code and. Let's, see I do want this, one. So. We're importing a custom, visual and and. I'm. Gonna run it and I'm going to show you what's inside that a custom visual so it's on the, here so we'll click it and, bring. It onto the worksheet and, our. Onto the canvas, and. You. Can see that in the well it's asking for data right, won't. Do anything until it, has data this is a live model this is not like going to be a static rendering, or anything it's a live model that will change and. So I'm going to drag.

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.