Creating AS2005 Local Cubes with XMLA
- Open MDX Sample app – yes, this is another one of those areas where SQLMS won’t do the job. Connect to the Adventure Works database
- Paste a CREATE GLOBAL CUBE statement into a query pane. Don’t run it yet though…
- Start a new Profiler trace on your target database
- Run the CREATE GLOBAL CUBE statement, then stop the trace
- Inside your trace, you’ll see the CREATE GLOBAL CUBE statement appear twice followed by a statement which begins with something like this: "CREATE GLOBAL CUBE statement. FILENAME|c:\MyfileName.cub|DDL|" and is followed by an XMLA batch statement
- Copy the XMLA batch statement out to MDX Sample App (if you find you can’t do this, you might need to save the trace as an XML file and open it in Notepad or something) and
- Close the connection to your server cube in MDX Sample app, then reconnect to the path you want to create your new cub file at, eg "c:\cubtest.cub" (instead of supplying the name of your server in the connection dialog box).
- Run the Batch statement, and your local cube is created!
The problem with local cubes in AS2K were numerous: they were hard to create, the creation process was so buggy/difficult that you could never be sure you’d succeed, and the query performance of local cubes greater than around 100Mb was atrocious. AS2005 local cubes have, in my understanding, been completely rewritten though and apart from the new way of creating them they’re also a lot more functional (see http://sqljunkies.com/WebLog/mosha/archive/2006/03/08/local_cube_password.aspx for example – they now have security!) and I recently did some testing on query performance that showed that a local cube of approx 230Mb actually performed better than the equivalent server cube. Unfortunately I also found out that trying to build a larger cube – at least with the cube design I had – led to some unpleasant crashes: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=189679.
But perhaps in a few service packs time local cubes will be ready for use. I guess local cubes have also been neglected because the promise of 100% network connectivity has always been just around the corner, so the thinking has been that there’s no point investing in local cubes when in a few years everyone will always be able to connect to a server cube. Increasingly, though, the fact that imperfect connectivity will be a fact of life at least in the medium term is sinking in and new tools are appearing which deal with this – think of the kind of BI solutions you could build with local cubes and Groove 2007, for example, where you could build you local cubes centrally and distribute them to your sales force using Groove’s file synchronisation features (which I assume is possible – I’ve not tested this!).
This might also be a good point to link to Tim Peterson’s site on local cubes:
When I last talked to him, he mentioned that he was working on an AS2005 version of localcubetask and it seems that there was indeed a release earlier this year.