AI and Business Rule Engines for Excel Power Users E-Book

Paul Browne

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 554

Veröffentlichungsjahr: 2023

Beschreibung

Microsoft Excel is widely adopted across diverse industries, but Excel Power Users often encounter limitations such as complex formulas, obscure business knowledge, and errors from using outdated sheets. They need a better enterprise-level solution, and this book introduces Business rules combined with the power of AI to tackle the limitations of Excel.
This guide will give you a roadmap to link KIE (an industry-standard open-source application) to Microsoft’s business process automation tools, such as Power Automate, Power Query, Office Script, Forms, VBA, Script Lab, and GitHub. You’ll dive into the graphical Decision Modeling standard including decision tables, FEEL expressions, and advanced business rule editing and testing.
By the end of the book, you’ll be able to share your business knowledge as graphical models, deploy and execute these models in the cloud (with Azure and OpenShift), link them back to Excel, and then execute them as an end-to-end solution removing human intervention. You’ll be equipped to solve your Excel queries and start using the next generation of Microsoft Office tools.

Leseprobe

AI and Business Rule Engines for Excel Power Users

Capture and scale your business knowledge into the cloud - with Microsoft 365, Decision Models, and AI tools from IBM and Red Hat

Paul Browne

BIRMINGHAM—MUMBAI

AI and Business Rule Engines for Excel Power Users

Copyright © 2023 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Group Product Manager: Alok Dhuri

Publishing Product Manager: Harshal Gundetty

Senior Editor: Nithya Sadanandan

Technical Editor: Jubit Pincy

Copy Editor: Safis Editing

Project Manager: Prajakta Naik

Proofreader: Safis Editing

Indexer: Pratik Shirodkar

Production Designer: Arunkumar Govinda Bhat

Developer Relations Marketing Executive: Deepak Kumar and Rayyan Khan

Business Development Executive: Thilakh Rajavel

First published: April 2023

Production reference: 1100323

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-80461-954-4

www.packtpub.com

To my parents, Robert and Vera, who instilled a life-long love of learning and who supported me in many steps of my journey. To my wife, Joanne, who has helped me become a better person, and to my sons, who are starting their own journeys but who have always made me proud.

– Paul Browne

Foreword

With the explosion of interest in Artificial Intelligence (AI) in recent years, I am more convinced than ever that the rules-based approach to AI alongside machine learning is the most accessible route for Excel users. That’s based on my own, and my team’s, work with business users like you, looking to capture business knowledge and rules in a way that can be easily shared and executed. Right now, the community is especially excited about the next generation of tools, making it easier than ever to deploy decision and machine learning models together.

It has been about 15 years since my first code contribution was accepted by the Drools project team – a brand new parser for the Drools Rule Language (DRL) that landed just in time for Drools 5. Since then, I have had the privilege to learn and work with the most brilliant engineers in the industry and the opportunity to collaborate with an amazing open source community.

Drools was already a widely used and mature open source rule engine. The community was about to release Guvnor, a set of integrated tools to support users managing their business rules, life cycles, from authoring to packaging to deployment.

But so much more has happened since. The Drools engine not only evolved but also helped advance the whole field of rule engines and expert systems with the introduction of the PHREAK algorithm. Drools was also one of the first to introduce a unified programming model for business rules and workflow working side by side, sharing the same concepts and APIs.

Outside the runtime engines, the tools also dramatically expanded. Guvnor evolved and became Business Central, a full Business Rules Management System (BRMS). Business Central had the ambitious goal to provide a user-friendly interface where developers and business practitioners could collaborate; progress was made, laying a solid foundation for future improvement.

In parallel with all these advances, the community itself evolved – from the single Drools project to a family of leading open source projects that play a role in delivering solutions around business automation and artificial intelligence named KIE (Knowledge Is Everything, pronounced the same way as the word key (/kē/) in English).

One turning point in the business rules and BRMS landscape was the introduction of the Decision Model and Notation (DMN) specification by the OMG (Object Management Group). This powerful spec defines a graphical notation for decision models and a set of semantics for decision modeling and runtime execution.

Today, DMN is already widely recognized as a leading standard for decision modeling and is used by organizations around the world to manage their decision-making processes. Moreover, the decision management discipline associated with AI has become more popular than ever, driven by the power of the DMN standard. Another advantage of the DMN is that it’s vendor-neutral, and it’s possible to find a variety of training material, books, methodologies, and other content from practitioners not limited to any specific vendor.

Drools introduced official runtime support for DMN a couple of years after the first release of the spec. Since then, the team has joined the OMG to help shape the spec’s future and shift all high-level representation of business rules and decisions investments to it. With this strategy in place, and as the DMN spec increased in popularity, the KIE community was ready to start a new cycle of innovation…

