Usage-Based Partitioning
You can partition a cube along any tuple of members at any level from any dimension. Analysis Services' Partition Wizard calls such a tuple a data slice. Although Analysis Services can scan a small partition faster than a large one, a small partition contains fewer members. Analysis Services might have to perform more scans of multiple small partitions to cover the same number of members that one larger partition could contain. So the overhead of performing calculations on the results of multiple partitions might negate the advantage of the faster scan in each smaller partition.
How you partition a cube depends on the queries you need to process. Logically, you might decide to partition along every tuple that a query specifies. For example, to improve Listing 2's performance, you might be tempted to partition along each tuple of the cross join of {Time.[1998], Time.[1999], Time.[2000]}, [Occupant_Severity].Members (6 members), and [Street].[Street_List].Members (roughly 30,000 members). You'd create partitions along a total of 540,000 tuples (3 x 6 x 30,000 = 540,000). This seemingly simple plan creates two problems. First, scanning 540,000 partitions and summing the 3 years for each tuple of severity and street (a total of 180,000 tuples) would create significant performance overhead. Second, the amount of work and time to create and process 540,000 partitions, manually or programmatically by using Decision Support Objects (DSO), is astronomical.
The excessive performance overhead you create when you partition along every tuple in a query is a serious concern for a couple of reasons. First, the query in Listing 2 isn't supposed to return each year individually. Instead, the query should return only the sum of incidents in 3 years. An efficient partition would include the three specified years so that Analysis Services could calculate the sum solely within the partition. Second, the query doesn't need to access just one street intersection; it has to scan all the street intersections regardless of the partitions you create. Being able to get to a particular street partition directly doesn't improve performance because you still have to walk through every partition. You'd be better off keeping all the street intersections in the same partition. The bottom line is that you should partition along a tuple only when the partition can save your query from doing a sequential scan for that tuple.
Partition Testing
To see what kinds of partitions avoid a sequential scan, I devised tests that use Listing 2 and Listing 3 as benchmarks. For complete details about my test environment, process, and results, see the Web-exclusive sidebar "Cube-Partitioning Tests" at http://www.sqlmag.com, InstantDoc ID 41231. In the rest of this article, I summarize the tests and some important results.
I created six cubes of identical structure with 30 percent aggregation and varying partition designs. I wanted to partition along the Time and Occupant_Severity dimension members (which Figure 2 shows) that the test queries in Listing 2 and Listing 3 are looking for so that they can get to those members with no scan or a faster scan. Table 1 describes the partitioning data slices of these six test cubes. I gave the cubes names that reflect their partitioning dimensions and total number of partitions.
To study the effect of the number and speed of CPUs, disk I/O, and physical memory on partitioned cubes, I repeated the same tests on six different Dell servers. Table 2 shows the specifications for these servers, ranging from the highest end to the lowest end in hardware resources. High1, High2, and High3 are high-end production-scale servers; Low1 and Low2 are desktops; and Low3 is a laptop (which I used as a server for the sake of testing). Each test executes Listing 2 and Listing 3 from the client machine Low2 against every test cube on all six servers.
All the tests measured the response times of Listing 2 and Listing 3. (To read the results of my tests for Listing 3, see "Cube- Partitioning Tests.") Figure 3 shows Listing 2's performance on all the servers. I drew the following conclusions for Listing 2:
- High-end servers (with multiple low-speed CPUs) performed worse than low-end servers (with one high-speed CPU) regardless of cube partitioning. CPU speed—rather than the number of CPUs, disk speed, or amount of memory—drives performance.
- Effective partitioning makes the query perform 5 to 10 times faster than on the default partition, especially on slower CPUs.
- Queries that have calculated members, such as the one in Listing 2, are CPU-bound.
- Partitioning along queried data slices, as I did in the Year_Severity31 and PartitionYear_Severity7 test cubes, gives the best performance.
- Slicing along queried members (e.g., slicing along the six members of the Severity dimension and the three members of the Year level of the Time dimension) prevented sequential scans.
- Minimizing partition sizes by excluding members that you don't query frequently (e.g., [Partition Year].[1], which includes the years 1985 through 1996) doesn't prevent a sequential scan but does speed up the scan.
- Test results (which you can read about in "Cube-Partitioning Tests") show that an aggregation level higher than 5 percent has no effect on performance, which proves my hypothesis that high aggregation levels are a waste of effort.
Guidelines for Partitioning
Based on the results of my tests and the conclusions I've drawn, I offer these partition- design guidelines. For all queries:
- Never overlap partitions.
- Never specify the [All] member as the partition data slice because doing so creates overlapping partitions.
For queries like the one in Listing 3, which accesses only pre-aggregations:
- No partitioning is necessary because its effect is negligible or negative.
- Apply Analysis Services' Usage-Based Optimization.
For queries like the one in Listing 2, which calculates many aggregations on the fly:
- Partition along queried data slices—for example, ([Partition Year].[2]
.[1997], [Fatal]).
- No Usage-Based Optimization is necessary because it has no effect.
- Five percent aggregation is the maximum aggregation level that provides performance improvements.
If you have multiple slow queries that have different partitioning needs, consider creating different cubes for each query. For desktop ad hoc users who can retrieve just one screen of results at a time, using multiple cubes might be inconvenient. However, for custom applications (such as Web and reporting applications) that need complete results, you have the full control of accessing multiple cubes or even multiple databases behind the scenes for the best performance.
The term "tuning" implies that you'll have to experiment to achieve the optimal performance for your system. The techniques and guidelines that this article offers won't necessarily create optimal performance right away, but if you take the time to examine your query usage and identify the slow queries, estimate which partitions might prevent sequential scans, and test those partitions, you'll get closer to the performance you want.
End of Article
Prev. page
1
2
[3]
next page -->
