Beyond Spreadsheets: The Key to Unlocking Digital Transformation

I recently posted some thoughts on Digital Transformation on LinkedIn and wanted to explore my thoughts more thoroughly.

https://www.linkedin.com/embed/feed/update/urn:li:share:7254222427814006784
https://www.linkedin.com/embed/feed/update/urn:li:share:7254219659883737088

For much of the industrial world, Digital Transformation is some poor person sitting in a control room at the end of a shift manually updating a spreadsheet and emailing it to everyone. Most Digital Transformation efforts fall short of expectations. My experience over the past 30 years of working in industrial environments offers some insights into this.

I started my career in power generation. We had dedicated engineers calculating heat rate and working with operations to tune the boilers. This was a manual and time consuming process, but the millions of dollars in fuel costs warranted the expenditure of manpower. I’m dating myself here, but Lotus 123 spreadsheets were the go to tool for these engineers.

I installed the first PI Data Archive on one unit at the plant in 1996 and we started using DataLink to pull data from PI into these spreadsheets. This gave the engineers unprecedented leverage making them more effective while spending less time on the task. They were now able to spend the time freed up analyzing other problems. Other engineers saw their success and started working with the PI data as well. It became critical for tuning, production reports, and environmental compliance.

This scenario played out across many plants in many industries in the late nineties and early two thousands. Collecting timeseries data from control systems, storing it in a historian, and analyzing data in spreadsheets became commonplace. Life was good, but as we became more ambitious in the scope of our work, we began to run into limitations. We’ll talk about those in a moment, but let’s review the benefits and drawbacks so far.

Benefits or drivers for the success of this approach:

• Familiarity – Most engineers learn Excel in school. It is familiar.
• IT Support – Excel is a standard business tool. It is easy to get Excel training.
• Low-Friction – Hit File->New and go to town.
• No or Low Coordination Required – There is no need for governance. Your spreadsheet is your own

Manageability challenges:

• Sharing and managing copies of spreadsheets can be a problem
• Excel Hell – Most spreadsheets contain errors that are difficult to find
• Manageability – The best spreadsheets are not unmanageable, nobody wants to inherit someone else’s spreadsheet, even when it is well designed. Everyone has their preferences and style.
• Scalability – The larger the spreadsheet the less manageable and slower it becomes.
• Redundancy – Often the work used to set up and organize tags to build the logical structures required must be copied into other spreadsheets where it is needed.
• Rework – Inherited spreadsheets are often recreated from scratch by the next user. Companies pay to solve the same problem all over again.

Spreadsheets will always have their place. They’re a great tool for analyzing things at a smaller scale, exploring data, and working on prototypes. The benefits outlined above are powerful drives incentivizing engineers to continue to use spreadsheets.

We quickly reach the limits of spreadsheets as we try to scale out our analyses, automate calculations and reporting, alert for problems, and integrate with other systems in our enterprises.

As the “PI Guy”, I often have engineers approach me with a spreadsheet and say “I built this spreadsheet to analyze x. This does it for one of them, now I need to figure out how to do the same analysis on all 100 of them in our fleet.”

Another common occurrence is to be called into a meeting to talk about integrating PI with another system. This could be for something like pulling in lab samples to PI, triggering work orders based on machine usage time, or sending data from PI into another reporting system. In all of these cases we need to map individual PI tags to some entity. This requires us to compile some sort of spreadsheet or database to cross reference information if we do not have an established way of doing it.

We call this the need to map tags to logical entities, group them, and relate them to entities in other systems contextualization. You quickly learn that unless you develop a systematic way or adopt a tool for doing this that you end up reinventing the wheel over and over again. It becomes a tax on every more advanced project you undertake. We need a way to do this systematically and at scale.

Fortunately for us, there are good tools for doing this. My next post will explore what I think are the key needs for our “Contextualization Layer” and some of the pitfalls I have seen in implementing it. This layer is the foundation for true digital transformation and I think most enterprises lack a complete understanding of their needs leading to less than optimal solutions.