Kogito began as an initiative, which later also became a project. All KIE technologies were containerized at this point, and all investments had cloud-first in mind. However, the team felt the need to go one step further: we needed a new initiative to rethink business automation in a cloud-native fashion.

A new set of tools also emerged from this initiative. First, the Business Process Model and Notation (BPMN) and DMN editors were extracted from Business Central and made available as extensions for VS Code. And the team didn’t stop there – we were motivated to make the KIE technology more accessible, and that’s when we introduced dmn.new, incorporating a runner that creates a dynamic data-input modeling experience inspired by spreadsheets.

And this is where Paul’s new book comes in handy: Paul is a long-time community member with a talent for decoding the universe of business rules, decision management, and AI for practitioners and advanced Excel users using accessible language. With the content of this book, you’ll add new powerful tools to your tool belt.

Very few technologies survive and can claim relevance for an extended period without a continuous track record of innovations. This community owes much of its success to its ability to foster a warm and welcoming environment. Mark Proctor, a long-time friend and mentor, has played an integral role in leading the community.

The KIE community has been fortunate to attract, build, and retain many other talented individuals, starting with Edson Tirelli and Kris Verlaenen, who laid the foundation for its success—followed closely by Mario Fusco, Maciej Swiderski, and I, who have continued to expand and bring new ideas and perspectives. And now we have the rise of a new bright generation that includes names such as Eder Ignatowicz, Tiago Bento, Matteo Mortari, Daniele Zonca, Enrique Gonzales, Tibor Zimányi, and Ricardo Zanini, who represent the future of the community and are poised to help it continue to thrive for years to come.

And just now, the KIE community is starting a new exciting chapter with IBM joining Red Hat in the stewardship of the community. I’m personally excited to share this part of the new chapter. KIE is in the process of moving to a new home: the Apache Software Foundation. At Apache IBM, Red Hat and the wider community (including you) will have the opportunity to expand the boundaries of business automation and AI even more, together!

Alex Porcelli

Chief Architect for IBM Process Automation and Decision Manager Open Edition

IBM Business Automation

Contributors

About the author

Paul Browne is a product and program manager currently working for Europe’s largest VC by deal flow. Most recently, he led a team from three partner institutions that won an EFMD award for ecosystem development, training over 100 eurozone companies to implement digital solutions together. This continues Paul’s previous work leading business and technical projects in the public, financial, and life science sectors across Ireland, the UK, the US, the Netherlands, Belgium, and New Zealand.

Paul’s qualifications include a degree in European business and French from Ulster University and more recent certifications in finance (ACCA), procurement (CIPS), and business strategy (ESMT). He has technical certifications from Microsoft, Oracle, and IBM and holds a master’s in advanced software engineering from University College Dublin.

Paul is also the author of JBoss Drools Business Rules, by Packt Publishing, and has lectured in Excel and Visual Basic at third level.

I would like to thank the Drools, KIE, and Kogito communities, and the teams at Red Hat, IBM, and Microsoft for delivering great AI and business automation solutions, and for their ongoing support in writing this book.

About the reviewers

Karina Varela has worked with enterprise technologies for over 10 years, focusing on cloud solutions, Java technologies, and the open source culture.

Her solid knowledge is built on practical experience in development, architecture, delivery, and troubleshooting production applications and products. Having worked at Red Hat, the biggest open source company in the industry, not only does she bring a vast understanding of the open source model and the open communities but she is also largely involved in the delivery of architecture and the delivery of mission-critical software in multiple sectors around the globe. She has also been awarded the Red Hat champion recognition.

Karina is well recognized within the Java community for articles and blogs, books, talks at international tech conferences, open source contributions, and active nurturing and management of communities such as SouJava.

Filippe Spolti is a software engineer and an open source and cloud enthusiast. On a day-to-day basis, he works with Kogito Engine for Serverless Workflows. He worked on OpenShift V3 in its early stages, helping to bring middleware products to the cloud. He was also a technical support engineer who helped customers to solve issues and contributed to several open source projects, more recently with CNCF Serverless. He contributes to local conferences in Brazil as a speaker and track coordinator. He holds a bachelor’s degree in information systems and specializations in software engineering and information security and wrote the book WildFly: New Feature, and has helped other authors to be successful with their books.

Sharing knowledge is rewarding and it also requires dedication and a little time away from family and friends. I am thankful for the support they have given me in recent years.

Nishant Ravle is an IT professional with more than 16 years of IT experience in BPM, SOA, and middleware technologies. He has extensively worked on Oracle and IBM product stacks. Currently, he is in the field of software consultancy and corporate and online training.

