Now this is interesting. I remember searching a few years back for spreadsheet design patterns. That combination of keywords never led me to the Best Practice Modeling Standards upon which BPM – a company founded in 2002 by ex-Salomon Smith Barney investment bankers – provides its modeling products and services. The base version is apparently maintained by an independent Spreadsheet Standards Review Board:
Spreadsheet Standards Review Board Charter
- To promote the Best Practice Spreadsheet Modeling Standards to ensure that the Best Practice Spreadsheet Modeling Standards are recognized, used and accepted as the highest professional spreadsheet modeling standards in the world;
- To develop and maintain the Best Practice Spreadsheet Modeling Standards;
- To bring together global Spreadsheet modeling skills in order to develop and maintain the Best Practice Spreadsheet Modeling Standards;
- To facilitate and manage participation of interested parties and the general public in developing the Best Practice Spreadsheet Modeling Standards; and
- To evaluate proposals to add, delete or modify the Best Practice Spreadsheet Modeling Standards.
You might find the two following posts interesting if you’re dealing with vast amounts of data:
Thanks to Dick Kusleika‘s article on EuSpRIG 2007 and after browsing the latter site a bit, I located a couple interesting papers I want to share here. These are full of good high-level tips related to spreadshet design:
If you’ve ever had to extract data from a system that applies layers of senseless formatting, this should have you rolling on the floor laughing in no time.
I’ve wanted to elaborate on this post by Andrew Wiles for a while but it appears this isn’t going to happen anytime soon so I’ll simple refer you to it. Andrew analyzes some of the reasons why financial reporting (and MIS reporting in general) should be handled separately from mainstream IT, and goes on to discuss how responsibilities can be assigned accordingly. Chances are, if you’re reading this, you can relate to the situation described in his article:
- Financial reporting is reactive and requires dedicated, in-house, resource who can respond without delay.
- IT departments are project driven with formal procedures to allocating resource during project conception and manage change after implementation.
Following up on a somewhat theoretical introductory post related to desktop data management, Here are 7 habits which should prove useful when approaching any desktop data-related task. These will be refined based on the discussion you and I will have here, so let me know what you think.
I was actually shooting for 10 commandments, but 7 habits sounded good, too. Seven should be enough to start the discussion anyway, and leave room for expansion without making the number ridiculously large should we end up adding a few.
Now remember. This is supposed to apply to desktop work so let’s not wander off too far into foreign keys, surrogate keys or normal forms territory;-)
Here we go:
- Any data processing task takes you through the steps of organizing data, processing it and presenting it. Acknowledge these steps in your workflow and organize around them.
- Do the hard work first. Looking up and presenting information is both easier to perform and more valuable when it is base on a well-organized data layer.
- Organize your data as flat files instead of free-form tables. The flexibility of spreadsheets makes them both a blessing and a curse, often leading you to design the processing and presentation layers first and the data organization layer last. Organizing your data as flat files may require some more work upstream but will pay off handsomely downstream. See #2.
- Strive for one source of truth. An important piece of information should have to be changed in one place only and cascade from there.
- As much as reasonably possible, always work with a full dataset that includes domains contiguous to the one you’re currently working on. You’ll be much better off keeping too much information and identifying records appropriately than discarding what you still think you won’t need – but will.
- Don’t design for exceptions. You want a few standard tools that abide by the principles above while allowing you to handle exceptions, not multiple unmanageable tools that were each designed to handle a specific exception.
- Use identifiers. Referred to as Unique IDs or keys in database jargon, the important idea to remember is that you want a way to lookup a particular item of data at any point in time and distinguish it unequivocally from its neighbors.
Each of these items deserves several posts and I will be following up on each – hopefully with a mix of opinions, examples, tool-agnostic tips, and tool-specific tricks.
The complexity of modern organizations requires their employees to manage an ever-increasing flow of information. Many trends combine to that effect, among which fast growth, under-staffing, inappropriate tools or poor procedures. The purpose of this blog is to create a discussion around all the ways that managing information can be improved from a desktop worker’s standpoint – if we only take the time to sit back and think rather than jumping right in armed with our usual tools and mindset.
I’ll use the term desktop data management to refer to these practices.
Few online resources seem devoted to data management from a desktop worker’s standpoint, with most searches on the subject quickly ending up in data modeling or “information technology” territory. Nonetheless, even when data management isn’t your primary job, it is usually in your own self-interest to approach it with care. You may, after all, be subject to external forces that have made you the (however unwilling) owner of some piece of data critical to your company. If you think you’re too busy to consider something as esoteric as data management, a lot of your unproductive time in the office (no offense) will probably involve looking up information, matching lists from different sources or doing some other kind of repetitive, error-prone work. Chances are that better data management could spare you time and frustration.
Robust data management relies on down-to-earth principles, not on a particular piece of technology. I am convinced that the broad application of a few simple ideas can have a significant impact on an organization’s productivity, facilitating the exchange of critical information among coworkers.
Desktop data management problems are real-world problems, not theoretical entertainment for “computer people”. They address data as a valuable resource. Relegating data management to a low priority has a hugely detrimental impact on quality and efficiency. It results in anything from lower service levels to wasted energy, human conflicts or a mix of all three – and more. On the other hand, treating data management with the care it deserves produces a snowball effect, revealing previously unthinkable opportunities at a fraction of the cost you would have imagined. This is as true for a large online retailer processing thousands of orders daily, as it is true for you as an individual, sending a mailing to a few dozen customers or producing monthly reports for your company’s headquarters.