The Local Cube Information Center
The Local Cube    Information Center    
www.cubeslice.com      

How to Dramatically Improve Browsing Speed Performance for Microsoft SQL Server 2005/2008 Analysis Services

This article describes an unconventional strategy that can dramatically improve browsing speed performance for Microsoft SQL Server 2005 and 2008 Analysis Services.

Browsing speed is a defining characteristic of OLAP. All the effort that has been devoted to developing multidimensional database servers has been motivated by a desire to give users the ability to rapidly move between different views of the data – drilling up and drilling down, adding or removing dimensions, applying calculations, showing particular perspectives of the data based on user-defined criteria.

A common rule-of-thumb for OLAP is that browsing results should be displayed within 5 seconds of a user’s selection. In many situations Microsoft Analysis Services returns results in less than a second, performance that appears to the user to be instantaneous. But there are some situations, such as working with lower levels of large dimensions, where browsing speed can slow down.

OLAP data is less valuable when performance declines. Users want to see their data quickly and will be less likely to browse for new data if they have to wait to see the results. If drilling down to a lower level or applying a calculation takes a long time, new users will sometimes give up on using OLAP before they have had the chance to experience its analytical power.

Microsoft has provided the ability to create local cube files so that users can browse OLAP data while they are disconnected from the Analysis Server. A little-noticed side benefit of local cubes is the dramatic improvement they can bring to browsing performance. Browsing speed can be improved by creating a set of local cube files from an Analysis Server cube, with each local cube containing a particular subset of the data.

Using local cube files will not speed browsing in every situation. However, almost every organization using Analysis Services could benefit from using local cube files in certain situations. This article describes when to consider using local cubes to boost performance and how it can be done successfully.

This article is specifically focused on the use of local cubes to enhance Analysis Services 2005 and 2008 performance. If you are interested in other (and more conventional) performance enhancing ideas, I would suggest reading the technical article available on Microsoft’s web site - Microsoft SQL Server 2005 Analysis Services 2005 Performance Guide by Elizabeth Vitt, February, 2007.

Situations where browsing performance declines

In my experience these five situations cause the most significant slowdown in performance:

1. A level has a large number of members (such as Customers or Orders)

2. An individual member has a large number of child members (as can happen when drilling down from the All level to the Customer level, if the Customers are not grouped into categories)

3. Calculation, filtering, or ordering is applied to a level with a large number of members.

4. Another dimension is placed after a level with a large number of members in the display grid (such as placing Products after Line Order Details)

5. Large levels from different dimensions are included in the display together

Sometimes users have to be taught certain restrictions to avoid having performance problems when browsing a cube, such as adding filters before drilling down to a large level. If the filters are added first, the number of members can be presented in a reasonable length of time. If, however, the user drills down to the lower level first, too many members are returned and the client tool takes a long time to display the data. These kinds of restrictions are followed automatically by experienced cube users, but can be a severe limitation for users starting out with OLAP.

We have prepared a performance demo that shows several browsing examples which perform well with local cubes but could cause performance problems when using an Analysis Server cube. You can download this performance demo here.

All the examples in the performance demo use the Adventure Works cube from the Adventure Works sample database that is provided with SQL Server 2005. This database has a dimension called Reseller Sales Orders, which has 60855 members at the lowest level. We encourage you to look at the browsing speed we achieve in this demo and then try similar browsing using an Analysis Server cube as the data source.

Here is a description of the local cubes and views displayed in the performance demo:

1. The first view uses a local cube containing Reseller Sales Orders for only one Employee for 2004. Instead of 60855 Reseller Sales Order Details, this cube has 638. This allows the Sales Orders and Order Details to be browsed quickly. The view shows the Sales Orders displayed on the rows together with the Time dimension, so that the user can view orders by time periods.

2. The second view uses a similar local cube with data for 2003. This view displays the Line Item Details for each product. Try browsing an Analysis Server cube in the same way. Unless a user applies appropriate filters first, it will take a long time to display the data when the Line Item Details are added to the pivot table.

3. The third view uses a local cube with data for 2002. This view shows Line Item Details for a Reseller Bank and Reseller. The Product attribute from the Products dimension is also displayed for each Line Item Detail. This view could take a long time to display with a server connection, because two large dimensions are placed together on the rows. When using a local cube containing a small slice of the data, it is possible to combine dimensions in almost any configuration without causing performance problems.

4. The fourth view uses a local cube with the data for 2001. This view shows the Sales Orders in order by a calculated measure, Reseller Gross Margin. If a user chooses to sort all the Sales Orders in a server cube by a calculated measure, the data can take a long time to be displayed.

5. The fifth view contains all the attributes from all the dimensions used in the Reseller Sales measure groups, except for Reseller Sales Orders. Many of these attributes were removed from the other cubes to keep them smaller in size. Because this cube does not have the Reseller Sales Orders dimension, the user does not risk having slow browsing caused by that dimension. This view is intended for more general browsing, while the previous four views would be used when the user wants to see particular Sales Orders.

6. The last view uses a local cube showing all the customers from Australia with internet purchases of at least $5,000, but with no internet purchases in the current year. By limiting the total number of customers in the cube to 265 (instead of the normal 18,484), the user can conveniently and rapidly browse the characteristics of this important data subset.

The challenge of creating and distributing local cubes

There are two main challenges in implementing this local cube performance strategy:

  • Designing and creating the best possible set of local cube files
  • Giving those local cubes to users in a way that allows them to conveniently select the appropriate cube to answer their questions

Our company has developed a software product, CubeSlice 9, to accomplish these goals. CubeSlice 9 has the following features:

  • Creates local cubes either from the Analysis Server cubes or from the source relational database.
  • Allows users to include or exclude any particular object – measure group, measure, dimension, level, attribute, member, calculation, KPI, or action.
  • Allows users to include or exclude any particular object – measure group, measure, dimension, level, attribute, member, calculation, KPI, or action.
  • Allows the inclusion or exclusion of members based on individual choices or on a calculation.
  • Can create a set of local cube files, each sliced by a particular member
  • Displays to the user a collection of views which can be based on server and/or local cube connections.
  • Creates CubeSlice Share files, which package a collection of local cube files together with a set of views, so that users can have the best possible local cube files for their own purposes – all delivered in a single file.

One of the key features of CubeSlice 9 is the ability to create a slice of a cube based on a calculation. With calculations, you can do any of the following:

  • Include only the top 100 best performing products in the cube
  • Show only the customers who had purchases of over $100,000 in the previous year, but less than $20,000 in the current year
  • Show the data only from the most current month and the previous month, based dynamically on what data is available in the data source

CubeSlice has two components. The CubeSlice Creator is used to create local cube files and views that display cube information. The CubeSlice Viewer is used to display files created by the CubeSlice Creator. Advanced users would use the CubeSlice Creator to create new local cube files to help them browse the OLAP data most efficiently. More typical users would use sets of local cube files designed for them by someone who understands the structure of the Analysis Server cubes and the needs of the users for particular subsets of the data.

Conclusion

Local cube files are an under-used but powerful component of the Microsoft SQL Server 2005/2008 Analysis Services toolbox. There are challenges and issues that need to be addressed when using local cube files to improve browsing performance. But the opportunities in providing effective OLAP far outweigh the challenges. We encourage you to take a look at our performance demo and consider how local cubes could give a performance boost to OLAP browsing in your organization.