Give Me Text

Every now and then someone will talk about the sad fact that we still use text files and text editors to build software. I used to think about this when I was in the DTS or OLAP cube designers of SQL Server 2000. I’d think about how writing software with a GUI wasn’t all it was cracked up to be. UI designers can be tedious, and in the Microsoft world they usually save data in opaque blobs.

Because of the blob factor, both DTS and OLAP resist attempts at serious configuration management. The deployment strategy for an OLAP cube is to drag the cube with a mouse from one server to the next in the MMC. I kept looking for the “generate script” option in the MMC – there is none*. One of my first tasks with OLAP was to write a utility to walk the object model exposed by OLAP’s COM interface and serialize the structure into an XML file.

XML is easier on the eyes than a structured storage file, in the same way it looks prettier on the wire when compared to a DCOM packet. You can check XML into source control, diff it, parse it, query it, and transform it. XML is friendly and comfortable. Using an XML format paid off for us recently when we needed to localize cubes for a client in another country. Add a little XPath, a little config file, and mes cubes parlent français.

In playing with the Business Intelligence Development Studio of SQL Server 2005, I am happy to see both Integration Services (DTS) packages and OLAP cubes save into files with an XML format. I’m even happier to see the “Add Solution To Source Control” option. It’s good to see progress. It’s good to see text!

* On the SQL 2000 Resource CD Microsoft released the MetaDataScripter. You can point this tool at an OLAP database and it will generate VBScript that can create the cube. For non-trivial databases though, the resulting vbs script can easily exceed 1MB. It is text, but it is not too friendly.