Nishant’s technological expertise spans primary skills and expertise in business process management and the service-oriented architecture domain, developing BPM processes using IBM BPM, Oracle BPM, jBPM, Camunda BPM, and Activiti BPM in on-site assignments in the UK, Norway, Oman, Malaysia, South Africa, Dubai, Kenya, and Qatar for requirement gathering, development, consulting, architecting, and training.

Niall Browne has worked extensively with Excel and web technologies. He studied maths and psychology at Dublin City University and has a keen interest in coaching and sports analytics. Most recently, he was a member of the team at The Mill in Drogheda, which co-created a financial payments project with PFS.

Table of Contents

Preface

Part 1: The Problem with Excel, and Why Rule-Based AI Can Be the Solution

1

Wrestling with Excel? You Are Not Alone3

Why do you use Excel?3

Excel as the engine room of the business4

Day-to-day Excel problems4

One solution – separating information and processing instructions5

Enterprise solutions6

Examples of enterprise solutions7

The chasm between Excel and enterprise solutions8

What’s your real business problem?10

What these business problems look like in real life10

Artificial intelligence and which type can help you best11

What is artificial intelligence?12

Practical artificial intelligence and business rules16

The when … then format16

Triage – life or death business rules17

Business rules in your organization18

A business rule engine you already own19

More powerful business rule engines21

Splitting Excel into different pieces22

Other solutions are available23

Summary24

2

Choosing an AI and Business Rules Engine – Why Drools and KIE?25

Are you reading this book for personal or business reasons?26

Which business rule engine to use26

What’s in a name – KIE, Kogito, or Drools?27

Why choose KIE and Drools as our Rule Engine?28

Open source and the Red Hat and IBM business model29

How KIE being open source helps you29

Open can be more secure31

Asking for help in Drools (or in any open source project)31

The best way to ask for help in the open source world33

KIE, Kogito, and Drools resources35

How to design and build enterprise solutions37

Working with a team37

What software architects do38

Containers supporting Kogito, KIE, and Drools40

Four different ways to work with business rules42

Summary43

3

Your First Business Rule with the Online KIE Sandbox45

Writing our first business rule46

KIE Sandbox Extended Services52

Downloading and running KIE Extended Services53

Troubleshooting KIE Sandbox Extended Services55

Running our first rule56

What is happening behind the scenes – KIE Sandbox and Services56

More on Decision Models57

Decision Models aren’t workflows58

Differences between Decision Models and business rules58

A tour of the UI59

What is a variable?60

What is a type?60

How to fix these errors61

Giving our Decision Model a name62

Previous autosaved decision models63

Uploading Decision Models from other sources63

Option 1 – importing the sample64

Option 2 – step-by-step instructions65

Other UI elements in KIE Sandbox67

Note – KIE Sandbox is only a sandbox69

Summary69

Part 2: Writing Business Rules and Decision Models – with Real-Life Examples

4

More Decision Models, Business Rules, and Decision Tables73

Make your decision service easy to work with74

What shape is your data – tables or trees?74

Modeling our data in the KIE Sandbox76

Using our complex data type in a decision model79

Describing our data better using constraints80

What makes a good custom data type?82

A more powerful Decision node – Decision Tables83

Our first Decision table in the KIE Sandbox83

Decision Tables as your go-to solution (other nodes are available)87

More nodes that you can use87

Other types of Decision Nodes89

Running our function93

A more sophisticated Decision Table95

Rule matching and HIT Policies96

A safer Decision Table example97

Summary100

5

Sharing and Deploying Decision Models Using OpenShift and GitHub101

How deploying to the cloud makes things easier101

Reality check – why not connect Excel to the local KIE Sandbox?102

OpenShift – Red Hat’s piece of the cloud102

Signing up for the OpenShift Developer Sandbox103

Accessing your OpenShift Developer Sandbox105

Linking KIE Sandbox and your OpenShift instance106

Deploying your decision model to OpenShift109

Important notes on the OpenShift Developer Sandbox112

Taking care of your data113

Saving and sharing your decision models using GitHub114

Signing up for GitHub and getting your token114

A tour of your personal homepage on GitHub115

Using your token to collaborate on KIE Sandbox118

GitHub flows for saving and project collaboration120

Summary126

6

Calling Business Rules from Excel Using Power Query127

Prerequisites127

Five different ways to link rules, AI, decision models, and Excel128

VBA129

Script Lab and Office Scripts129

Microsoft Power Automate130

Power Query130

Machine-readable web pages using REST130

A simple REST example130

More on REST – GET and POST requests132

Swagger – a more human-friendly link to Kogito132

Exploring Product Recommendation Service135

Some vital information – service endpoint and contents136

Other desktop REST clients – HTTPie137

Calling the decision service using Power Query139

A simple Power Query REST example139

Using Power Query REST data in Excel142

Passing parameters into Power Query – things we need to know144

The Power Query M language and the advanced editor145

