Ayende @ Rahien

It's a girl

RavenDB Customers Stories

I just got this from a customer, and I think that it is a great story:

Hi Oren,

I thought you might be interested in a little success story!

At the beginning of the year we began pre-financing a pepper factory in the [redacted]. In order to control the money that we were sending to the factory I created a little Excel spread sheet to keep a record of the payments that we were making. However, some payments were made directly from our head office to the factory, and some were made to us in [redacted] which we in turn transferred to the factory. So I added a few more columns to the spread sheet to record this.

However, the repayment calculations are different depending on whether we make the payment in [redacted] or whether it comes from head office. So I added a few more columns to the spread sheet to calculate this. When the money arrives in [redacted], we need to sell the USD at the Central Bank and convert them into [redacted]. So I added a few more columns to the spread sheet to record this.

However, some of the payments that we are transferring to the factory are from money that we borrow locally in [redacted]. So I added a few more columns to record this.  The spread sheet is getting quite big now – we’re at about 30 columns and there’s a lot of horizontal scrolling going on. However, we’ve only done about 10 operations, so there are only 10 rows. No big deal if we have to scroll around a bit.

Now the factory has started to receive the product which we paid for in advance. Good idea if I add a column to record this. Shit – every five minutes, someone comes to me and asks me to add another column for something or other.

OK – these are all distinct operations. When the factory starts to ship out the goods, the first page of the spread sheet is at 45 columns, so I now create a new page in the spread sheet to control the shipments as we receive them. Hmm – another 30 columns appear – of which about a third of them are just references to the first page, and the rest new data.

Now I realize that it would be nice to control our margin on the entire operation. The spread sheet is shared on SkyDrive so that our offices in [redacted] and [redacted], our accountants, our lawyers, the factory and the freight forwarder can see what is going on. However, I can’t allow all of these people to see information about our margins, so I create a new hidden page in the spread sheet.

Boy are we successful – we’ve now done 50 operations. Can’t see the column or row headers any more. No worries, just freeze the columns/headers. Wait a minute – who the hell copied and pasted the formulas to the new rows? The numbers aren’t adding up any more. Duh – why did you put the number for the currency contract in against contract 1234 – it should have gone against 1235??

Now, I don’t want to touch the spread sheet any more for fear of breaking something.

Business is great. We’ve finally nailed how to run pre financing operations for pepper factories in [redacted]. We decide to expand our business, and start working with another factory. Since the data in the spread sheet has to be visible to the factory, I create a new spread sheet. However, some of the requirements are slightly different, so it has different columns to the first one.

Our spread sheet is now 85 columns and 60 rows, and the other one is 78 columns and 7 rows.  Since the second spread sheet “looks” almost identical to the first spread sheet – yup – you guessed it - someone puts information into the wrong spread sheet!

Crisis point – we’ve got about [redacted, but in the millions US$] out in the field, and we can’t work out where the hell the money is – are the goods with the farmer, are they at the factory, are they at the port, are they already on board the vessel,  has the vessel already left? Have we paid for them, if not, when do we need to pay for them?

All the data is in the spread sheet, but only one person in the entire company really understands it  (I just wrote it – I don’t put the data in). So if I want to know something it’s a scream over the desk – “Jonathan where the **** are the goods we were supposed to ship last week?” Jonathan goes on holiday for three weeks.

[redacted] pepper production stops, while Jonathan is going up the Eiffel Tower in Paris. Even at this point – if you can believe this – I was waiting for him to come back so that we could continue operations.

I’ve had enough of this – RavenDB to the rescue! Two – let me repeat that – TWO - hours later, I’ve written the scripts to read the spread sheet(s) and throw them into one document – I’ll repeat that again – ONE document. 

The document is nicely partitioned into logical groups – i.e. classes for Payments, Currency contracts, Shipments etc. There is also a separate class that only does the calculations between the different logical groups. But all of this is stored in one document only. Since the calculations are all obviously read only, the results get persisted to the document.

