www.cubeslice.com

Many companies that are switching to Microsoft SQL Server 2005 Analysis Services are considering the possibilities of using local cubes. This is happening both as a result of changes made in the structure of Analysis Services and because of improvements made in the capability to create local cubes. Here are some key reasons why local cubes have become more important in Analysis Services 2005:

Would you like to improve the speed of your cube browsing? Would you like your users to view OLAP data offline? You can do this with CubeSlice. We can demo CubeSlice for you, using your own cubes or with our sample cubes. Sign up now for a free demo.

1. Local cube files can dramatically improve browsing speed performance, especially when analyzing low levels of large dimensions. Cubes in Analysis Services 2005 can be larger and more complex, especially with the new option of putting multiple measure groups in a single cube. When you create a local cube, you can choose to include only the subset of the server cube data that you want to see. This can greatly improve browsing speed, especially when you are looking at a low level of a large dimension and you are able to build a local cube that reduces the number of members in that level. See the document entitled How to Dramatically Improve Browsing Speed for Microsoft SQL Server 2005 Analysis Services.

2. Local cube files can improve browsing performance because requests for additional data are handled on the local computer rather than across a network on an Analysis Server. Previous editions of Analysis Services used a fat client architecture, where much of the multidimensional data was cached on the local computer. Analysis Services 2005 uses a thin client architecture, with almost all of the browsing handled directly by the Analysis Server. If many users are browsing cubes at the same time, there is a potential for browsing speed to decline, if the Analysis Server does not have enough available memory to promptly handle all the requests. In some cases, browsing a local cube file can be significantly faster than browsing a cube on the Analysis Server.

3. Local cube files can now be encrypted and password-protected. In previous editions of Analysis Services it was not possible to encrypt local cube files. Anybody who had file access permissions to a local cube was able to read the content of that local cube. In Analysis Services 2005 it is possible to encrypt a local cube file and require users to provide a password each time they want to view the data in the local cube.

4. Analysis Services 2005 provides more precise control over the creation of local cube files. The Analysis Services Scripting Language provides full control over which elements are included in a local cube file. It is far more flexible than the commands that were previously available to create local cube files.

5. When you use local cube files you can give each user the specific data they need, or want, or are allowed to see. Analysis Services 2005 provides many new options for looking at multidimensional data:

• Browsing on individual attributes

• Multiple measure groups within a cube, each with its own set of dimensions and measures.

• Key Performance Indicators (KPI’s)

• Multiple perspectives within one cube

All these improvements make Analysis Services 2005 cubes more powerful and useful, but they can also make cubes more complex. You can reduce this complexity by using local cube files to give each user only the dimensions, levels, attributes, measures, and the slice of the cube’s data that is significant for them.

NOTE: It is especially important to limit the number of displayed attributes if you are using Analysis Services 2005 cubes with a browser designed for Analysis Services 2000, such as the Pivot Table in Microsoft Excel 2003. A browser designed for Analysis Services 2005 can display attributes by measure group and attribute type, but older browsers will typically display all the attribute hierarchies as a long list of dimensions. If you are using Analysis Services 2005, you should consider using a browser designed for it, such as the Pivot Table in Microsoft Excel 2007. If some of your users are using older browsers, it can be helpful to create local cube files that remove some or all of the attribute hierarchies.

How to create local cube files with Microsoft SQL Server 2005 Analysis Services

There are three ways to programmatically create local cube files with Analysis Services 2005.

The CREATE LOCAL CUBE command was the first method Microsoft provided to create local cube files. It has been deprecated in Analysis Services 2005 and is not supported in ADOMD.NET. It can still be used, but only with ADOMD.

The CREATE GLOBAL CUBE statement is supported in Analysis Services 2005, but it also has several limitations, most notably in not supporting the password-encryption of the local cube files.

The Create command in the Analysis Services Scripting Language (ASSL) provides the full Analysis Services 2005 local cube functionality.

See this article for a full description of the differences in using these three methods of local cube creation.