Referencing an Excel named range using M in Power Query145

Calling the decision service using parameters150

When to use Power Query154

Summary154

Part 3: Extending Excel, Decision Models, and Business Process Automation into a Complete Enterprise Solution

7

Using Business Rules in Excel with Visual Basic, Script Lab, or Office Scripts159

Calling decision services using Visual Basic for Applications159

Security checks in running Excel macros160

Sending JSON calls via VBA to our decision service161

Extending our example163

Fixing project references163

Next steps after VBA164

Meet Microsoft Script Lab – a modern version of VBA164

Tour of Script Lab167

Saving projects in Script Lab169

Importing scripts into Script Lab169

Hello World in Script Lab170

Calling our decision service from Script Lab172

What is Office Scripts?176

Getting familiar with the Office Scripts environment177

Calling our decision service from Office Scripts179

Lots of choices – but which one to link with?181

Summary182

8

Using AI and Decision Services Within Power Automate Workflows183

Prerequisites184

What is a workflow?185

Differences between business rules and workflows186

What is (and why) Power Automate?187

Getting started with Power Automate online187

Our first Power Automate flow189

A Power Automate flow to call our decision service195

Preparing our Excel output table196

Creating our flow to call the decision service197

Running our updated example199

Modeling our customer service flow in Power Automate200

Introduction to Microsoft Forms200

Updating our Power Automate flow201

Running our customer service Power Automate flow206

Suggestions to expand this example207

Other workflows – Kogito Business Automation and Power Automate Desktop208

Power Automate Desktop208

A quick look at Kogito business automation209

Summary210

9

Advanced Expressions, Decision Models, and Testing211

Prerequisites and pre-reading211

DMN and FEEL expressions – extending what you know212

Dynamic lists, contexts, and relations213

More on manipulating lists using FEEL expressions216

Lambda functions217

Ranges instead of sequential lists217

Relations – tables of information218

Dynamic contexts219

Graphical contexts220

More on building and editing decision models222

Common decision patterns224

Another pattern – linking decision models together225

Linking the Bill of Materials example229

Testing and breaking your rules (before somebody else does)230

Agile, Scrum, and test-driven development230

Run ... as Table231

Test cases using Excel232

Scenario testing and simulation233

Summary and further reading235

Part 4: Next Steps in AI, Machine Learning, and Rule Engines

10

Scaling Rules in Business Central with Docker and the Cloud239

Prerequisites240

Comparing business central and the KIE sandbox240

Containers, Docker, and getting help241

Installing Docker242

Preparing your Docker Hub account242

Downloading and installing the Docker software242

Running Docker and a tour of Docker Desktop243

First time use of Docker Desktop245

Running a simple Hello World image in Docker246

Running the Business Central rule editor in Docker247

Opening the Business Central rule editor249

Scenario Simulation and testing in Business Central252

Setting up our test scenarios253

Running our test scenarios257

The KIE Server in Docker and Azure258

Replacing the KIE Extended Services259

Running the KIE Server in Docker260

Running Business Central and the KIE Server – Docker Compose260

Can we deploy the KIE Server onto Azure or other cloud providers?263

Roundup of decision model deployment options265

Summary265

11

Rules-Based AI and Machine Learning AI – Combining the Best of Both267

Technical requirements268

Business rules as preparation for Machine Learning268

Graphical introduction to Machine Learning269

Looking at the sales data from our online chocolate shop269

Introduction to notebooks and Python274

Naïve Bayes and other classifiers276

Training models in Azure Machine Learning277

Setting up Azure Machine Learning278

Step-by-step training of the Machine Learning model282

Deploying the two AIs together (ML and rules)286

A decision service combining rules and ML288

Key points in importing ML models to decision models289

Running our integrated model292

Summary293

Further reading293

12

What Next? A Look inside Neural Networks, Enterprise Projects, Advanced Rules, and the Rule Engine295

Technical requirements296

Another machine learning method – decision trees296

Decision trees and decision tables299

Neural networks in machine learning300

A neural network implemented in Excel301

Training our neural network – can you do better?302

You already have tools to train neural networks303

Neural networks – more powerful but less explainable303

Ethics and explainability in decision making – how KIE helps304

Business rules are explainable by default304

Explainability in machine learning304

Executing combined rules and machine learning models305

Running the KIE machine learning samples using Java and VS Code306

What next from here – exploring other models and building your own309

Executing machine learning and rule models in Business Central310

Alternative – integrating rules and ML in a Power Automate flow310

Calling the machine learning model from Power Automate311

Red Piranha as a template enterprise project313

Running the Red Piranha Docker image313

Running your own files in Red Piranha317

A look inside the Red Piranha project using Codespaces and VS Code317

Advanced DRL rules in VS Code and Business Central319