Two indexes which give me all the information I need to control where the money is, where the stocks are. Three days to write the front end – it only took that long because I’m still learning MVC and javascript (I really wanted to use Backbone, but just couldn’t get my head around it).

In the first analysis of the new application I found [redacted, hundreds of thousands US$] that we had been paid 30 days ago, that we had not sent to the factory, and was lying at the bank!

Now this is where it gets pretty amazing (for me at least). Our data is nicely partitioned, four pages that are simply different views onto the same data. The guys are getting all excited – hey I want to control whether we paid the lawyer etc. Someone notices that some of the calculations are not correct – so much for my edge case tests!

So I add a couple of new properties to the document, create a three line foreach loop to read all the data in the database and save it straight back again, and voila – I’ve “patched” the database. Migrations, shmigrations  - who needs them! All the calculations are updated and stored back in the database, the new properties all appear. And this is all on live data. The minute I’d got the raw data from the spread sheet into the database we stopped using Excel.

And that made my day.

Tags:

Posted By: Ayende Rahien

Published at

Originally posted at

Comments

Rafal
11/08/2012 10:46 AM by
Rafal

I wonder what will they do if they add a second document to the database. Hopefully nobody will put data in the wrong document... But seriously, I'm not sure if replacing columns and rows with a single document isn't going to work against them soon.

joff
11/08/2012 12:13 PM by
joff

This so depressing...

Jason Meckley
11/08/2012 01:17 PM by
Jason Meckley

@Rafal, maybe, but success of this story is concept to deployment in 3-4 days. Finding 1000s of dollars without any effort and the ease of migrations.

The solution described was not suppose to be a cure all, just a way to better manage the data. And you figure the developer didn't know the first thing about MVC or RavenDB when he started. I say that is a huge win.

Rafal
11/08/2012 01:40 PM by
Rafal

Yeah, a spectacular amount of $$ makes everything else pale. And I envy the guy that despite his basic programming skills he was able to provide so much value for his company - this is so much different from many amazingly architected applications that didn't earn a single dollar for their creators.

João P. Bragança
11/08/2012 08:54 PM by
João P. Bragança

Yes but what about relational integrity(TM)? /sarcasm

@Rafal: they can always partition this later.

Jeremy Holt
11/08/2012 11:38 PM by
Jeremy Holt

I appreciate the comments above. It really was a stop gap measure - literally do or die. I'd already had some experience with RavenDb, but am still learning every day. In large part, thanks to the incredible support from the people on Google.Groups I was able to bash the app out in two days. When I originally wrote to Oren, it was as much to thank him for Raven as for anything else - the actual db part took about two hours - three weeks on I'm still messing around with the pages and javascript etc, but as I said the actual move from Excel to Raven, with all the indexes etc, data access was only about two hours. Now that it's become an "app" and is no longer a spreadsheet, I have the luxury of being able to improve it - the flexibility that Raven offers allows me to do that whenever I want. The biggest fear I had was modelling the data against live data - my starting point were the columns in the spread sheet. If this would have been SQL I would have no doubt locked myself into fixed data types, migrations etc - Raven let me change the types, add and delete properties on the fly etc. The thing is that the spreadsheet/app is responsible for the management of literally millions of dollars - even though we knew at the time when we started the business, that it would grow in time, we had no idea that it would grow so fast - the real issue lay with the fact that I foolishly and lazily decided to put all the data into a spread sheet in the first place. The app is very nearly finished now - it's anything but beautiful ( and I would be ashamed to show my code to anyone ), but it does its job (and pretty much saved us from bancruptcy) - which I guess is all I could really ask of it. Regards Jeremy

Mike Minutillo
11/09/2012 04:23 AM by
Mike Minutillo

This is a great story. Especially when you've been out there a while and you know just how many enterprises ARE held together with ridiculously massive and cobbled together Excel Spreadsheets and Access Databases. Now if only we had some kind of Raven LightSwitch...

Alexei K
11/09/2012 02:55 PM by
Alexei K

@Mike Minutillo, you do have Raven LightSwitch, it's called Embedded mode :)

Comments have been closed on this topic.