Questions and Answers about Using Local Cubes to Improve Browsing Speed Performance

The use of local cube files to enhance browsing performance is not a common practice. Here are answers to some questions regarding this strategy:

1. OLAP browsing focuses on summarized information. Users shouldn’t be browsing large low levels of dimensions. Why should we be concerned about the performance of that browsing?

I have worked with OLAP systems for about 8 years and I have continually heard OLAP system designers argue that the detailed level of data should not be included in the cubes. At the same time, I have always heard the users require the ability to drill down to the lowest level. I think the users are usually right. When analysts find something interesting in a cube, they want to see what details came together to make that data – and those details are sometimes at low levels that have a very large number of members.

2. Isn’t drill-through the proper technique to use with the detailed information in an OLAP system?

I don’t think drill-through is the best solution because it pulls the user away from OLAP and back into a relational data structure. If we think OLAP browsing is the way we want to present data to our users, we should try to stay with OLAP browsing even at the lowest levels. However, drill-through is certainly one option that gives users access to the detailed level of data without slowing down cube browsing speed. If you don’t want to use local cube files and can’t get adequate performance any other way, it is an option that should be considered.

3. Can we achieve a similar performance gain through conventional techniques, such as the proper use of aggregations and partitions?

Proper aggregations are very important in achieving OLAP browsing speed. (See the technical article available on Microsoft’s web site - Microsoft SQL Server 2005 Analysis Services Performance Guide by Elizabeth Vitt, February, 2007.) Aggregations, though, normally don’t help at the lowest level of a large dimension.

Partitioning can significantly help performance at a large low level. If, for example, you partition a cube along the time dimension and you are slicing on the time dimension, the Analysis Server only has to look in the partition that you are querying.

The main problem with a performance strategy based on partitioning is a lack of flexibility:

• With partitioning, you have to figure out what are the best partitions for the queries you anticipate will be used by all of your users. With a local cube strategy, you can build specific local cubes designed for particular users and particular situations.
• Partitioning is a strategy that is normally used on one and sometimes two dimensions. Local cubes can be customized in all dimensions.
• Partitioning usually uses high-level members to divide a cube. With local cubes, you can slice on high-level members, but you can also slice on a set of members created by a calculation.

4. What size should a level be before I consider using local cubes to improve performance?

The performance demo we have prepared for this presentation shows the value of reducing the size of a level with 60,000 members.

The local cube performance strategy becomes more important as the number of members in a level increases, when users want to use more sophisticated cube browsing, when cubes are browsed using older hardware, when an Analysis Server has more concurrent users, and/or when there are bottlenecks in the network connection to the Analysis Server.

5. Don’t the cubes become less valuable when you remove parts of them?

The challenge in successfully implementing this strategy is to give each user a collection of local cubes which, together, provide all the desired browsing. Of course it would be more convenient to have all members, levels, and dimensions in a single cube. The local cube performance strategy recognizes the need to make trade-offs to achieve the best possible browsing speed. It is a strategy that allows for customization for different users – so that each user can have the most valuable data at the best possible speed.

6. Won’t this local cube strategy be too complicated to implement?

There are many tools that provide some support for creating and using local cube files. We believe, though, that the options provided by CubeSlice 9 make the use of local cubes much more convenient than ever before. This is especially true when you want to limit the members of a level by a particular calculation.

7. Are there performance issues in creating a large number of local cube files?

There could be, if a lot of people were each creating their own customized local cube files. CubeSlice gives the ability to schedule local cube creation during off-peak hours, to help address this problem.

8. Are there security issues in using local cube files?

Possibly. When browsing an Analysis Server cube you can use a comprehensive role-based security system to ensure that only authorized users can see specific portions of the data. Local cube files in Analysis Services 2005/2008 can be password-encrypted, which provides a basic level of protection, but you don’t have the same security options that you have with server cubes. If you are going to use local cube files, you need to be aware of what data you are putting in them and who has access to those files.

On the other hand, there are some security advantages with using local cube files. Databases often have a combination of more private and more public data. When you use local cube files you can give the public data to users without giving them any access at all to your database.

9. How much data can I put in a local cube file and still achieve excellent performance?

Small local cube files perform better than large local cube files. The ideal size varies enormously based on the particular data being browsed. Here are a few general guidelines:

• We recommend keeping local cube files under 10MB in size and certainly under 50MB.
• We recommend keeping the maximum number of members for a level or an attribute under 10,000.
• We have seen situations where complex cube structures, such as calculated members that use measures from more than one measure group, appear to slow local cube performance.
• We recommend experimenting with different sizes of local cubes in each individual case, to determine the best balance between performance speed and the amount of data available in the cube.

10. How can I create local cube files?

There are two ways to create local cube files in Analysis Services 2005/2008. The easier way is to use a command called CREATE GLOBAL CUBE. A more complex (and more flexible) way is to use Analysis Services Scripting Language (ASSL). CubeSlice 9 supports two forms of ASSL – one that uses the Analysis Server cube as the data source and the other that uses the relational data source. The CubeSlice Relational ASSL usually offers more flexibility in creating local cube files.

For more information on the creation of local cube files, download the document Using Local Cubes with Microsoft SQL Server 2005 Analysis Services from this website.

11. How are local cubes created with Analysis Services 2005/2008 different from local cubes created with Analysis Services 2000?

There are three main differences:

• Analysis Services 2005/2008 local cubes offer more of the functionality of Analysis Server cubes.
• Analysis Services 2005/2008 local cubes can be password-encrypted.
• Analysis Services 2005/2008 local cubes are single-user. Only one connection can be made to a local cube at a time.

For full details, download the document Using Local Cubes with Microsoft SQL Server 2005 Analysis Services from this website.

12. I used local cube files in Microsoft Excel 2003, but I don’t see a place to select them in Microsoft Excel 2007. Can they be used in Excel 2007?

Yes. It’s very easy to use local cube files in Excel 2007, even though you’re not given an option of picking a local cube when creating an Analysis Services connection.

To use a local cube file in Excel 2007:

1. Select the Office Button and choose Open.
2. Change Files of Type to All Files.
3. Select the local cube file.

The local cube file will open up in the Excel 2007 pivot table.