Why Local Cube Creation with ASSL is Superior to Local Cube Creation with the Create Global Cube Command

The Create Global Cube command is the most commonly used method of creating a local cube for Analysis Services in SQL Server 2005/2008. It is the method used by Microsoft Excel to create an offline cube from an Analysis Server cube. It is also the method used by the Microsoft Office PerformancePoint Server when a user chooses to work with data offline.

But Microsoft provides a much better way to create local cubes – the Analysis Services Scripting Language (ASSL). This paper outlines the reasons why ASSL creates better local cubes than the Create Global Cube command.

An ASSL script is an XML document. The format of ASSL is defined in the SQL Server Books Online. The ASSL discussed in this paper is the ASSL that is generated by CubeSlice, a third-party tool that creates local cube files.

The problems with the Create Global Cube command have led some people to think that local cube creation in Analysis Services 2005/2008 is unreliable. In our experience, AS2005/2008 local cube creation is very reliable, but it is much more reliable and flexible when using ASSL to create the local cubes.

I have tried to list the differences between Create Global Cube creation and ASSL creation in order of importance, with the most significant differences listed first. This testing was done with SP2 of SQL Server 2005.

1. CubeSlice ASSL allows you to remove unused members from dimensions.

Impact: Greatly reduced local cube size and local cube creation time – often 30-75% reduction, in one case a 99.6% reduction.

Discussion: When using Create Global Cube all unused members from all dimensions are included in the local cube, unless those dimensions have specific filters assigned to them. If you are creating local cubes for one particular store, there will be many irrelevant members from other dimensions included in the local cube, such as customers from other stores and products that aren’t carried by that particular store.

In CubeSlice ASSL the source queries that extract the data from the relational database tables are modified, giving the option to filter out all unused members. This greatly reduces the size and the creation time of the local cube.

Here’s an example using the Adventure Works cube from the sample Adventure Works DW database that is provided by Microsoft. This test was done using all the measures and dimensions from two measure groups – Internet Sales and Exchange Rates.

Using Create Global Cube, the local cube file size is 17.3 MB.

Using Create Global Cube and slicing the Sales Territory dimension on the Central US Region, the local cube file size is 15.1 MB.

Using CubeSlice ASSL, slicing on the Central US Region, and removing unused members, except in the time dimensions, the local cube file size is 6.3 MB.

2. CubeSlice ASSL allows you to change the key attribute of a dimension.

Impact: Greatly reduced local cube size and local cube creation time. This capability makes it possible for you to include a portion of a large dimension without making the local cube a lot larger.

Discussion: The key attribute is the value that joins a dimension table to the fact tables. The key attribute is at the lowest level of granularity. It cannot be changed when using the Create Global Cube statement. Even when a single attribute from a dimension is included in the local cube, it appears that the key attribute (and all other attributes) are included in the local cube, even though they are not visible.

For example, here are the sizes of the local cubes created by Create Global Cube using the Adventure Works cube from the sample Adventure Works DW database.

One measure: Internet Sales
One dimension: Customer
Local cube size: 10.1 MB

One measure: Internet Sales
One visible attribute, with a total of 5 members: Commute Distance from the Customer dimension
Local Cube Size: 9.9 MB

CubeSlice modifies the source queries for the local cube in a way that allows the key attribute to be changed. In this example, the key attribute could be switched to Commute Distance, making the local cube a lot smaller and greatly reducing local cube creation time.

Using CubeSlice ASSL, switching the key value of the dimension:
One measure: Internet Sales
One visible attribute, with a total of 5 members: Commute Distance from the Customer dimension
Local Cube Size: 1.3 MB

3. ASSL gives you the option of creating a local cube directly from the relational database tables.

Impact: Dramatic reduction in local cube creation time.

Discussion: The Create Global Cube command creates local cubes from an Analysis Services cube. This can be very inefficient, particularly when you have a large Analysis Services cube and you want to create many relatively small local cubes from it.

With ASSL, you can create local cubes directly from the relational database tables, eliminating the time it takes to load all the data into the cube and the time it takes to extract the data into separate local cube files. By creating local cubes directly from relational database tables you also have the opportunity to optimize indexes for rapid local cube creation.

