- 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:
http://www.localcubetask.com/
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.
Hi Chris,
Thank you very much for the post. Following the steps you mentioned I have been able to create the .Cub file in a local folder but while processing the cube I am getting a message that \’an unexpected error has occured\’. The cube processing continues for about 5 minutes, I see no progress messages in the processing window. Also there is no data in the cubes. How can I get the data in the cube?
How do I automate the processing and incremental data update of the .cub on a daily basis? Can I automate the processing using SSIS or a agent job?
Thank you for your help in advance. Bidyut
Hi Bidyut,
Rather than do this yourself (it is very, very tricky to get local cubes working) I would recommend you check out Tim\’s product, referenced above. Your error could be caused by any number of problems or bugs…
However if you do get it working and want it automated, then I guess it would be possible to do in SSIS by using the Execute Analysis Services DDL task.
Chris
Hi Bidyut,
Rather than do this yourself (it is very, very tricky to get local cubes working) I would recommend you check out Tim\’s product, referenced above. Your error could be caused by any number of problems or bugs…
However if you do get it working and want it automated, then I guess it would be possible to do in SSIS by using the Execute Analysis Services DDL task.
Chris
Dear Chris,
I’m able to folow the stems up untill the point where you say:
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).
The XML statement is being executed succesfully but there is no local cube file as a result from that.
I got rid of the piece starting after the actual xml statement:
Query Text: CREATE GLOBAL CUBE [BG_CUBE_SALES] STORAGE ‘….
because that seems to be syntactically incorrect.
What do you mean with reconnect to the path you want to create your local cube. It’s probably me but can you be more detailed about that please?
kind regards,
Christian
Hi Christian,
Probably the first thing to say is to not use local cubes! There are so many problems associated with them (buggy, slow, limited functionality etc) I don’t think they’re worth bothering with any more…
However, to answer your question, what I mean is that in MDX Sample App you need to close your connection to SSAS and then, when you open a new connection, enter the path to the .cub file instead of the server name. If no local cube is being created, it could be because SSAS doesn’t have rights to save the .cub file to the directory you’ve provided, or because the XMLA isn’t valid, or because of a bug. It’s hard to say unfortunately.
Chris
Hello chris,
thanks for your reply.
The problem is that I don’t have the mdx sample app available.
We are working with SQL Server 2008 R2.
I have googled for the mdx sample app, but I’m not able to find it anywhere to download…
Are you sure that there is no alternative in the MS SQL Management Console?
Christian
It’s been a while since I tried, but I’m pretty sure SQL Management Console won’t work. Your only alternative will be to write some code that runs the statement, I think. As I said, though, you really do not want to be using local cubes – try to search for an alternative solution (maybe PowerPivot?) to whatever you’re trying to do.
I understand what you’re saying.
Unfortunatelly, my boss want the users, not only to be able to play in Excel with the cubes on the server.
But he also wants our users to be able to download an offline cube, to work with, when they don’t have internet access, like when they are travelling and stuff.
So I don’t really have a choice but giving them the .cub files.
Thanks for your time though
Christian