What’s wrong with the Spreadsheet PIM? Part 1

Compliance

The Spreadsheet is the software equivalent of a Swiss Army Knife for many businesses, it’s a Business Intelligence (BI) tool, a Customer Relationship Management (CRM) system, an Accounting system, a Warehouse Management System (WMS), but above all, it’s the world’s most widely installed Product Information Management System, commonly referred to as a PIM.

It’s not hard to see why the spreadsheet is used to mimic these different systems, especially for smaller or start-up enterprises. The cost of acquisition is very low and a simple column/row/cell structure provides an easy way to store data, perform calculations and produce reports. However, there comes a time in any business when levels of sophistication become too much for the trusty spreadsheet to handle, resulting in business inefficiencies and data quality issues.

While we’re all comfortable with the concepts of spreadsheets, another key consideration must be security. When was the last time all of the mission critical spreadsheets in your organization were backed-up? Do those spreadsheets go on ‘walk-about’ at the end of each day – heading home on a laptop that sits on the back seat of the car? What about the kitchen counter with the bowl of cereal, jug of orange juice and the toddler running around underfoot? Think about the amount of money that may have been invested in network security and how spreadsheet security compares to that initiative.

Increased levels of sophistication and security concerns may prompt a business to invest in commercial software that has been designed specifically for a business process. Specifically in the area of product information management, high levels of complexity around product data can be reached very quickly, therefore becoming the catalyst to take such action.

Product data is in multiple spreadsheets, everywhere

No matter how hard one may try, getting all your product data into one spreadsheet, with every product category, attribute, packaging configuration and language variant is no simple task. Even a small product catalogue can quickly become unwieldy.

In many businesses, it’s not uncommon to find multiple spreadsheets containing different product item attributes that only when aggregated describe a complete product. Product data is usually managed by department or business operation, for example:

  • Marketing
  • R&D
  • Production
  • Logistics
  • Regulatory
  • Finance

It’s aggregating this product data that becomes an issue. How do you ensure you are merging the correct attributes with the correct item?  Using keys like SKU (Stock Keeping Unit) or GTIN (Global Trade Item Number) are common approaches, however I have seen cases where item data is merged based upon product description. The more sophisticated your business gets, the more fragmented your product data becomes.

Spreadsheets do not contain business rules – they’re not intelligent

Certain properties can be set within a spreadsheet that can be applied to rows, columns and cells. Some of which can enforce rules to determine what is entered into a cell, however, without diving into scripting languages and macros, that’s about the extent to which data validation takes place inside a spreadsheet. In order to apply further validation checks on data contained within spreadsheets, human intervention is required. But not just any human being, a person that possess the knowledge, skills and qualifications to know what data is valid and invalid according to the data quality requirements of the product data.

It’s the data quality requirements that have made product information management a sophisticated process.  These requirements are defined by: Standards Bodies, Regulators, Legislative Organizations, Trade Associations and Trading Partners. For example, in the Retail Food & Beverage sector data quality requirements include:

  • GS1 Standards for Automatic Identification and Data Capture (AIDC)
  • GS1 Standards for the Global Data Synchronization Network (GDSN)
  • EU Regulation 1169/2011 for the provision of Food Information to Consumers in Europe (FIR/FIC)
  • FDA Food Safety Modernization Act (FSMA)

Carrying out manual data quality validation is fraught with problems.  It’s time-consuming, complicated, error-prone (because we’re only human) and just plain inefficient.  What’s more, if you have these ‘super humans’ retained as employees, they must be trained and pursue programs of Continuous Professional Development (CPD).  Of course, you can always hire a Consultant, but that approach could be even more costly and leave your organization vulnerable.

Spreadsheets do not have an intuitive interface

While a spreadsheet’s simplistic look has been an attraction to many, it’s also a major drawback when attempting to turn data into information. The ability to use color fills and outlines, font styles and sizes, functions and formulas are no match for an application that has been purpose built.

Many people will inadvertently confuse the terms Product Data Management (PDM) and Product Information Management. A spreadsheet can be used to manage product data to a certain extent, depending of course on levels of sophistication, however ‘data’ and ‘information’ are somewhat different. Data only becomes information when it is presented in context and associated with additional data elements. Application logic is responsible for turning product data into product information.

Without application logic a spreadsheet user has no start point. It’s usually the spreadsheet creator that knows their way around it, but give it to someone else and they’ll just be staring blankly at the screen. This means that every time someone different uses that spreadsheet you have to explain where to start, where to enter data, where not to enter data, where the hidden formulas reside, etc. Further more, when you share your spreadsheet with somebody else, they always have a suggestion on how it can be improved! Then you get more hidden fields, more formulas and different versions of the same spreadsheet.

A lack of application logic and business rules also means that data errors will creep in. Humans, no matter how meticulous they may think they are, will hand-key data incorrectly. Even when using cut and paste functionality, it’s possible to place data in the wrong cell, row or column. We have conducted our own studies on manual data entry and can report a consistent 3-5% error rate. Indeed many studies show the truth, that bad data is actually very expensive.  According to a study conducted by Eloqua (An Oracle acquisition) – A bad record that originally cost $1.00 ends up costing $11.50 after it has made it through the marketing and sales cycle! A $10,000 investment just became a $36,250 investment.”

In Part 2 of “What’s wrong with the spreadsheet PIM?” I’ll discuss why spreadsheets don’t provide the mechanisms to control your data and offer an alternative to the spreadsheet PIM. Stay tuned for more!

Ian Piddock

Author: Ian Piddock

Ian Piddock's career has been focused in the IT industry. Since 2000 he has worked in a variety of business development and marketing roles for global enterprise software companies. Ian has been a GS1 standards advocate and practitioner for over 10 years, he is a data quality evangelist and experienced in the practical application of the GS1 Data Quality Framework. In recent years Ian has been responsible for the design, branding and product management of LANSA’s Data Quality software tools – DQ Inspector and DQ Reporter.At LANSA Ian heads up Marketing for EMEA and manages the relationship with GS1 Member Organisations worldwide, seeking to identify where LANSA solutions can accelerate industry adoption of GS1 standards and use of the Global Data Synchronisation Network (GDSN). Ian is also a trusted advisor on the impact that regulation and legislation has on the product data management process and has worked on projects involving the EU Food Regulation and the FDA’s Unique Device Identification (UDI) rule.

Leave a Reply

Your email address will not be published. Required fields are marked *