Local cubes in management studio

Local cubes where already introduced in AS2000 and it was possible in earlier versions of ProClarity Professional to create them directly in this client. I also think it was possible and with as many errors and problems in Excel 2003 and earlier. Most of my clients were interested in this feature but with many problems and limitations, like no support for calculated members nore parent-child dimensions, I never recommend anyone to use it. The exception is the local cube task that is a third party tool that produced local cubes in Data Transformation Services in SQL Server 2000.

 

This week I was asked to check the possibilities with local cubes again and started to search for information on the net and in the SSAS newsgroup. Most of the information  was in the newsgroup but it is not a big subject according to the hits I have got. Some, but not all of them, states that it is impossible to create them i management studio. This is also the view on page 544 in "Microsoft SQL Server 2005 Analysis Services" (SAMS). However I recalled some lines regarding local cubes in the SP2 documentation. I run SSAS2005 SP2.

 

You can start management studio and connect to the Adventure Works cube. Create a new MDX query and paste the following code: