Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

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]
 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.

3 Responses to “Local cubes in management studio”

  1. 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 –
    Good luck in using local cubes!

  2. 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.

  3. Sam Kane said

    Here are this and some other articles on Local Cubes:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: