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.

Between This. Add-in, and Excel. So it's sort, of a substrate, for. A pretty, powerful in general application, so. There are there are things like the ribbon and the task pane that you saw but. Then there are custom functions which will illustrate in, a minute here with a Monte Carlo simulation, example. There, are events, that happen when, the user is just changing numbers on the spreadsheet that we're reading and writing cells we, even get the whole workbook contents, and create new worksheets all, those things are things that this application needs to be able to do and so it's interacting, with excel in many ways and, to, sort of picture that here. Is that model, and. I've got the, ribbon visible, and you can see there's a drop-down menu and there's the task pane with quite a bit of UI in there and then, there's a pop-up dialog. That's. On display but that's not all so. Here's sort of the rest of the things that are happening there's. A custom function on here this is the airline, revenue management problem, we're. Reading writing cells getting workbook contents, and so on now. The. Thing is that these api's, have. Changed, over time in. Fact. This is sort of a history showing you what has happened in the, last 30 years so. The very first version of solver was built using some, called dynamic data exchange dde and I don't think there's anybody here, left. Who has, never, worked with a DTE some. People might recognize the. Excel, macro language because there, are there's elements of that still around if you build an Excel function, right so we'll. Illustrate that and then, about 1993, is when comm and VBA came, into the picture, this. Was all a 16-bit, world at the time then we moved to 32-bit, comm. And VBA other, things happened we've inch them into the xlsx, format we. Went to 64-bit, comm, and. Finally in 2013 we had the first version of the office jeaious. Api's. And we actually built. And launched the basic solver, much, less than all of analytic solver in. And by the end of 2013 we were shipping that so we've actually been at this you. Know this JavaScript out in this thing for like six years, if. You're just starting today you have a lot of catch-up to do, and. So. So. There's, a lot of history here and so. I'm really going to be talking about how we leverage, our code and our users investment, over all this time one. Observation is that you could take a model, from. Excel 3.0, the solver, model in a workbook and run. It today in an, Excel online, or. In this analytic solver application, and it would work you know completely. Unchanged. Okay. So. How. Do we make this happen the issues are and this is going to be true for almost any developer, we have lots of computational. Code. We. Have and then we have lots of user interface, code. We're. Going to find that as the as the technology, changes in the architecture. Changes we have very very different ways to run even our computational, code that's independent, of any API is or any UI so. I'm gonna run an in process, out of process and so. On, and. In the cloud, then. We have these different these api's, that have changed over time, then. We have an addition the fact that we've got to make this all work in an environment where so. Solver, was you know it's been around for so long and it's appeared in many textbooks and it's, been used there there are literally millions of solver models out there on the web and so on and, we actually and people mad at us if these things did not work so. We've so that's part of our challenge. So. This is sort of like the takeaways. From the beginning, and. We'll come back to like this this sort of summary slide a couple of times of the. Big lessons that we've learned that I think you could apply in almost, not, just in Excel. That I deserve thanks for power bi but in almost any project. Because, almost any project relies, on others. Api's. So. First. Thing is use. But don't depend internally, on others API we'll talk more about that. Define. And consistently use your own internal. Ap is, segregated. Generalize, your computational, code and then, think about how your users work is persisted. And, think. Long term about, that leverage, open standards. Leverage. Other popular, a PID Excel workbook API is an example of a, proprietary. But very widely used, object. Or storage format, and. Then plan, how your users work, can be used beyond, the. Initial. Conception, of your own software and you can see that we we're able to do that and get some very big benefits like what you just saw with Microsoft, flow and then. If we have time talk, about leveraging your development, team over, time and. That's, sort of a more of a development, management issue. So. A little bit more background on what you know what solver, is it's. Used in many different applications that's very much a horizontal. Or cross-industry application. So these are some examples, choosing, lowest, cost shipping routes maximizing.

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.

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.

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.

