Maybe it's just messages
It's always so hard to know when you are refining your architectural skills or just spinning your wheels unable to see the next horizon clearly. Today, I had one of the moments where for an instant, a bunch of things clicked in the convergence of my work and constant reading about software design. Domain models, AOP, SOA, O/R mapping, mvc have all been floating around the office like a haze. Wafts of haze sometimes thickening into something cohesive, sometimes dissipating into nothingness. But today I was working on something much more mundane, Excel 2003's updated XML features.
The reason it probably makes so much sense for Microsoft to pursue SOA is that it fits so well with the vision of MS-Office. Office was all about making things for a single user or small groups of users, a document, a spreadsheet, a presentation. They never really succeeded at making the jump of having Office tools drive the web. They made attempts at getting data into Office applications, but let's face it. Beyond the small group level supported by Access, it really doesn't work that well to have end users going directly at enterprise databases. The client server vision of Office coming out of the 90's just didn't work. But, users really can do great stuff with these tools. Books, presentations, charts, you name it, without any (almost) support from programmers, workers are enabled. Enabled to find paltry ways to get timely information in front of them, all too often by typing in themselves by hand, and turn it into the stuff of daily business. This is where SOA should come in, but after working on it today, it stared at me right boldly in the face: the web service approach is almost completely wrong for Office. A REST approach based on URLs, query strings and the “web query” feature of Office would be a much better fit. And it's so dang close!
Excel has this new feature where you can bring in XML files, explicit or by example, refer to a schema for the document and then map this document to a “List”, a sort of named range on steroids. Like earlier versions of office, you can retrieve this document from the filesystem, a network drive or a web page. Buuttt, unlike the earlier web query feature that supported post or get parameters, derived by typing or from cells in the page, the XML feature must use a fixed location. So, while the web query is still there, it can't be used with the richer data format of XML. The list has some great features, and the XML is preserved and even exportable in original form like the XML list can. Translation, you can use Excel to edit XML documents and export them. If you just had the web query feature combined with the XML feature, you could very easily have a RESTful style data interface without much developer knowledge at all. And, it would fit the paradigm of Office much better than an api style interface done the web services way. I mean really, how many normal Office users are going to think about their spreadsheet like macros with buttons to call api's on servers. But, navigate to a web page, and you have rich data in and out, sounds pretty doable to me.
So why did I start all this with “messages?” To test out my XML features, which I originally started as a way to get test data into and out of excel for unit tests, I wired up a fairly simple web page to execute database queries and return the results in XML. This was based on my own pet project, SnapDAL which made it much simpler to do this because the tool uses a metadata file for queries that gave me user meaningful parameter names and query names. These I turned into a web page where you could test a db call, and then build a full URL that could be used in an Office web query. A simple next step is to also generate the web query .iqy file to create official data sources. So then, Office sends a message to a web server, the web server interprets the message and sends a new message to SnapDAL. SnapDAL interprets the message and sends a new message to Sql Server. Sql Server returns a tds stream grabbed by ado.net, converted to XML by SnapDAL, sent back to the Office app as XML, and ultimately, viewed as rows and columns in Excel.
Of course, this could have been done with web service calls. I would have had to write vba code, used soap libraries in excel, created a unique web service for each query, and all of it would have to be maintained by a programmer. The path I took, I could teach to almost any of our users in an hour I think.
Now, if I could just get Excel to use my parameters and still treat the data as XML! I'm thisssss close!