Dec
11 Spreadsheets and Databases
Custom Spreadsheets.
It's obvious to the database administrator when he (usually he - ask me sometime about one awesome female DBA I worked with) hears those words that this is going to be a mess.
Someone who doesn't know much about computers (or they wouldn't have done this on a spreadsheet) has poured hours and hours into a set of worksheets that work "just right." All the formulas are just so, the graphs are exactly what we want to see, and the tables can be sorted and filtered to a fare-thee-well.
Until it doesn't work.
Someone adds a row in the wrong place. Data is improperly typed in. The wrong format data is imported. Someone forgets to change the "Month" cell that a bunch of careful analysis is based on.
Suddenly, it's IT's problem. Or - if you are a small business owner - it's your problem, and even if you created the spreadsheet, it's not an easy solution.
Where's the backup? There used to be formulas in this column - what were they supposed to be? How was this supposed to be updated at the end of the month?
If North Creek is going to solve the "Spreadsheet Problem," we need to hear all of these stories. The gnarly, painful, exhausting work of untangling a spreadsheet that fit like my old pair of slippers, until suddenly it didn't. Let me know in the comments. Like "North Creek Software" on Facebook. Follow @NorthCreekSoft on twitter. Find me (David Block) on LinkedIn. Whatever - I want to hear your horror stories.
I also want to connect with you experts that know the tools to get us out of this mess. I will not have the answer to every problem - despite my optimistic marketing claims. I am going to solve one big piece of it.
How is that going to work?
You have to think in multiple dimensions - I'm going to start with 5(!) and we'll see how far we get.
1) Spreadsheet Data
Spreadsheets are tables, and we love putting data
in table form. All SQL databases work off of tables. CXL will extract
the data from the spreadsheet you upload, so that we can track changes
to it as the document evolves.
2) Spreadsheet Programming
The difference between a Word table and an
Excel data table is that in Excel, you can do things to the data! You
can use formulas (formulae if you are pedantic) to do calculations. You
can use VBA to do things even Excel can't do. You can then display the
results in informative graphs and charts. Everything in the document is
stored in the file (xml if you are using .xlsx), so it can all be
extracted and stored. CXL will isolate the programming parts of the
document as much as possible, so that we know what's editable in what
way. Some times (model generation) the data is static and the
calculations are volatile. Usually, the calculations change relatively
slowly, and the data underneath changes.
3) Cell formatting
Cells are little documents, and each has a format.
Many times, the formats will be consistent down a column, or across a
row. But the differences are meaningful. Spreadsheets are meant to have
a displayed component somewhere. That needs to be tracked. This is where
.csv export and import fail.
4) Rows
One of the simple pleasures of working with a spreadsheet is
the moment when you realize you've forgotten a row, and you just insert
it in the right place in the middle of things. As long as it doesn't
break that absolute reference you put in that other place... Rows are
often inserted or deleted, and tracking that change by individual cell
(a) is tedious, and (b) misses a piece of information. The user was
inserting or deleting a data record of some kind (usually), or adding a
calculation that wasn't there before. This could be noticed as a change.
5) Columns
Of course, anything you can do to rows, you can do to
columns. Again, it may be a whole new record, or more likely, a whole
new attribute across all the records, that you are adding or removing.
New calculations across the entire data set. Storing that change as a
bunch of cell additions turns that useful information into a bunch of
noise.
CXL is aiming to be a smart, spreadsheet-savvy document repository. Sharepoint and other document management systems can store versioned spreadsheets and keep the "latest" copy available. CXL will break your spreadsheet down across at least these five dimensions, tracking each change where it makes the most sense. The evolution of your document becomes a story CXL tells back to you. Of course there will be security and user roles and easy access. But CXL will see into your document, helping you develop it and protecting you from that change that necessitates that call to some expensive "expert" who will tell you to quit using spreadsheets and get a real application already.
I think we can make your spreadsheet into a part of your business that you don't have to worry about.
P.S. The Beta will start in January. We are looking for a few more businesses who have spreadsheet problems that need fixing. Let me know if you are one of them!