This article considers the differences in AS2005/2008 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).
4. When you create a local cube with the Create Global Cube command, the ASSL script generated by the command is displayed in the SQL Server Profiler. This script can be used by itself to create a local cube, and is referred to here as CGC ASSL.
5. Using the ASSL script generated by CubeSlice with an Analysis Services 2005/2008 server cube as the source. This script is called the CubeSlice OLAP ASSL.
6. Using the ASSL script generated by CubeSlice using a relational data source. This script is called CubeSlice Relational ASSL.
Chart 1 shows the primary differences in the creation of Analysis Services 2005/2008 local cube files using the six different methods described here. Each of these topics is discussed below.
Chart 1. Local Cube Creation – Primary Differences
|Major Feature||Create Local Cube||Create Global Cube||SSMS ASSL||CGC ASSL||CubeSlice Relational ASSL||CubeSlice OLAP ASSL|
|Deprecated in Analysis Services 2005||Yes||No||No||No||No||No|
|Data Source||Relational||Analysis Server||Relational||Analysis Server||Relational||Analysis Server|
|Ease of Use||Low||Moderate||Low||Low||High||High|
|Excluding Unused Members to Reduce the Size of the Local Cube||No||No||No||No||Yes||No|
|Changing the Key Attribute to Reduce the Size of the Local Cube||No||No||No||No||Yes||No|
|Excluding Objects to Reduce the Size of the Local Cube||Yes||Yes, but problems with dependent objects||Not without extensive editing||Yes, but problems with dependent objects||Yes, and dependent objects are automatically included or excluded||Yes, and dependent objects are automatically included or excluded|
Attributes not included
|Requires excluding whole dimension||Not without extensive editing||Requires excluding whole dimension||Automatically modified (if possible) or attribute alone is excluded||Automatically modified (if possible) or attribute alone is excluded|
|Member Slicing||Yes||Yes||Yes, with some difficulty||Yes||Yes||Yes|
|Member Slicing Based on a Formula||No||No||Yes, with some difficulty||No||Yes||Yes|
|Multiple Local Cubes from Member Slicing||No||No||No||No||Yes||Yes|
|Distinct Count measures||No||No||Yes||No||Yes||Yes, but hurts performance|
|Linked Measure Groups and Dimensions||No||Yes||No||Yes||Not at the present time||Yes|
|Semi-Additive Measures||No||Can cause local cube creation to fail||Could affect data in cube||Can cause local cube creation to fail||Could affect data in cube||Can cause local cube creation to fail|
|Many-to-Many Dimensions||No||With restrictions||With restrictions||With restrictions||With restrictions||With restrictions|
|Data mining dimensions||Not supported||Not supported||Not supported||Not supported||Not supported||Not supported|
Deprecated in Analysis Services 2005
It is important to recognize that the CREATE LOCAL CUBE statement has been deprecated by Microsoft in Analysis Services 2005. This statement is not documented in detail in Books Online and its functionality has been reduced from Analysis Services 2000. As a deprecated feature, it should not be used for new development, because Microsoft decided that it would probably not support it in future versions of SQL Server. It is not supported when using ADOMD.NET, but is supported when using ADOMD.
You can create a local cube from either a relational data source or from an Analysis Server cube.
When you create a local cube from a relational data source, you know you’re getting a local cube with data that is as current as the data in the source relational database. When you create a local cube from an Analysis Server cube you get the data that is current as of the last time that Server cube has been processed.
If you create local cubes from an Analysis Server cube you can have greater simplicity, because you only need to connect to the Analysis Server instead of having the need to connect to the relational data sources. You can possibly also have greater cube creation speed, because the data has already been aggregated.
In SQL Server 2000, you could use CREATE LOCAL CUBE to create a local cube from either a relational data source or from an Analysis Server cube. In SQL Server 2005/2008, CREATE LOCAL CUBE can only be used with relational data sources.
CREATE GLOBAL CUBE can only be used with an Analysis Server source.
The ASSL Create command can use either a relational or an Analysis Server data source. The ASSL created from the SQL Server Management Studio uses a relational data source. The ASSL generated from CREATE GLOBAL CUBE uses the Analysis Server source. CubeSlice 9 supports the generation of two types of ASSL –one that uses a relational source and one that uses an Analysis Server data source.
The new Analysis Services 2005/2008 password-protected encryption for local cubes can only be used with the ASSL Create command. It is not available for either CREATE LOCAL CUBE or CREATE GLOBAL CUBE.
Without encryption a local cube file can be viewed by anyone who has access to the file itself. Encryption allows organizations to distribute local cube files more widely because only individuals who know the password are able to view their contents.
Flexibility and Ease of Use
The CREATE GLOBAL CUBE command has the simplest syntax, but it is quite inflexible. You cannot use encryption. Some objects, such as calculated members and KPI’s, are included automatically, and you are not allowed to exclude them. You can choose to include or exclude dimensions, but not to include just some of the hierarchies or some of the attributes of a dimension.
The CREATE LOCAL CUBE command is more difficult and more powerful. However, in Analysis Services 2005/2008, its use is restricted to relational data sources.
The ASSL Create statement is very flexible, but is also difficult to use because it is very detailed. The Create statement for the Adventure Works cube in the sample database included with Analysis Services 2005 is 395 pages long when copied into Microsoft Word!
CubeSlice 9 provides a convenient visual interface to allow users to modify the ASSL Create statement.
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/AS2008 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 or AS2008 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:
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.
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.
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 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.
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/2008.
Chart 2. Local Cube Creation – Specific Features
|Feature||Create Local Cube||Create Global Cube||SSMS ASSL||CGC ASSL||CubeSlice Relational ASSL||CubeSlice OLAP ASSL|
|Database name in local cube file||Always same as cube name||Always same as cube name||Same as server database name||Same as cube name||Can be set by user (same as server database name by default)||Can be set by user (same as server database name by default)|
|Dimensions, Hierarchies, Levels and Measures||Choose which ones to include||Choose which ones to include||All included||Choose which ones to include||Choose which ones to include||Choose which ones to include|
|KPI's||No||All must be included||All included||All included||Choose which ones to include||Choose which ones to include|
|Actions||No||All must be included||All included||All included||Choose which ones to include||Choose which ones to include|
|Calculated measures and calculated sets||Include or exclude||All must be included||All included||All included||Choose which ones to include||Choose which ones to include|
|MDX Script||No||All must be included||Yes||Yes||Choose which portions to include||Choose which portions to include|
|Measure Groups||Cannot be used||Included for selected measures||Yes||Included for selected measures||Choose which ones to include||Choose which ones to include|
|Dimensions associated with a measure group||N/A||Same as in source cube||Same as in source cube||Same as in source cube||Choose dimensions for each measure group||Choose dimensions for each measure group|
|Dimensions aliased in the source cube||N/A||May not be displayed properly||Displayed properly||May not be displayed properly||Displayed properly||Displayed properly|
|Automatic bucketing in source cube||N/A||Not displayed properly||Displayed properly||Not displayed properly||Displayed properly||Displayed properly|
|ROLAP dimensions in source cube||N/A||Converted to MOLAP||Causes local cube to be unusable (sp2)||Converted to MOLAP||Converted to MOLAP||Converted to MOLAP|
|More than three partitions in source cube||N/A||Supported||Prevents the creation of the local cube||Supported||Almost Always Supported||Supported|
|Creating a local cube from a partition||N/A||No||No||No||Yes||Yes|
Database name in local cube file
When you use the Create Local Cube or the Create Global Cube syntax, the name of the database inside the local cube file is always set to be the same as the local cube that was created. If you put more than one local cube in the local cube file they will each be placed in a separate database.
When you use ASSL, you can name the database any way you choose and the name is easily changed. You can also put more than one cube in a single database inside the local cube file.
The CGC ASSL sets the database name to be the same as the cube name. The SSMS ASSL uses the database name from the Analysis Server. CubeSlice has a place to set the database name in the user interface, using the Analysis Server database name as the default.
Dimensions, Hierarchies, Levels, and Measures
All the methods of local cube creation except for the SSMS ASSL allow you to easily include or exclude particular dimensions, hierarchies, levels, and measures. The SSMS ASSL always includes all of these objects.
KPI’s, Actions, Calculated Measures, Calculated Sets, MDX Script
KPI’s, actions, calculated measures, and calculated sets are handled in different ways by the different methods of local cube creation.
The Create Local Cube syntax allows you to include or exclude calculated measures and sets. KPI’s and actions cannot be included when using Create Local Cube syntax.
The Create Global Cube syntax automatically includes all of these objects, without mentioning them. It is not possible to exclude these objects when using Create Global Cube.
The CGC ASSL and the SSMS ASSL also include all of these objects. You can edit the ASSL scripts to exclude specific objects, but it can be difficult to do so.
The CubeSlice user interface allows the user to select which of these objects are included in the local cube. CubeSlice also coordinates these selections with the selections the user makes regarding dimensions, hierarchies, levels, and measures. If an object is excluded by the user, but that object is needed for an included calculation or an included KPI, one of two things is done:
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 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 and AS2008 allow 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:
These buckets would be bucketed again as they are displayed in the local cube as follows:
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.
Creating a local cube from a perspective
Local cubes can only be created from perspectives with CubeSlice Relational ASSL and CubeSlice OLAP ASSL.