Who doesn’t? It is easy to use; most everyone has a copy on their computer; most everyone tracks work stuff (and sometimes personal stuff). I am guilty as charged when it comes to tracking everything… finance… kids school progress… even our scout outings and achievements… When it comes to work, I track data as well as logic in my many spreadsheets. I sometimes feel like I am a little obsessive, but I must admit, it is convenient.
The ‘problem’ with ease of use is that it encourages a proliferation of these corporate spreadsheets. I met an insurance company once that was dealing daily with tens of thousands of spreadsheets!
The question becomes:
- which one should I be looking at / editing?
- has it been approved?
- how to validate the content?
I am not saying that you should not use spreadsheets, since obviously I am a huge producer and consumer. My point is that we need to become smarter at managing these spreadsheets in the greater context of the business.
If the spreadsheet collects data, it is likely that the data was produced automatically out of another system. Granted, there are times when we need to look for the information and compile it by hand, but, if we are talking about thousands or more of records, I do hope that you have systems automating this process.
I’d like to focus more on the spreadsheets that you use to collect and document your business decisions. Phrased that way, does it make sense? Would it be more relevant to talk about your business requirements? I have seen a majority of companies use Excel for documenting their business requirements, especially when looking at business rules (because they are independent pieces of logic that don’t really fit any other document type). That is exactly why the DMN standard (Decision Modeling and Notation) is based on tables.
There are two main use cases I believe. Those that use a spreadsheet to document the requirements, and continue managing the evolution of their business rules in a business rules management system. And those that keep tracking changes in the spreadsheet going forward, providing this spreadsheet to the rules writer (who may or may not be the same person by the way).
Spreadsheets that document initial business requirements
The typical example of that would be a DMN decision model that you created by hand. Not that you need to use a standard to use a spreadsheet for rules requirements. Any form of spreadsheet would do. I have actually seen business rules architects use spreadsheets for rules harvesting since I started in this industry, decades ago.
Back then, spreadsheets were used to collaborate with business users. They could read and edit the text of the business rule requirements, and iterate until everybody agreed. Then the rules writer would translate the English version of the rules into the proper rules syntax. In the more modern days, more automated import capabilities have been introduced, but I still see a fair amount of projects that are coded by hand based on spreadsheet specifications.
The key in this process is that the business rules play a major role during rules harvesting, but it does not survive the implementation. The spreadsheet would typically be thrown away after the business rules have been implemented. From that point on, the subject matter experts would have access to the business rules and would be able to tweak them directly, without a need for the waterfall approach.
I am absolutely not condemning this approach. This is a great way to align the constituents before the actual implementation. If you are about to engage in such activities for your project, I would encourage you to look more specifically at the DMN standard as it embeds methodology in its format. Instead of starting with an empty spreadsheet, it guides you in the selection of the columns that make sense, while focusing on your end-goal (the decision). Our webinar shows you how our own DMN tool works (it is called Pencil), but you can also follow the same guidelines by hand on your own white board, or spreadsheet!
Spreadsheets that continue capturing changes in business rules
The other use case we see a lot refers to spreadsheets that tend to be much bigger and that capture simple or complex assignments. Think about rating tables. In the insurance industry, these pricing schemes can be very complicated. Complexity is measured here in the number of segmentations, and parameters that you need to look at. For example, geography would be a parameter, which you could segment at a very fine grain level, for each zipcode (there are 42,000 zipcodes in the US!!). The pricing would also depend on many factors like the number of accidents and violations, on the age of the driver, on the years of experience, on the classification of the car, etc.
While spreadsheets are convenient to capture, sometimes auto-calculate, they become significantly less easy to validate. With a quick eye check, or a quick calculation, you can verify that the rates are growing as expected, but actually validating with data is not that easy.
The main difference with the previous use case is that you may not want to throw away your spreadsheet. When a pricing revision happens, you want to be able to make simple edits in the overall spreadsheet, and get them approved independently of the implementation.
Some tools might give you the ability to import and export tables. I want to bring your attention to two potential issues that you need to consider ahead of your project implementation.
First, if the export of the table does not match the format of the original spreadsheet, the business users will not be able to easily connect the dots. That’s one thing to check early on.
The second important criteria is runtime performance. While importing a spreadsheet might be feasible, how large is yours? We have seen spreadsheets broken down into tens of thousands of rows, and even hundreds of thousands of rows! While they could turn into business rules, it is not a done deal that the implementation will be able to execute in a reasonable amount of time.
While I am big fan of business rules (of course), I recommend that you keep these spreadsheet as data that can be referred to by the rules. Keeping the large table in the database is an option with a big runtime cost (avoid making external calls if you do not have to). What I would personally do is to use lookup models. They are in-memory tables (as in ‘data’) that the rules can refer to. Because they are in memory, they are cached and indexed for performance. The beauty of keeping your spreadsheets as data is that data can be refreshed without a translation phase as we saw before. The business translation of what the spreadsheet means is defined once and for all in the interface of this lookup model. This is where you will define which column is input, which is output, and what they mean if it is not a simple test or assignment.
I don’t have an online video to show you yet, but we will have an insider webinar very soon. If you sign up for an Evaluation, you will be invited, and / or will be able to watch the recording at your convenience.
Rex Keith, from Equifax, and I also presented at BBC last year. If you have access to the proceedings, there will be some information there too.
Learn more about Decision Management and Sparkling Logic’s SMARTS™ Data-Powered Decision Manager