4. ASSL allows you to password-encrypt local cube files.

Impact: Some users need password protection for their local cubes.

Discussion: One of the new features of local cubes in SQL Server 2005 versus 2000 was the ability to encrypt local cube files. Only users that supply the correct password are able to view the data in these local cubes.

This password-encryption feature is not available when using Create Global Cube. You can only use it when you create a local cube with ASSL. This Create Global Cube limitation has been documented by Microsoft.

5. Create Global Cube does not allow you to include Distinct Count measures.

Impact: Distinct Count measures are very important for many cube users. Losing them significantly reduces the value of a local cube.

Discussion: Local cube creation fails when using Create Global Cube if any Distinct Count measures are included. This limitation has been documented by Microsoft.

Distinct Count measures are fully supported in local cubes created with CubeSlice ASSL.

6. The Create Global Cube command will fail in some situations when using semi-additive measures.

Impact: There is a loss of flexibility in the use of semi-additive measures in local cubes.

Discussion: When using Create Global Cube, the following rules must be followed:

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. 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 limitation has been documented by Microsoft.

This issue does not cause a local cube creation failure when using CubeSlice ASSL from a relational source. However, it is important when slicing or otherwise limiting the time dimension, to check the semi-additive measures to make sure they are still displaying correct data.

7. Create Global Cube does not allow the inclusion of attributes that have the AttributeHierarchyEnabled property set to False.

Impact: These attributes (sometimes referred to as member properties) cannot be included in the local cube. If they are used for sorting another attribute, the sorting will be incorrect in the local cube.

Discussion: When using Create Global Cube, if a dimension is fully included in the local cube, all attributes with the AttributeHierarchyEnabled property set to False are automatically excluded. If attributes are referenced individually in the Create Global Cube statement and one of these attributes is included, the local cube creation will fail. This limitation has been documented by Microsoft.

CubeSlice ASSL with a relational source allows you to include all of these attributes in the local cube. When they are used for sorting, the sorted attributes are displayed correctly.

8. Create Global Cube creation fails if a dimension contains any other attributes that are unacceptable in a local cube.

Impact: It is hard to identify all the situations that cause Create Global Cube to fail.

Discussion: It appears that some inconsistencies are allowed in an Analysis Server cube, but are not acceptable when creating a local cube with Create Global Cube. One example of this is having an inappropriate data type in the Analysis Server cube. The Analysis Server cube processed without any problem, but the Create Global Cube statement failed without giving a clear error message.

CubeSlice ASSL automatically corrects for known problems such as inconsistent data types in the Analysis Server cube. If there is a situation where an adjustment cannot be made, the dimension is still included in the local cube with just the problem attribute removed.

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

Impact: Attributes with bucketing cannot be shown in a local cube.

Discussion: 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

CubeSlice ASSL displays all bucketed attributes correctly.

10. CubeSlice ASSL allows you to include a set of members based on a formula.

Impact: Members included in a cube can be selected dynamically rather than by listing them out individually.

Discussion: Create Global Cube allows you to slice on one or more members. To slice, you list the members to be included in the creation statement. The members must be listed out individually. You can not specify the members for slicing by using a formula.

When using CubeSlice ASSL, you can also slice the local cube by listing individual members. In addition, though, you can slice by using an MDX set formula, such as the Customers that had Purchases in Excess of $1,000 Last Year But No Purchases This Year To Date. This capability allows you to create local cubes that are customized for specific purposes.

11. CubeSlice ASSL allows you to exclude specific calculated members, KPI’s, and actions.

Impact: You can customize the local cubes to only include desired functionality.

Discussion: When using the Create Global Cube statement you do not include any information about calculated members, KPI’s, and actions. All these objects are automatically included in the local cube and you cannot exclude them. This limitation has been documented by Microsoft.

CubeSlice ASSL gives you full control to include or exclude any of these objects.

12. Calculated measures in a local cube created with Create Global Cube may disappear with no error displayed.

