|
|
|
|
||||||||||
| The Local Cube Information Center |
www.cubeslice.com |
||||||||||||
|
|
|||||||||||||
How to Dramatically Improve Browsing Speed Performance for Microsoft SQL Server 2005 Analysis ServicesThis article describes an unconventional strategy that can
dramatically improve browsing speed performance for Microsoft SQL Server 2005
Analysis Services. Situations where browsing performance declinesIn 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 cubesThere 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 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 |
|||||||||||||