download

doc
Filesize: 150528
Click Here To Download...

Using Local Cubes with Microsoft SQL Server 2005 Analysis Services Updated December 14, 2007 Summary

This paper describes the use of local cube files in a Business Intelligence system built with 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.

A local cube is a file that contains multidimensional data. Client applications can connect to a local cube file in the same way that they connect to cubes stored on a Microsoft Analysis Server. By using local cube files, users can view cube data when they’re not connected to the network.

There are several different ways of creating local cube files in Analysis Server 2005. This paper describes the advantages and disadvantages of each local cube creation method.

The information in this paper has been primarily obtained through practical experience working on the development of CubeSlice, a product that automates the creation of local cube files. I have used Microsoft documentation when it was available, but much of this information has not been documented by Microsoft. I would like to thank Chris Webb and the other authors of MDX Solutions With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase for their writing about local cubes. I would greatly appreciate input from anyone to help correct errors and to provide greater insight into local cube creation with Analysis Services 2005.

The importance of local cube files when using Microsoft SQL Server 2005 Analysis Services.

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:

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, available at www.cubeslice.com.

2. Local cube files can also 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 (ASSL) 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. In this paper I describe the different local cube support provided by an ASSL Create command generated in four different ways:

The ASSL generated by scripting a cube in the SQL Server Management Studio (referred to as SSMS ASSL). The ASSL script created by issuing a CREATE GLOBAL CUBE command (CGC ASSL). You can see this script displayed in the SQL Server Profiler The ASSL script generated by CubeSlice using an Analysis Services 2005 server cube as the source (CubeSlice OLAP ASSL). The ASSL script generated by CubeSlice using a relational data source (CubeSlice Relational ASSL).

CubeSlice 9 is available from www.cubeslice.com. CubeSlice displays the full text of the ASSL script it generates to create a local cube file. You can view this script by choosing the Edit Code button on the Local Cube Options form.

Primary differences in the local cubes created with the different local cube creation methods

Chart 1 shows the primary differences in the creation of Analysis Services 2005 local cube files using the six different methods described in this paper. Each of these topics is discussed in detail on the following pages.

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

Data Source

Relational

Analysis Server

Relational

Analysis Server

Relational

Analysis Server

Password-protected Encryption

Yes

Flexibility

Moderate

Low

High

Ease of Use

Low

Moderate

Low

High

Excluding Unused Members to Reduce the Size of the Local Cube

Yes

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

Yes

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

Problem Attributes

Attributes not included

Problem attributes can be excluded

Not without extensive editing

Problem attributes can be excluded

Automatically modified (if possible) or the attribute alone is excluded

Member Slicing

Yes

Yes, with some difficulty

Yes

Member Slicing Based on a Formula

Yes, with some difficulty

Yes

Multiple Local Cubes from Member Slicing

Yes

Distinct Count measures

Yes

Yes, but hurts performance

Linked measure groups and dimensions

Yes

Not at the present time

Yes

Semi-Additive Measures

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

Member Properties

Yes

Many-to-Many Dimensions

With limits

Data mining dimensions

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 has said they may not support it in future versions of SQL Server.

Data Source

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.

When you create a local cube from a relational data source, you have more control over what is included in the local cube. You have the option of removing unused members, which can greatly reduce the size of the local cube and speed up creation time.

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.

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

Password-Protected Encryption

The new Analysis Services 2005 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 somewhat inflexible and can be difficult to use. You cannot use encryption. Some objects, such as calculated members, KPI’s, actions, and the MDX Script, are included automatically, and you are not allowed to modify them. You can choose to include or exclude measures, dimensions, levels, and attributes. The syntax for including individual attributes is not well documented.

The CREATE LOCAL CUBE command is more difficult and more powerful. However, in Analysis Services 2005, 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 generate an ASSL Create statement that includes exactly what the user wants to have included.

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 all forms of local cube creation. It is difficult to implement when using the SSMS ASSL.

Member slicing in parent-child attributes is not supported when using Create Global Cube. Parent-child attribute slicing is supported by CubeSlice OLAP ASSL and CubeSlice Relational ASSL, though it occasionally results in an error when creating the local cube.

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 Measure Groups and Dimensions

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 inappropriately 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

When using a many-to-many dimension, the intermediary measure group must always be included in the local cube.

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

Major 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

Same as server database name

Same as cube name

Can be set by user (same as server database name by default)

Dimensions, Hierarchies, Levels, and Measures

Choose which ones to include

All included

Choose which ones to include

KPI’s

All must be included

All included

Choose which ones to include

Actions

All must be included

All included

Choose which ones to include

Calculated measures and calculated sets

Include or exclude

All must be included

All included

Choose which ones to include

MDX Script

All must be included

Yes

Choose which portions to include

Choose which ones to include

Measure Groups

Cannot be used.

Included for selected measures

Yes

Included for selected measures

Choose which ones to include

Dimensions associated with a measure group

N/A

Same as in source cube

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

Automatic bucketing in source cube

N/A

Not displayed properly

Displayed properly

Not displayed properly

Displayed properly

ROLAP dimensions in source cube

N/A

Converted to MOLAP

Causes local cube to be unusable (sp2)

Converted to MOLAP

More than three partitions in source cube

N/A

Supported

Prevents creation of the local cube

Supported

Almost always supported

Supported

Creating a local cube from a perspective

N/A

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, 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

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:

If the required object can be included invisibly, CubeSlice will do that. If the required object cannot be included invisibly, CubeSlice will ask the user if they want to include the selected object and the required object. If the user chooses to include them, they are both included. If the user chooses not to include them, they are both excluded.

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 can be a problem when using CubeSlice Relational ASSL, but not very often. When using CubeSlice Relational ASSL, the separate partitions in the source cube are combined into three or fewer partitions. This cannot be done when a measure group is using partitions from more than three source relational databases. In those situations, the CubeSlice Relational ASSL will not be able to create the local cube.

