Differences in the various ways of creating SQL Server 2005 Analysis Services local cubes

This article considers the differences in AS2005 local cubes resulting from six different ways of creating them:

1. Using the Create Local Cube command programmatically.

2. Using the MDX Create Global Cube command.

3. Using the ASSL (Analysis Services Scripting Language) script generated by scripting a cube in the SQL Server Management Studio. This script is referred to as SSMS ASSL).

Excluding Unused Members to Reduce the Size of the Local Cube

One of the biggest advantages of CubeSlice Relational ASSL is that it is the only local cube creation option where you can exclude unused members from the local cube.

Cubes often have dimension members that are not being used – members that are not linked to any records in the fact table. This happens to a greater extent when a local cube is created with slicing – limited to one Sales Rep, for example.

By removing unused dimension members, the size of a local cube can often be reduced by 30% to 75%. We have even seen one situation where the size of the local cube was reduced by 99.6%.

If you choose to exclude unused members, you should also consider choosing the CubeSlice option to create the local cube using temporary tables. The use of temporary tables often dramatically speeds up the process of creating local cubes – and that’s especially true when excluding unused members.

Changing the Key Attribute to Reduce the Size of the Local Cube

CubeSlice Relational ASSL is also the only local cube creation option that allows you to change the key attribute of a dimension. This can greatly reduce local cube size and local cube creation time. This ability allows you to include some attributes from a large dimension without including the whole dimension.

If you remove the key attribute in any of the other local cube creation options, the key attribute will not be visible, but it will still be present in the local cube. This can dramatically increase the size and the creation time of the local cube.

Excluding Objects to Reduce the Size of the Local Cube

One of the most important issues when using local cubes is the ability to limit the size of the local cube files. In many situations a user doesn’t need all the dimensions, levels, measures, or members that are available in the server cube. You can make your local cube files smaller by removing parts of the server cube. These smaller local cubes are easier to transport, they can be created more quickly, and have faster browsing speed.

Create Local Cube gives you full control over the dimensions, levels, measures, and calculated members you want to include in the local cube.

Create Global Cube and the CGC ASSL give you a good deal of control, but there are some limitations. Some objects, such as calculated members and KPI’s can not be excluded. More significantly, many objects in AS2005 cubes have dependencies on other objects, and it can be difficult to know which objects are needed by other objects.

The SSMS ASSL creates a local cube file containing all the cubes in an AS2005 database. It can be a difficult task to edit the ASSL to remove specific cubes or parts of cubes. If you do so, you must also handle all the situations where one object is dependent on another object.

CubeSlice 9 shows all the objects in the source cube and gives users the ability to include or exclude individual objects. Numerous dependency issues are automatically handled, including the following:

• When a calculated member or KPI references a measure from an excluded measure group, the user is given a choice of excluding both the calculated member and that measure group or of including both of them.

• When a calculated member or KPI references an excluded measure from an included measure group, the measure is added, but is made invisible.

• When a measure uses a Measure Expression, the dependent measures are handled in the same way as they are for calculated members.

• The measure group referenced by a many-to-many dimension is automatically included when the dimension is included.

• When calculations reference calculations that are excluded, the excluded calculations are included in the cube, but are made invisible.

• When attributes are excluded, they are automatically included, but with an invisible attribute hierarchy if they are needed for levels of a user-defined hierarchy, if the attribute is the key attribute or a granularity attribute, if it is used in a calculation or a KPI, or if the attribute is needed for converting a Distinct Count measure into a calculation.

Problem Attributes

In working with local cube generation with Analysis Services 2005, we have faced some problems with certain attributes. In one case the problem was a result of an error we made in designing the Analysis Server cube. When we installed the Adventure Works cube, for example, we did not follow the directions carefully and included a member for the Large Photo field. The Business Intelligence Design Studio automatically generated a MemberValue for this member which had a binary data type. Because we had this member in our Analysis Server cube, the creation of the local cube failed.

The CubeSlice ASSL code generator attempts to identify attributes that could cause the creation of the local cube file to fail. These attributes are modified, if possible. If they cannot be modified, they are automatically removed.

Member Slicing

As mentioned above, member slicing can be very important for keeping the size of local cube files as small as possible. It can also be used to customize cubes so that each user gets only the information they are interested in seeing or are allowed to see.

Member slicing is supported by Create Local Cube, Create Global Cube, the CGC ASSL, the CubeSlice OLAP ASSL, and the CubeSlice Relational ASSL. It is not supported by the SSMS ASSL.

CubeSlice gives you the ability to choose the members to be used in the local cube by the use of a formula, such as the top ten best selling products.

Member Slicing Based on a Formula

CubeSlice OLAP ASSL, CubeSlice Relational ASSL, and the SSMS ASSL give you the ability to choose the members to be included in the local cube by the use of a formula, such as the top ten best selling products.

When using Create Local Cube and Create Global Cube you can only do member slicing by listing the specific members you want to include.

Multiple Local Cubes from Member Slicing

CubeSlice provides an easy way to make separate cubes for different member slices. If, for example, you want to create a local cube for each store, where each local cube file has only the data for the individual store, you can choose to create a separate cube for each member of the Store attribute (or level).

Distinct Count Measures

Measures with Distinct Count Aggregation appear correctly in local cubes created with the SSMS ASSL, the CubeSlice Relational ASSL, and the CubeSlice OLAP ASSL. With the other forms of local cube creation, the Distinct Count measure either prevents the local cube from being created or the Distinct Count always displays a value of ‘1’.

Browsing performance can be slower when Distinct Count aggregation is used in local cubes created with CubeSlice OLAP ASSL.

