In the two months since I first came across Proto I’ve worked my way through most of the tutorials and sample applications and I’ve come away with a very positive impression of the product. I hadn’t however managed to the find the time to create a VBA based component; that is until last weekend. It was the choice of VBA as the product’s macro language that first attracted me to Proto – that and its close integration with Excel – and to celebrate this victory of practically over fashion I decided to build something real (i.e. might just be useful in the real world not just another map mashup demo – I’m just jealous of the fact that Google Maps for my part of the world consist of major roads and nothing else;-)).
So what to build for my Proto debut? Well looking at Proto one of the things that struck me was the lack of a relational join feature. In a previous post I explained that other mashup tools tend to use XML as their “pipe transport protocol” rather the table-oriented nature of EntrySets. But these entrysets cannot be manipulated using relational operators, not even the ability to join two tables by a shared key. Although the tool doesn’t offer this facility it does offer the flexibility to create a component to solve the problem and somebody has already done so; simulating an Excel VLOOKUP “join”. But I wanted the full power of a relational engine, INNER and OUTER joins, UNION and MINUS, aggregations via GROUP BY, access to very large datasets and I already had the answer; an Excel VBA module, xLite.
To say that xLite is a standard generic piece of code would be to exaggerate, it is like most operational VBA code, a template of ideas that are manipulated from project to project to serve the needs of the day. It had started life as an excel-to-Oracle tool (when it was called xORA), morphed into a SAP master data extraction tool (by the name of xSAP), finally getting a bit of structure when I discovered SQLite as the ultimate desktop database (having occasionally being seduced by that grand old dame, MS Access). Its further transformation into a Proto module required more renovation work; replacing Excel Range objects with variant arrays and replacing the excellent but COM based LiteX SQLite wrapper with the simpler but more flexible PSSQLITE.DLL thus allowing registry-free deployment.
By why SQLite, why not MS Access or SQLServer? See “What’s So Good about SSQLite” for why I use SQlite but in this case two reasons stand out;
SQlite doesn’t enforce column based types (i.e. VARCHARS,TEXT,INTEGER etc.) like Excel (more or less) it’s the content of the cell that determines its type – SQLite calls this manifest typing. This allows excel Ranges and Proto entrysets to be filtered through a relational join without type-conversion errors arising but at the same time allowing the database to recognise types where useful. For example;
| Month |
SalesThisMonth |
| Jan07 |
1000 |
| Feb07 |
2000 |
| Mar07 |
Accounts not available!!! |
| Total |
3000 |
Excel and SQLite will both give a Total of 3000 for SalesThisMonth column, most databases would choke on the accounts-not-available value.
SQLite does not need to be “installed” before it is used. Like MS Access the data resides in a single file but unlike MS Access even that single file can be dispensed with by using a “:memory:” database as I do within xLite.
The transformation from Excel to Proto VBA for both myself as the programmer and the code as the programmed proved to be trivial and rewarding. In this occasion I did most of the work and testing from the Excel IDE as I hadn’t yet fully comprehended the event firing mechanisms within Proto but I think in future I might develop and test under Proto and then port the code to Excel.
So any negatives?
With the development experience any negatives were generally to do with my inexperience with the platform (e.g. the above mentioned event firing causing me to “hang” the program once or twice) or features such as non-Excel “asset” management which are, I believe, on the product’s roadmap (e.g. I have to download my DLLs from the web rather than including them in the .PROTO manifest file as I would prefer).
My main two “problems” with Proto are not technical as such but market positioning misgivings:
- Treating Excel as the add-on;
Proto will not run without Excel being installed. Excel is the prime piece of data-manipulating real estate on users’ desktops. They trust it and depend on it. It would be much easier to “sell” Proto to end-users if Proto was marketed and packaged as an add-on to Excel (see for example Palo OLAP ).
Pricing software products is not easy (see this if you’ve never given the subject any consideration) and the last thing the good folks at Proto need is somebody on the other side of the ocean telling them they need to charge less per seat for their software. But I think they do if they intend to sell this software to individuals rather than to corporate IT purchasing departments. By individuals I mean the SMB owners/accountants/salesfolks, the large company departmental power-users, the freelance ‘data smiths’ like myself. Selling software into SMBs is not the same as selling to large companies (for some of the problems see http://supplychainventures.typepad.com/my_weblog/2006/04/tackling_the_sm.html) but equally selling something “new” into large company IT departments has in many cases become impossible unless that “new” product comes with a MS,Oracle,IBM or SAP badge on it.
But let’s not end on a negative, with its feet firmly on the ground where business computing currently operates (i.e Windows/MS Office, behind-the-firewall company intranets) but with an eye to the opening up of IT to the economics of the wider internet, Proto is one to watch.
UPDATE:
Byron Binkley CEO of Proto has pointed out in the comments below that the current dependency on Excel is temporary and will be removed in a future version. But he also reponds to my pricing concerns by offering ….
….a total of 50 licenses over the next 90 days (through July 13th 2007) at $79.50, a 90% discount to the listed price for Proto Individual (http://www.protosw.com/products/purchase)…
…this offer applies to anybody mentioning this blog post. This is pretty typical of my dealings to date with this young company; you raise a problem and they respond with a solution. Takes all the fun out of complaining.
I’ve 
