Local cubes in management studio
Posted by thomasivarssonmalmo on December 29, 2007
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:
CREATE GLOBAL CUBE [InternetSalesSimple]
Storage ‘C:Local CubeInternetSalesSimple.cub’
FROM [Adventure Works]
(
MEASURE [Adventure Works].[Internet Sales Amount],
DIMENSION [Adventure Works].[Product],
DIMENSION [Adventure Works].[Date],
DIMENSION [Adventure Works].[Customer]
);
Storage ‘C:Local CubeInternetSalesSimple.cub’
FROM [Adventure Works]
(
MEASURE [Adventure Works].[Internet Sales Amount],
DIMENSION [Adventure Works].[Product],
DIMENSION [Adventure Works].[Date],
DIMENSION [Adventure Works].[Customer]
);
You will have to create a folder according to your requirements and I assume that SSAS2005 is on the same machine as I run managemen studio on. You can check in the folder that a *.cub file is created. If you run this on a separate server you will have to change the folder to a network name.
After this was finished I started ProClarity Professional 6.3 and connected to this local cube file. I also stopped the SSAS2005 services to check that I was not dependant on that and that this is a fully disconnected mode.
The result was interesting. I have only one measure but all calculated members that are only using this measure will be created without you having to specify it. If you specify a calculated member you will get an error. KPI:s, with only this measure, will also be created. I also included a parent-child dimension, account in Adventure Works, and it was created as well(something that was not working in AS2000 local cubes).
The only problem I have seen this far is that currency values seems to be converted to the local settings of my computer.
Thomas.I Microsoft BI & Analytics 
Tim said
Thank you for referencing our product, the Local Cube Task. For Analysis Services in SQL Server 2005 we have developed a new product we call CubeSlice. We think local cube files can be very useful, not only in allowing disconnected use of OLAP data, but also in providing a subset of an Analysis Server cube for very fast cube browsing.
We support the Create Global Cube syntax in AS2000 but are not supporting it in AS2005, because it has a number of limitations. Here is our analysis of its limitations – http://www.cubeslice.com/asslandcreateglobalcube.htm
Good luck in using local cubes!
Thomas said
Hello Tim. We have both a more limited scenario where Create Global Cube will work and a more complex where the solution in your whitepaper will be appropriate(security, more control of objects created and distinct count measures like you paper tells). I wrote the blog post because I was surprised that it worked in management studio.
Sam Kane said
Here are this and some other articles on Local Cubes:
http://ssas-wiki.com/w/Articles#Local_Cube