Creating a local cube from a perspective

Local cubes can only be created from perspectives with CubeSlice Relational ASSL and CubeSlice OLAP ASSL.

Summary of differences between the two forms of CubeSlice ASSL

CubeSlice uses two forms of ASSL (Analysis Services Scripting Language) to create local cubes. The two forms of ASSL created with CubeSlice are very similar. Here are the main differences:

1. CubeSlice Relational ASSL builds local cubes directly from the data in the source relational database, while CubeSlice OLAP ASSL builds local cubes from the Analysis Server cube. When choosing which data source to use, you have to consider:

Permissions to access the Analysis Server and the source relational server. The additional usage of the source server, either relational or Analysis Server. The concurrency of the data. When using Relational ASSL the current data in the relational database is loaded into the local cubes. When using OLAP ASSL, data is loaded that is current as of the last time the Analysis Server cube was processed. CubeSlice Relational ASSL is fully supported only when the source relational database is Microsoft SQL Server. When you are using other relational data sources some features, such as member slicing, are not supported with Relational ASSL.

2. When creating local cubes with CubeSlice Relational ASSL you have the option of removing unused dimension members, which can greatly decrease the size of your local cubes and the time needed to create them.

3. When creating local cubes with CubeSlice OLAP ASSL, you have the option of changing the key attribute of each dimension. This also greatly decreases the size of your local cubes and the time needed to create them.

4. Local cubes created with CubeSlice OLAP ASSL can have slower browsing performance when certain features are included in the local cubes. Here are the features that can slow browsing in a CubeSlice OLAP ASSL local cube:

Unary operators Custom rollup formulas Measures with Distinct Count aggregation

5. Local cubes created with CubeSlice OLAP ASSL are sometimes (but not always) larger than local cubes created with CubeSlice Relational ASSL.

6. The creation of a local cube from a server cube that has linked dimensions and/or linked measure groups is supported when using CubeSlice OLAP ASSL. At the present time, this functionality is not supported by the CubeSlice Relational ASSL.

7. More cubes can be successfully created with slicing when using CubeSlice Relational ASSL. Local cube creation with CubeSlice OLAP ASSL can fail when slicing on the time dimension, if there is also a semi-additive measure included in the local cube. Slicing can also cause local cube creation to fail when a many-to-many dimension is included in the local cube. CubeSlice Relational ASSL does not have these problems.

8. When unary operators are used along with measures using semi-additive aggregations, the data in the local cubes can differ. This happens in the following situation:

Unary operators are being used A semi-additive measure (such as Most Recent Non-Null Value) is being used There is no data for a time period in the source data, so that a previous value is displayed in the cube.

In this situation the CubeSlice Relational ASSL local cube ignores the value in the semi-additive measure and does not include it in aggregations, matching the behavior in the Analysis Server cube. The CubeSlice OLAP ASSL local cube uses the value in the semi-additive measure and aggregates it into the higher levels using the specified unary operator.

Differences in local cube files between SQL Server 2000 and SQL Server 2005

Chart 3 shows the primary differences between local cube files created with SQL Server 2000 and those created with SQL Server 2005.

Feature

SQL Server 2000

SQL Server 2005

New Analysis Services 2005 features – translation, attributes, measure groups, KPI’s, MDX Script

Not available

Supported

Password-protected encryption for local cube files

Yes

Creation of local cube files with no data.

Not supported

Supported

Inclusion of members that have no data in a local cube

Yes, except they can be removed with CubeSlice Relational ASSL

Multiple users can use a local cube file at the same time.

Yes

Unary Operators

Do not work properly when used with parent-child dimensions

Supported

Custom Rollup Formulas

Supported

Password-protected encryption for local cube files

Analysis Services 2005 gives the option of encrypting a local cube file. The same password used to encrypt the file must be used to decrypt it. This password is included in the connection string as the Encryption Password parameter both when creating the local cube file and when opening it.

New Analysis Services 2005 features

One of the main advantages of the Analysis Services 2005 local cube files is the ability to include the new Analysis Services 2005 features. The following AS2005 features are all fully supported in local cubes:

Translation Attributes Measure Groups KPI’s MDX Script

Creation of local cube files with no data

In SQL Server 2000, the creation of a local cube file fails when there is no data in the local cube. This can happen when multiple slicers are used and the combination of slicers eliminates all the data.

In SQL Server 2005, a local cube file can be created with no data. When browsing a local cube file with no data, you can view the members of the levels, but no cells are displayed.

Inclusion of members that have no data in a local cube

In SQL Server 2000, individual members are not included in the local cube if they have no records in the fact table. This can cause problems, especially when using calculations with time dimensions.

In SQL Server 2005, all members are included in the local cube, even if they do not have records in the fact table. This behavior can cause the AS2005 local cube file to be much larger than a comparable AS2000 local cube.

When using CubeSlice Relational ASSL, you can choose to remove unused dimension members.

Multiple users can use a local cube file at the same time

In SQL Server 2000 local cubes, many users can use one local cube file at the same time.

SQL Server 2005 local cube files can only be used by a single user at a time. A single application can, however, open up to five connections simultaneously.

Unary Operators

In SQL Server 2000 local cube files, unary operators do not work properly with parent-child dimensions.

SQL Server 2005 local cube files display unary operators properly.

Custom Rollup Formulas

SQL Server 2000 does not support the creation of local cube files with custom rollup formulas.

Custom rollup formulas are fully supported in SQL Server 2005 local cube files.

Download download.doc

Comments

Your talk will be first one...

Google Search

RECENT SEARCHES