And Several hundred thousand users as customers. But. Notice also that in addition to. Sort of leverage over time we're getting this ability to run optimizations. And simulations. Anywhere. Not, just in, you, know inside, of Excel not, just on the desktop. So. Again, we used. But didn't deep it we don't depend internally, on things like the JavaScript API is either the comm or the JavaScript, API and we've, pretty, consistently, used our own internal API. So. We've. Gotten this far now we're going to sort of shift gears again and look at what happens how do we deal with the issue that we're going to run this computational, code in very different ways we're. Gonna run it in process, in Excel. For Windows we might be, running it we used to run an out of process in, Excel. From Macintosh, and then we're gonna run it in the cloud as you just saw with Excel online, so. Now. We're sort of stepping, up to the sort of architect, architecture. Level and let's. Look at what we have here so you've kind of heard about we have these optimization, algorithms we actually have twelve, or thirteen different optimization. Solvers, and then we have a simulation, engine, and sample generation, and then we have all these data mining algorithms, to we're. Kind of leaving that out of this talk and then we have this eye interpreter, that that is able to analyze and interpret, Excel formulas, so. That's sort of the picture if we just look at excel but. We. Have a little broader of you and I'm just going to introduce these other elements in addition defining. Your model in excel we. We, let our customers define. Their models in two other ways one is in this, high-level modeling, language we call raise on you're, going to talk about a little bit later raised on is an acronym for restful. Analytics, solver object notation and. And. We'll, talk about that for a minute and then the third way that you can, define. Your model, completely, outside of Excel is encode. And C++. C sharp Java, or Python. Where. We essentially. Expose a high-level object API that. You can use to define decision. Variables and constraints and uncertain, variables and things like that. Okay. So, now. One. Way that we, physically. This is sort of the general architecture independent. Of any physical packaging, so here's the first kind. A physical packaging, we have these we, have these SDKs solver, SDK and Excel minor SDK that. Where, we expose things through an object API, and. If I shift over to the. Other machine here and bring, that Visual Studio, here. We have a little, this, is a set of a little sample applications, in c-sharp that, we can compile and run. And. And. Each, one of these is a this. Is a little app that does. One clever thing which is it it reads its own source file and then, Scrolls, around when you're running these examples, to show you the source code that it is running so. If, I actually go to let's say, second. Example here and this is a small, linear mixed integer problem and you can kind of see the objects hopefully. That's reasonably. Readable. Where. We have you know vectors of decision variables and vectors are constraints and. We can run it this will just yield an integer solution, for. Just a couple of variables but. The one the the example. That I'd like to show you that's more interesting is. Scrolling. Down the list a little ways it's. This one where, we, run some, c-sharp. Code. And, all that c-sharp code is going to do is instead. She ate a problem, in our object library. And then, say problem download and it's going to load an Excel workbook and. The, result of that is to instantiate in, our object model the. Formula, well they've got the objective and constraints and everything that that was that is actually in the Excel workbook and so, then we're gonna actually sell the model and then and then I'll put these lines up, to status. And an. Objective, so, I'm gonna actually open. And, let's see the one I want I, think. About the one I want. I'm. Taking a little bit of a risk here. Go. Ahead and try, this one. But. It runs so. This is that airline model, do you know the same thing we were talking about before the, constraints, all come out to be one which is what they should that means the flights are covered and the solutions the same and the objective, is the same, so. What's, happening there is if. We go back to. The. Slides. The. The. The SDK looks like this so, it's it's got Sai interpreter common, code optimization. Algorithms, and simulation algorithms common code it, exposes, that object, API and, it, also has the ability to load in an Excel workbook and interpret, it through the scienter prefer so.

