Business Transformation phase 2 write-up turns into ERP rambling

After a month or so of hard work, the very alpha of the front-end application for the sales team is released.  Although the functionality is very limited, I am a believer of the release early, release often motto.  It is vital not to hide in the corner, spend lots of time to work on your "brilliant idea", release it then realized it is quite disconnected from reality.

In our current utilization of the Access-based ERP system, we have stretched it to its limits.  For example, we are using it for quite a bit of analytical work such as average sold and purchase price calculations, customers sales history, cash flow reporting, product sales activity, etc.  With 15 years of sales history, this queries are a little bit too much for MS Access and its Simple JET-Engine architecture.

Some may then ask: What is the point of building the MS-Access ERP system and only to begin migration of it in 1/2 year?  Isn't it a complete waste of time and money?  Since a lot of business folks would like to compare software development with car manufacturing(1), I would use the car analogy too:  It is not a complete waste of money to buy a car for 1/2 year only to dump it for another car.  What is the ROI? Blah, blah, blah ... ...

This is a classic pitch from ERP software vendors to make you buy their ERP.  Why waste time developing your software when you can simply by a COTS products.  Yes, they love their TLA just to look smart.

No doubt some business should buy off-the shelve software to use it to solve well defined problems such as tax returns, restaurant POS/order entry/reservation management, but since a lot of business have this expectation, of it being this in-all, be-all system, well all information and all business process in to their ERP system, is your business a "off-the-shelve" business?

To be fair to the business folks, their expectation is probably inflated by the rogue vendors, salesman and all these marketing BS trying to prey on the unsuspecting folks.

Since I love the car analogy, let's go back to it with a software development-twist:

Buyer:  I need a car.
Salesman:  It is your lucky day, the new Prius just came in! It just won the "Best Resale Value" award from Kelley Blue Book's, it was named one of Forbes "Tougheset Cars on the Road", blah blah blah ... (and the list goes on)
Buyer: This must be a great car with all these recognitions
Salesman: Best of all it is a Hybrid, using Green Technologies and all.  You know, some the planet, save some trees. (What the hell is green technology?)
Buyer: Wow! I can really help the world with this car.
Salesman: It has great on mileage too!  That means this car will saving YOU money at the gas pump.
Buyer: Wow, I am really saving money buying this car.

No problem with the story? Here is where the analogy breaks down.  We never know what the buyer wants to do with the car!

In the software world, many buyers probably never driven a car before, have only seen piece of the car (think just the dashboard, the steering wheel, engine) but never seen the car as a whole.  They have read some fancy, vague, sales pitches in article in the latest magazine of how great a car is, for example:

•  "A car can take you from point A to point B very fast!" - buyer thinks he could use it to go from America to China really fast.
• "Save you money going to and from work!" - buyer walks 5 minutes to work, but since he doesn't really know what a car is, he really think it will save him money.
• "Revolutionize the way you travel" - now he thinks he can go to all these exotic countries he saw on the Travel Channel.

But you might think, the car buyer is being really stupid. How can he not know about what a car is. This comparison is complete bogus and unsound.  If that is what you are thinking, here is a question for you: What is an ERP system?

This post started off with the intention to write about our effort in business transformation.  It went complete off tangent into a rambling.  Oh well, I probably have ADD.  That's the beauty about blogs right?

(1) Disclaimer: I have not read the book.  I have only derived some basic understanding of the book from a blog post by Jeff Atwood

Using Groovy script for ETL, and more about the ERP

After 7 months of hard work, we have successfully migrated off a primitive console base system. The console base system consist of just 2 tables:

• Order + Line Items (this is one table!)
• Products (It was called Inventory, but really it just keep track of the product code, description and the Stock on Hand)

As a result of the primitive construct, little could be derived from the data. In phase one, we were able to make use of the data in the old system and merge it with 3 years of purchasing record stored in some excel documents. The ETL exercise itself was quite laborious. Merging 15 years of sales record and 3 years of purchase record was no small feat! Groovy scripts proved to be very handy for this ETL exercise, and Groovy's Java heritage, there was shortage of connectors to different data systems. For we have two sources: FoxPro DBF file and Excel XLS file, and the target system is a MS Access accdb file.  Here is an example to demonstrate how concise the connection code is, which free us to concentrate on the transformation logic:

def src= Sql.newInstance("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=<path to xls file>;DriverID=22;READONLY=true", "", "", "sun.jdbc.odbc.JdbcOdbcDriver");

def dest = Sql.newInstance("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=<path to accdb destination file>;pwd=<accdb file password>", "", "", "sun.jdbc.odbc.JdbcOdbcDriver");

src.eachRow("SELECT [PRODUCT ID], [PRODUCT CODE], ... WHERE ...", {
  // function body to process each row obtain from your source using the SQL statement
  // it.<field name> to get the value: for example:
  def productId = it."[Product ID]"

  // ... more code to get stuff from source

  // ... probably logic to run different insert statements base on different value from the source and business logic

  // an example to insert data into your target
  dest.execute("INSERT INTO [Products] ([Product ID], [Product Code], ...) VALUE (?,?, ...)", [productId, productCode, ...])
});

The new system went live after 3 weeks of hard work and we ran the old and new systems in parallel for 1 quarter and a bit. As of August, I am happy we are comfortable enough with the new system and since retired the old FoxPro system. During the last 1 quarter and a bit, development of the new system has continue to evolved. The functionality of the system and integration with the company is depicted in the following diagram:

There are two main problems with the above ecosystem:

1. Salesman, Inventory Controller, Shipper and Couriers all rely on the clerk to access the system.  
2. The analysis and data mining activity by the managers are stretching the limit of Access.

In the next phase of the evolution we have plan on addressing the above issues and add on more features. In order to prepare for the next phase, I have begun migrating the data in Access to SQL Server Express. This proved to be quite a challenging task, migrating a live system while trying to minimize the interruption to the business. I am in the middle of the process and this whole migration could be a post of its own.