Linked Dimensions and Measure Groups.

Linked measure groups and dimensions cannot be used with Create Local Cube and the SSMS ASSL. They can be used with Create Global Cube, the CGC ASSL, and the CubeSlice OLAP ASSL. They are not supported at the present time with the CubeSlice Relational ASSL.

Semi-Additive Measures

The February 2007 edition of SQL Server Books Online states that certain dimensions must be added to the local cube when semi-additive measures are used.

If the ByAccount aggregation function is used the Account dimension must be included in the local cube.

If any of the time-sensitive aggregation functions are used (FirstChild, LastChild, FirstNonEmpty, LastNonEmpty, AverageOfChildren), the entire time dimension must be included.

Violating these rules can cause local cube creation to fail for Create Global Cube, the CGC ASSL, and the CubeSlice OLAP ASSL. Specifically, we have seen local cube creation fail when slicing in the time dimension and there are semi-additive measures being used in the cube.

This issue does not cause local cube creation with CubeSlice Relational ASSL to fail. However, it is important when slicing or otherwise limiting the time dimension, to make sure that you have not modified the data displayed by the semi-additive measures.

Member Properties

Member properties are attributes that have the AttributeHierarchyEnabled property set to False. They are sometimes used for sorting other attributes.

Member properties are supported in the CubeSlice Relational ASSL, SSMS ASSL, and Create Local Cube. They are not supported in CubeSlice OLAP ASSL, CGC ASSL, and Create Global Cube. If you reference one of these attributes in a Create Global Cube statement, the statement will fail.

In situations where a member property is used for sorting and that member property is not included in a local cube, the sorting will be displayed incorrectly.

Many-to-many Dimensions

The February 2007 edition of SQL Server Books Online states you must add the entire many-to-many dimension and the entirety of all dimensions common to the measure groups involved in the many-to-many relationship.

Violating these rules can cause local cube creation to fail for Create Global Cube, the CGC ASSL, and the CubeSlice OLAP ASSL.

When using CubeSlice Relational ASSL, you must include the intermediary measure group, but you do not have to include the entirety of all the affected dimensions.

Data mining dimensions

Data mining dimensions are not supported in any of the local cube creation methods.

A detailed look at features of the local cubes created with the different local cube creation methods

Chart 2 shows how specific features are handled in the various methods of creating local
cube files in Analysis Services 2005.

Chart 2. Local Cube Creation – Specific Features

MDX Script

The MDX Script cannot be used when creating a local cube with Create Local Cube.

When using Create Global Cube, the MDX Script is always fully included and cannot be modified. This can cause local cube creation to fail if the MDX Script references objects that have been removed from the local cube.

When using CGC ASSL and SSMS ASSL, the MDX Script can be edited.

When using CubeSlice Relational ASSL and CubeSlice OLAP ASSL you also have the option of editing the MDX Script. CubeSlice automatically excludes all portions of the MDX Script that reference measures or dimensions that are being excluded from the local cube, so local cube creation will not fail.

Measure Groups

Measure groups are included for all cube creation methods except for Create Local Cube.

Measure groups are created when you use the Create Global Cube syntax, though you do not specify them in the Create Global Cube statement. The appropriate measure groups will be created for the measures you include in the local cube.

Dimensions associated with a measure group

When using Create Global Cube syntax, all the dimensions associated with a measure group in the source cube will also be associated with the measure group in the local cube, unless the dimension is not included in the local cube at all. This is also true for the CGC ASSL and the SSMS ASSL.

The CubeSlice user interface allows you to remove a dimension from a particular measure group, while still including that dimension in other measure groups.

Dimensions aliased in the source cube

AS2005 allows a user to alias a single dimension several times in a cube. This is done in the Adventure Works sample database with the Time dimension.

These aliased dimensions are not properly displayed when a local cube is created using the Create Global Cube Syntax or the CGC ASSL and displayed using Microsoft Excel 2003.

Aliased dimensions are always displayed properly in Excel 2007. They are displayed correctly in Excel 2003 when the local cube is created with CubeSlice Relational ASSL or CubeSlice OLAP ASSL.

Automatic bucketing in source cube

Attributes using automatic bucketing are not displayed properly in local cubes created with Create Global Cube and CGC ASSL.

When Create Global Cube creates a local cube, all attributes that have bucketing are bucketed a second time. For example, the following buckets could be displayed in the Analysis Server cube:

1-3
4-8
9-15
16-23
24-35
36-50

These buckets would be bucketed again as they are displayed in the local cube as follows:

1-3-4-8
9-15-16-23
24-35-36-50

Bucketed attributes are displayed correctly in local cubes created with CubeSlice Relational ASSL, CubeSlice OLAP ASSL, and SSMS ASSL.

ROLAP dimensions in source cube

Starting with SQL Server 2005, SP2, all ROLAP dimensions must be converted to MOLAP dimensions in the ASSL. If they are left as ROLAP dimensions, the local cube will be created, but it cannot be opened. This conversion to MOLAP is done automatically by CubeSlice, but must be done manually when using SSMS ASSL.

More than three partitions in the source cube

You cannot use the SSMS ASSL to create a local cube if there are more than three partitions in any of the measure groups of the source cube. An error is generated and the local cube is not created.

This is never a problem when using Create Global Cube, the CGC ASSL, or the CubeSlice OLAP ASSL.

This is rarely a problem when using CubeSlice Relational ASSL. In this form of the ASSL, the separate partitions in the source cube are combined into three or fewer partitions. This can not be done when a measure group is using partitions from more than three different relational databases.