Individual DRL rules versus decision tables320

Why are rule engines and decision models so fast?320

The RETE and PHREAK approach to running rules (fast)321

Summary323

What we’ve learned in this book323

Appendix A

Introduction to Visual Basic for Applications325

Introduction to macros and VBA325

Appendix B

Testing Using VSCode, Azure, and GitHub Codespaces329

Getting started with VSCode and Codespaces330

Setting up scenario testing in VSCode333

Running scenario testing in VSCode336

Alternative method – starting with Kogito scenario samples338

Running VSCode on your laptop339

Appendix C

Troubleshooting Docker341

Preparing your laptop for Docker341

Switching between Hyper-V and WSL342

Troubleshooting Docker – some obvious things343

Troubleshooting Hyper-V and WSL344

More help on Docker345

Index347

Other Books You May Enjoy358

Download a free PDF copy of this book

Thanks for purchasing this book!

Do you like to read on the go but are unable to carry your print books everywhere? Is your eBook purchase not compatible with the device of your choice?

Don’t worry, now with every Packt book you get a DRM-free PDF version of that book at no cost.

Read anywhere, any place, on any device. Search, copy, and paste code from your favorite technical books directly into your application.

The perks don’t stop there, you can get exclusive access to discounts, newsletters, and great free content in your inbox daily

Follow these simple steps to get the benefits:

Scan the QR code or visit the link below

https://packt.link/free-ebook/9781804619544

Submit your proof of purchaseThat’s it! We’ll send your free PDF and other benefits to your email directly

Part 1:The Problem with Excel, and Why Rule-Based AI Can Be the Solution

The first part of the book builds the case, showing how an AI and business rules approach can help solve the problems you are encountering in Excel.

This section includes the following chapters:

Chapter 1, Wrestling with Excel? You Are Not Alone, outlines the problems you may be facing in Excel, but reassures you that you are not the only person to encounter these issues and shows how an AI-based rule approach can help.Chapter 2, Choosing an AI and Business Rules Engine – Why KIE and Drools?, shows why the rules engine and AI tools from the KIE project are the best choice for you as an Excel user and welcomes you to an open community supported by IBM and Red Hat where you can get ongoing support.Chapter 3, Your First Business Rule with the Online KIE Sandbox, dives into writing business rules and creating your first decision model in the online KIE Sandbox using a classic Hello World example.

1

Wrestling with Excel? You Are Not Alone

If you read the Preface, you will know that a lot of people use Excel, being one of them, and that you are not the only person frustrated with the limitations of it as a tool.

Based on the fact that you have read on, you must now know that this book can help you. But before we can go into how exactly it can help, it’s worth exploring in detail the problems we’re trying to solve. In this chapter, we’ll cover the following:

Why do you use Excel?Enterprise solutions and the gap between them and ExcelWhat’s your real business problem?What is artificial intelligence and which type can help you best?Practical artificial intelligence and business rulesSplitting Excel into different pieces

Why do you use Excel?

If you ask most of the billion Excel users around the globe, most of them will say they use Excel for these two main reasons:

As a convenient place to store information – more structured than using post-it notes, but flexible enough to evolve over timeA convenient way to exchange information – because everybody else uses it

If you are an Excel power user, you'll add a third reason:

You use Excel to process important information. And your organization depends on the knowledge you have in your head to get the process right

In working with Excel, you and your colleagues are using it as the core engine to drive your business. Let’s look at a couple of real-world examples.

Excel as the engine room of the business

There are many important jobs in every business (I’m using business as shorthand for any organization). But those jobs will grind to a halt without a way of capturing, processing, and sharing information. On the surface, it looks like Excel does these tasks very well:

If you work for a medical company, you might use Excel to capture orders for vaccines, process them to forecast demand, and then use Excel to share orders with various plants worldwideIf you’re in the aircraft leasing industry, you might use Excel to track how many planes you have placed with airlines worldwide, process the information to calculate different risk scenarios (for example, a major war breaking out), and then use Excel to share information with the banks that are funding youIf you’re a small business, you could use Excel to track salary information, process it to calculate payroll taxes due, and carefully share the relevant information with each employee

The following diagram shows how you use Excel in each case:

Figure 1.1 – How you use Excel

This diagram is so simple that you’ve probably never needed to think about it – you take some information in (for example, from a spreadsheet), process it (apply hand-crafted formulas or review it manually), and then share it (maybe save it to an internal drive or SharePoint for others to use).

And that is the core of the problem – your processing is bundled up with how you gather and share your information. This workflow is great for getting started, but sooner or later, you’re going to run into problems as each of the steps in Figure 1.1 gets in the way of each other. We’ll look at how you might encounter this problem in your daily work, before beginning to talk about ways of solving it.

Day-to-day Excel problems