Impact: It is difficult for users to determine why a calculated measure is missing in a local cube created with Create Global Cube.

Discussion: The Create Global Cube statement allows you to remove any specific measure or dimension from the local cube. If you remove a measure or dimension that is needed for a calculation that calculated measure will disappear with no error message being given to the user.

CubeSlice ASSL automatically includes all objects required by calculations that the user has chosen to include. If the user has excluded any of these required objects they will be included in the local cube invisibly, so that the calculated measures can still be properly displayed.

13. The Create Global Cube statement will fail if there are invalid statements in the MDX Script.

Impact: Portions of the MDX Script may be invalidated by the removal of measures or dimensions. If the MDX Script becomes invalid the local cube creation will fail.

Discussion: Just like calculated members, the MDX Script is always completely included in a local cube made with Create Global Cube. If the MDX Script references objects that are not being included in the local cube, the local cube creation will fail.

CubeSlice ASSL automatically excludes all portions of the MDX Script that reference measures or dimensions that are being excluded from the local cube, so the local cube can be created.

14. CubeSlice ASSL provides more flexibility when slicing on a parent-child dimension

Impact: Slicing on parent-child dimensions is often useful and can reduce the size of the local cube.

Discussion: When using Create Global Cube, all slicers on a parent-child dimension are ignored. This limitation has been documented by Microsoft.

CubeSlice ASSL allows you to slice on members of parent-child dimensions. This slicing works correctly most of the time, but not all of the time. Some slicers in parent-child dimensions cause the CubeSlice ASSL local cube creation to fail. This is particularly true with slicers that are connected to a small number of records in the fact table.

15. Create Global Cube does not allow the creation of a local cube from a perspective.

Impact: Perspectives provide a convenient way to limit the data available to a particular user. When they are defined, it is also convenient to use them to create local cubes.

Discussion: When using Create Global Cube, you cannot create a local cube from a perspective. This limitation has been documented by Microsoft.

CubeSlice ASSL allows you to create a local cube from a perspective. If there are calculations or other objects that depend on objects not included in that perspective, those objects are added but are not visible in the local cube.

16. Create Global Cube syntax is not fully documented and can be confusing.

Impact: It can be difficult to find the correct syntax to create a local cube.

Discussion: Though the Create Global Cube syntax is fairly simple, we have seen several situations where it is difficult to use correctly. Here is one example:

It would seem that the following syntax should create a local cube that includes only one attribute of the Customer dimension:

CREATE GLOBAL CUBE [TestOneAttribute] STORAGE ‘c:\test.cub’
FROM [Adventure Works]
(
MEASURE [Adventure Works].[Internet Order Quantity],
DIMENSION [Adventure Works].[Customer].[Commute Distance]
)

This syntax, however, creates a local cube that displays all the attributes of the Customer dimension. The only portion of the dimension that is not displayed in the local cube is the Customer Geography hierarchy.

In order to create a local cube that just displays one attribute, the following code must be used:

CREATE GLOBAL CUBE [TestOneAttribute] STORAGE ‘c:\test.cub’
FROM [Adventure Works]
(
MEASURE [Adventure Works].[Internet Order Quantity],
DIMENSION [Adventure Works].[Customer].[Commute Distance]
(
LEVEL (All),
LEVEL ([Commute Distance])
)
)

CubeSlice provides a graphical user interface for selecting which portions of the Analysis Server cube should be included in the local cube, so there is no need to directly manipulate the ASSL code.

17. Create Global Cube changes the name of the database in the local cube file.

Impact: Client applications may have trouble switching between an Analysis Server cube and a local cube because the database name is different.

Discussion: The Create Global Cube statement always sets the database for the local cube to be the same as the name you give to the cube in the local cube file. For example, you might have an Analysis Server database named FoodMart and a cube named Sales. In the local cube, if you name the cube Sales, then the database will also be named Sales.

CubeSlice ASSL allows you to set the database name and the cube name to be whatever you want. By default they are set to be the same as on the Analysis Server, so that, in this example, the database inside the local cube file would be FoodMart and the cube would be Sales.