That's The whole thing you know it's just a it's a dll solver, SDK da DLL, and excellence. De kado DLL. But. So now what what happens how does the architecture, change when we're dealing with desktop. Excel and Comm it, just kind of looks like this so many, of the same elements sigh interpreters they're all the algorithms are there, instead. Of our object API we're now using, excels. API the comm API, and the X ll8 I've used them both and we, talked to desktop Excel and our, model comes from Excel and. Our data comes from Excel and then we also have you know UI code to. Create a ribbon and task pane, in. Comm and using like yes, do and column and c-sharp and all that, now. What happens when we might want to move this to Excel online architectural. E well kind, of looks like this so. We, take the scienter Peter and the optimization algorithms, and we move them to a remote server and. We put a REST API in front of that remote, server and the, interesting thing is that what the server is going to do is, this going to receive an Excel workbook model, and it's going to run our at solver SDK just, like we just did in Visual Studio we're gonna run that on a on a as. Your back-end worker, okay. And then, then we have the actual. JavaScript. Add-in, uses. JavaScript, API is makes calls to excel that to, office EAS and also, you know creates all the UI their ribbon and test me and so on so it's really HTML. CSS and and lots of JavaScript, talking. To to. Talking to Excel and the, especially, nice thing about this which is so much better today. For. All of you then. The way things where our years ago is that this code this, JavaScript, API code, is, itself, leverageable, so. It works in Excel. Online which, is the new possibility. But it also works in Excel desktop works in Excel, for Windows and in excel for mac and, actually. And other devices like excel. So. That's that's, a real advantage. So. About that rest api so. We have this server, called raise on it, is a I. Can, actually show you, on. The demo machine. Just. In the browser. This. Is raised. On comm and this is simply a developer, portal you know a front-end, portal where you can exercise a REST API and. We. Have a little editor in here and then we have a bunch of you, know API calls, that you make I'm, gonna actually run something different here product, mix for just to show you an example of. A, raise on model it's like the product mix we were talking about earlier and so. This this, literal, payload, which is JSON, raised, on is embedded in JSON. Defines. The product, mix model very much like we are looking at it Excel and, we can send that off just that is the payload that literally, is it and then, it comes back from the, raise on server where the JSON, response which. Tells us that we should make 200, TV sets and 200 stereos and no speakers, and. Tells us things about to constraints and so on. So. That's that's. What the. Race. On API looks like now. So. And. This has allowed us to you, know to support all these. Five. Hundred fifty thousand cloud spreadsheet, analytics users for both optimization. And simulation. So. Here's a little bit of code from within the raisin server this is really from one of the backend workers, so. This is a c-sharp, but. All it does and it's using Azure API, this is obviously dependent, on Azure API, is because it's reading, from. An azure cue and then. It gets a reference within that as your cue to an azure table, and from the table it gets you know all the details about, the problem, that. It's gonna solve. And. Then it, instantiates. And calls solver, sdk and. It's, going to take the results and put them you know back in a place where they can be sent back through the front end API. Let's. Just give you a flavor of what that's, like, so. At this point we have leveraged. Our computational, code we. Have leveraged, our scienter, protec. Also. Kind of leveraged ourselves to K something, we built for other, developers, to use by. Ourselves you know because we're developers we want to build this race on, server. And, we, and now all we have something even better we because of the JavaScript, API the modern office dojo sapiens we have common.

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.

For Many many, days could've been good for several decades and, then. We thought a lot about how our users Word could be used beyond, Excel now of course we didn't like foresee everything, it, couldn't have but. But. I think you could you can apply these principles to, you, know just thinking of thinking, about it and and, trying, to structure your work to, make this possible so. Just, a few closing comments. Here about. How. Do you leverage your development, team across, changing, technologies. And changing api's. Though. The you know the questions are these how does a small team write and maintain so, much software, my. Developers don't like it when, we talk about lines of code I mean, nobody, does in. Fact they're proud of the fact when they refactor, our code and it shrinks and, you get fewer lines you. Know that's that's something that, was that we work, on but. It's some kind of measure and it was a surprise to me actually to see how, many lines of code we had, much. Of that is computational, code and. The you, know 12 different optimization, solvers and simulation. Engine and data, mining. Algorithms. You know it neural networks and and, classification. Regression trees, and when you're in logistic, regression all those things. But. How. Does a small team write and maintain importantly. Maintain so much software how. Does a team that used C++. And calm learn asynchronous. JavaScript. Anybody. Can do it you know but it's a question of you know you. Know what can you do to facilitate that, I guess and then how does the team use to shipping desktop self or learn to build and run cloud services so I just have a few simple. Comments. About this really now, this one Kiril. After you saw, the. First one is it. Is I think it's true of our team it's, small team it's like half a dozen people. They're. Good and that. Is actually the most important, issue you've. Got a you notice you if you have really good people everything, else becomes much, easier. Okay. Very. The second most important, team idea. Here is the observation. Is that the team has, continental, continuity. Over many years I think, our average you. Know tenure, of a developer, is something, like 16, years and the, shortest one is six years in logs does 26, years and. That has allowed us to build. Cumulatively, you know we don't forget. And discard a lot of code we and. We've been able to build a cumulative of cumulus II and. Finally this you know we've been willing to invest in early stage technologies. And, a good example of this is you. Know using things with a JavaScript, API is 1.0, you. Know it was a bit of a struggle you know the tooling wasn't all there and the documentation wasn't all there and the. Api's were pretty limited but we used them and we were able to ship products on them and because we have we. Have six years of experience in over 500,000, you know cloud spreadsheet, analyst users. Just. A comment about development. You know just. A sort of a plug for Azure DevOps if you're not already using it highly, recommend, it we it's what we're using now and we, had to go from you know old-style, development.

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.

2019-05-10 14:27

Other news