How you use Excel is unique to you. But I’m guessing that you’ve run into several of the following common problems:

A large part of your day involves repeating tasks in Excel by hand. This includes capturing information by manually copying details from one sheet to another.Emailing Excel files back and forth, not being sure whether you’re using the latest version, and losing information by editing the wrong version.Wondering whether your formulas may have been broken by other users and spending hours rechecking them.You use complex Excel formulas that no one else can really understand without spending hours drilling down into them. Formulas within formulas combined with more formulas. Sometimes, even those who write them can’t really remember how they work.

These pain points are particularly harsh in a regulated environment (for example, financial or medical/life sciences) where data integrity and auditing how decisions are made are required. But these pain points also exist in sectors such as manufacturing and management, where the consequences of decisions made on faulty spreadsheets can get very expensive very quickly.

The core part of most of these problems is that the file that contains the information is the same file that contains your functions for processing that information. What if you could separate them?

One solution – separating information and processing instructions

One way of solving this problem is to use two Excel files – one data source containing the information we want to work with and the other for processing the instructions or business logic.

Figure 1.2 gives an example:

Figure 1.2 – Separating information and processing instructions

This diagram shows a solution, used by many Excel power users, to separate the original information from the formulas that act on this information:

File 1 contains the information, and only information. You can share this file with other people and allow them to edit it.File 2 contains the processing instructions (formula), often a summary page or report that can be printed.The two files are linked using the standard Excel formula for linking spreadsheets, similar to the following: =[Workbook_name]Sheet_name!Cell_address

This solution is better – you can pass around the first file, knowing that your formulas can never get broken (either by accident or deliberately). The second file contains all your important formulas. It’s not perfect – updating the two files can be tricky, and it’s easy to break the link (for example, by saving the files in the wrong place).

It’s a start toward a solution. In fact, most enterprise computer systems separate the information and information processing in this way. They will often refer to the information you’re working on as data and the processing instructions (for example, Excel functions) as code.

If you’ve ever written your own functions or macros in Excel, using Visual Basic for Applications (VBA), you’ll have come across these terms before. You’ll see the terms data and code a lot in the coming chapters. We’ll also be talking about enterprise solutions as we move through the book – it’s worth reminding ourselves what they are.

Keeping it simple and secure

If this simple solution solves the problem for you, use it. More information on how to use a formula to reference a cell in another Excel file or spreadsheet is available on the Microsoft website.

In fact, if there is one thing we recommend you take away from this book, it’s don’t fully trust any spreadsheet that another person can edit (even if it’s password protected). If you aren’t convinced, I recommend you read the story of how one trader almost put a major international bank out of business by fraudulently manipulating Excel – https://www.amazon.com/Panic-Bank-John-Rusnak-Million/dp/0717135632.

Enterprise solutions

One core promise of this book is to build on your existing knowledge of Excel and help you find solutions that are more scalable, robust, and supported by a wider team. By this, we mean the following:

Scalable: Whatever we build can be used by tens or hundreds of users simultaneously, manipulating a very large dataset.Robust/durable: What we build can be left to run by itself, since it won’t easily break and highlights any errors using tests that can be run automatically.Supported by a wider team: Most Excel sheets are maintained by only one person. What if they leave? The solutions we build should be understood by many people.

Typically, these are called enterprise solutions. In your organization, you’re likely to have several of these, supported by an internal or external IT team. Let’s look at a couple of examples.

Examples of enterprise solutions

We gave an example before of a company using Excel to track salaries paid to employees and manage the taxes to be paid. But unless you’re an early-stage start-up, you’re likely to find another way of doing this – using a pre-built solution. If you Google the term Payroll Software, you will find hundreds of examples of it, and many will be tailored to the tax system of the country that you live in. In general, for a generic requirement such as this (since every company has to comply with the same local laws), you’re better off using an off-the-shelf solution.

This is great if you’re a business such as a fast-food franchise. Headquarters will tell you what enterprise systems to use and then charge you for using them. But most businesses do things slightly differently as they try to win new customers. Even if 80% of their requirements (HR, property management, and finance) are the same, these businesses are likely to have some things that they need to do differently.

One example might be a home loan lender who focuses on lending to workers in the gig economy with a fluctuating income. It may decide to build its own system to approve these loans since the decision-making process is different from banks that lend to people with a stable 9-5 job.

For these bespoke requirements, its IT department will often build the solution using a language such as Java, C#, or Python. They’ll build on top of standard libraries and tools such as Oracle, Microsoft SQL Server, and even cloud-based services (e.g., from Amazon). If it doesn’t have the skills in-house, it may contract an external company to build the system.

Simple then – we just get our IT department to rebuild our Excel sheets in an enterprise software solution. Obviously, it’s not quite as simple as that; otherwise, 1 billion people wouldn’t still be using Excel.

