Categories
Guides Help

A Single Source of Truth in Spreadsheets

60 percent of organizations say they are not very confident in their [data & analysis] insights

KPMG – Building trust in analytics

The single source of truth concept is an approach to data management where all of a business’ core data is centralised in one place. This could be as simple as a single spreadsheet which everyone refers back to or a little more complex using a database platform like Microsoft Access.

A single source of truth helps ensure:

  • Everyone trusts that they’re using the latest information. We’re not going to cause reputational damage to the company today.
  • There are fewer errors as corrections are made to the same data used by everyone.
  • We don’t end up with duplicates, missing or inconsistent data when we try to combine data from different sources; there’s just a single source.

Why?

70% … agree that [data & analysis] will expose them to reputational risk.

KPMG – Building trust in analytics

Imagine the situation where Karen, the relationship manager for Acme Enterprises, emails a spreadsheet containing an important list of customers to a number of people within the company.

Everything looks good so far as everyone is working from the same list of customers. But Terry in sales realises that the list doesn’t contain two new clients that he on-boarded last week. He updates the spreadsheet and emails it back to Karen who is, by now, out of the office.

The next day, Greg in sales updates the spreadsheet to make sure a particular customer doesn’t receive marketing emails anymore.

When Karen returns to the office, she notices that there are two emails from sales containing updates to the data. Unfortunately, Karen isn’t sure exactly what was added by Terry and what was changed by Greg. Karen has to figure out exactly which bits of data to keep before she updates her original copy and emails it back out to everyone. Karen is getting fed up already.

Clearly in this situation, Karen knows that she has to keep her master spreadsheet up to date with the latest information. She likely realises that the company would end up in a bad situation if they keep sending emails out to clients who don’t want them, and she’s probably heard about GDPR and knows the company could be fined up to 4% of its revenue if they don’t keep clients personal information up to date.

Karen is inadvertently using the single source of truth approach to data management here. But there are probably some improvements she could make to keep it all up-to-date without so much stress.

Shared Spreadsheets

Karen might think about putting a spreadsheet on a shared network drive, in a place where all of the relevant people could access and update the details. This is a step in the right direction as it means that any changes or corrections can be made by someone else and then everyone has the most up to date information.

But Karen still has some concerns:

  • Someone might accidentally delete the file or maybe ransomware might make the file inaccessible.
  • Someone could change the file in a way she’s not okay with.
  • She’s not totally comfortable with file permissions and isn’t sure exactly who will have access to the file. The sensitive information in the file could end up shared with too many people.
  • She isn’t sure that everyone in the company has access to the same network share.

So Karen decides to check with her IT department and they confirm that they backup the network drive pretty frequently and that everyone should be able to access it; so she asks them to set some permissions on her file so that only the right people can access it.

Another approach that Karen might choose to take is to share a document on Google Docs, Office Online or on SharePoint if her company has it. This solution might in-fact be the easiest and ensures that she can share the data with whoever she needs to. Office Online and Google Docs both have great support to allow people to edit the file at the same time. Some customers may be uncomfortable with Karen sharing their private data with Microsoft or Google and she may end up with some problems with the compliance team.

Google actively scans and analyses everything that’s uploaded. This, according to Google, is to “provide relevant product features, such as customized search results, tailored advertising, and spam and malware detection.”

The Truth About Google Drive and GDPR – Rinodrive

Karen now has a single source of truth where everyone who needs to can make the updates they need to without causing Karen too much bother; she just needs to check that she’s happy with the updates which have been made.

Karen has another problem though. She wants to create a report based on the private information held inside the shared spreadsheet, but it needs to be augment with some very sensitive information that she doesn’t want to share with anyone else. She decides to do this in another spreadsheet which no one else is to know about, but she still wants to use the single source of truth approach so that she doesn’t have to keep manually updating the source data.

Using the Data in Other Spreadsheets

So Karen sets up another spreadsheet and she looks at the ways she can import data from her existing spreadsheet. Given that she’s gone for the network file share and is using Excel, it should be relatively easy for her to reference the data from one workbook and use it in another.

However, there are still some problems:

  • If Karen corrects the data in her new workbook it won’t automatically propagate back to her single source of truth in the original workbook.
  • If someone adds a new row into the original workbook, it won’t necessarily come through if it’s outside the cell range she initially selected.

Still, it could be worse. If Karen chose to use Excel online or SharePoint, she would find that it’s currently not possible to reference other workbooks at all!

Alternatively, if Karen had used Google Docs to hold her documents, it would have been pretty easy to reference data from the shared spreadsheet. But she would still have the same problems when she corrects data in her new spreadsheet or if someone adds a record outside of the imported cell range.

If You Must Export Data and Email it Around…

If you find yourself in a situation where cloud storage with Microsoft and Google isn’t suitable for your business and you find yourself without a suitable network drive you may still find that email is the best way to share data. If you do this, there are some tips out there which can help you streamline the process:

“Include a “Read me” tab in your Google Spreadsheet with instructions for your volunteer AND instructions for YOU to help you retrace your steps if you have to refresh the data”


“Include “As of” date (when you pulled data from the Database) (reminder that the two MAY be out of date if the “as of” is a few weeks old)”

syncing ship – The Data Are Alright

Conclusion

It seems like a bit of an uphill struggle to implement the single source of truth model for data management in spreadsheets using the tools currently at our disposal.

If you’re working in a small office with a shared network drive where you can set permissions, it seems that the best solution is to store a shared spreadsheet with appropriate access restrictions.

If you have no shared network drive and data which is not particularly sensitive, then it seems that Google Docs is the best solution for sharing a single source of truth data-set with others.

But it seems like there is still a problem here. It’s possible to share the data from a single source of truth to another spreadsheet, but in doing so you have to accept that the data is read-only; by doing this your single source begins to fragment as people begin to make corrections to and share the read-only copy of data. It feels like there should be a way to synchronise data in both directions so that all of your spreadsheets are always up to date. Mwnci aims to be that tool.

Helpful Links

Leave a Reply

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