It’s probably worth underlining that we think Excel is a great tool and should be used alongside your set of enterprise solutions. Not only is it incredibly flexible and puts the power in the hands of business users but it can often also fill the gap as a good-enough solution while your IT team builds something longer-term. Either way, now that Excel is a first-class programming language, don’t be afraid to use it. Just know that this book gives you a roadmap to build from your current Excel sheets when you need something better.

What is a first-class language?

For more details on what this means, see the following link. In general, don’t let anybody intimidate you into thinking that Excel isn’t a proper solution – https://www.microsoft.com/en-us/research/podcast/advancing-excel-as-a-programming-language-with-andy-gordon-and-simon-peyton-jones/.

The chasm between Excel and enterprise solutions

So, why doesn’t your IT department just build a shiny new solution to solve all your Excel problems? It turns out there is a bit of a chasm between Excel and true enterprise solutions. We’ll cover three gaps that you may already have run into.

Gap 1 – the need to code

Coding or programming is being able to write instructions in a language that a computer can understand. It’s not difficult – you’re doing it already in Excel. The set of functions, numbers, graphs, and so on that you put in Excel are instructions to the computer to produce the result you want.

You’ve already done the hardest bit – breaking down a larger problem into smaller ones that the computer can then work through. But like Excel, even if coding is easy to do, it is harder to do well. It can take commercial developers (including your colleagues in the IT department) several years to fully master their craft.

Gap 2 – business experts and tech experts are now two different people

As an Excel power user, you’re likely to be a business domain expert. You might be the person who knows everything about the forecasting demand for umbrellas in the mid-west region of the United States. You could be the person who knows how to tweak expenditure on online and traditional advertising to make the most sales in India. Or you could be the person who knows how to forecast the demand for spare computer parts in Europe for the next five years.

This business knowledge is valuable as it is the one thing your business cannot do without.

Very few people have this business knowledge and are also able to code. It’s not impossible, it’s just that mastering two things (business knowledge and coding) is more difficult than one. Also, both disciplines lead to different career paths, so being skilled in both is tricky.

That’s OK. Two people can work together to build the solution. But having two people working on the solution leads to gap number 3.

Gap 3 – knowledge disappearing into code

Take a look at the following mortgage tool (a home loan calculator, available at https://www.drcalculator.com/mortgage/) shown in Figure 1.3:

Figure 1.3 – A web-based home loan calculator

It’s a pretty straightforward system – you put in the amount of money you want to borrow and what the current interest rate is, and it calculates how much you need to repay every month. Play around with it – especially the bit where paying off a small amount of money early can save you large amounts over the lifetime of the loan.

When they built this home loan website, you could imagine a business expert setting out the rules for the calculator, and a technical colleague building these rules in code. What if you want to see whether they have done the job correctly? Easy – you just look at the code, right? You’ll see that it’s not so simple when you look at the following code sample:

(window.webpackJsonp=window.webpackJsonp||[]).push([[19],{"3X0S":function(n,e,t) return d.fc(n),d.Wb().sliderButton(0,0)}),d.Kb(2,"ion- this.updateAll()}radioChange(n){"ion-radio-group"===n.target.localName&&(this.m.getCalcMode()!==+this.radioValue?(this. radioClicks=0,this.g.getShowCalcModeMessage() &&this.c.showCalcModeDialog(this,+this.radioValue),this.m.setCalcMode(+this.radioValue)) …

Don’t worry if you can’t understand this – that’s the point. Without extensive testing, it’s very difficult to check whether what is happening in the code is what you intended. It'd be even worse if you were returning to update this calculator after a year or two of working on other projects and had forgotten the work you did previously on the project.

But why don’t we build this in Excel?

As an Excel power user, when you saw the mortgage calculator website, you probably thought I could build this in Excel. And you probably could. But as you add more features, your Excel sheet will become so complicated that other people will have difficulty reading it, a bit like the code sample. This is where the solutions that this book proposes come into play.

What if there was a halfway house – allowing you to capture your business knowledge in a way that the computer could understand, but was still human-readable? This book proposes business rules as part of the solution. But to explain why (and how), let’s put Excel to one side and look at the business problems you’re trying to solve.

What’s your real business problem?

We talked earlier about Excel being the engine room of many businesses. Somebody (a client or an employer) pays you to use it for a reason. The knowledge you have embedded in your spreadsheets helps the business save money or make more sales. If you work with an organization that saves lives, the knowledge you have embedded in your spreadsheets could get people the right medicine at the right time.

The knowledge you have embedded is the important part. Spreadsheets are just convenient tools to capture and share that knowledge. Since most businesses have intense (and growing) competition, how well you capture and manage that knowledge dictates whether your business will survive and grow or shrink and close in the coming years.

Spreadsheets are convenient tools, but they are not perfect. In fact, if your organization relies heavily on Excel, it is likely that most people haven’t (yet) thought about how to capture and maintain the business knowledge that’s core to a company’s survival. The very same senior managers would be horrified if they didn’t have a list of buildings owned by the company. But there is complacency in listing and managing intellectual property, which is often several times more valuable.

What these business problems look like in real life

Without a systematic way to capture and manage your company’s business knowledge assets, you’re going to run into these kinds of day-to-day problems. Ask yourself the following questions and consider how many apply to you:

Do you find that you can never get enough good people to join your team? Either it’s difficult to recruit people with the right skills, it takes too long to train them with the knowledge that they need, or when they are trained, they move on to other jobs, taking their knowledge with them.Do you get complaints from customers that they get different answers depending on who in your team they ask? Even worse, have you found customers repeating the question to different team members until they get the answer that they want?Is there one key team member who you can’t afford to lose – the one person who knows how the system really works? If you answered no to this question, is it possible that you’ve overlooked a key team member and will only realize the gap when they leave?If you’re that key team member with the key knowledge in your head, it feels pretty good, right? Or it would feel good if you weren’t getting asked basic questions most of the day, getting in the way of you doing your real job…If your organization is project-based, do the project teams capture the knowledge coming out of each project so it can be used in their day-to-day work? Or does each effort tend to start with a blank sheet as the team finds it difficult to capture and reuse knowledge from previous projects?Do you have process and procedure documents that have been left sitting unused and not updated on a shelf? Is there a temptation to not use or update these documents as the team is too busy and you promise yourself you’ll get around to updating it sometime later?If you changed these process documents, would anybody notice? Your team members aren’t machines – they won’t just execute a new process if the document changes. But what if you could (gently) check that what is in the process was actually being implemented?Are edits tracked so that people can agree on what changes need to be made to the process? Just as important, if things go wrong, can you trace back to the change that broke it? When the fix is made, are you sure people will use the correct version of the process?Are the right people carrying out the right tasks? This used to be easier to manage when everybody worked in the same office. Now, with hybrid working, and teams being dispersed across different continents and different companies, do we know whether people are doing the right thing at the right time?In general, sharing information is much more powerful than keeping it a closely guarded secret. But sometimes, key information needs to be kept under wraps (for example, the percentage discount you give to key customers). The ability to edit this information (for example, changing the level at which this discount applies) needs to be more closely controlled. Is this the case in your company?

It’s no surprise that in a book with business rules in the title, we’re advocating for a solution involving rules to capture, share, and manage your business knowledge effectively. But to explain how we can do this, let’s introduce one of the hottest topics nowadays – Artificial Intelligence (AI).

Artificial intelligence and which type can help you best

On a bad day at the office, you've probably had the great idea of either cloning yourself or one of your key team members. That way, you could leave your clone working away while you do more important things, or just take a break at the beach.

Just to be clear, this book does not recommend cloning humans as a practical solution!

What is possible is applying recent advances in AI to the problem. Surely if we live in a world of almost self-driving cars, we could train a computer to capture and process the knowledge in our Excel sheets, in a way that can solve many of the problems that we’ve talked about?

Will AI take my job?

You may read this and think, if a computer can do my job, what am I going to do? With almost every bit of automation in the last 200 years, about 80% of your job can be automated, and it’s normally the boring and repetitive bits. If the boring bits are automated, it leaves room for the 20% that is really interesting to expand – hopefully leaving you with a more interesting job!

It turns out (no surprise, since AI is in the title of the book) that we can teach computers to help us in this way. But since there is so much buzz around AI, it’s worth being a bit more precise about the type of AI we’re proposing to use to solve the problem.

What is artificial intelligence?

There are many formal definitions of AI, but I particularly like the definition from John McCarthy, the Stanford professor that originally came up with the name – http://jmc.stanford.edu/artificial-intelligence/index.html. Broadly speaking, it is teaching computers to do tasks normally carried out by humans. Most people include the following as examples of AI:

A computer learning to identify tumors in X-raysA computer applying a set of knowledge to a problem to propose a solution (for example, a streaming site suggesting movies you’re most likely to want to watch)A computer understanding your search phrase well enough to suggest documents on a similar topicA computer evolving programs generation after generation to attempt to find the best match for the problems (genetic algorithms)

If you notice, these four examples are in very specific areas. While AI has made great advances in recent years, these are often across deep verticals, and AI often lacks general common sense. Humans will not be replaced anytime soon, even if some of the specific tasks we do will be.

Since AI is good at specific verticals, we need to decide which specific tools can help us solve our problem. We’ll focus on three main areas that you’ll likely have come across already.

More reading on AI

We’re focusing, in this section, on