OLAP Performance

Beat your users' performance expectations

Many system performance factors—including those for OLAP Services—involve tradeoffs. If you tweak an operation to make it perform faster, you might pay for the speed with performance degradation elsewhere. With OLAP Services, performance tradeoffs occur between cube build time and query time. As I discuss various actions that can influence performance, such as setting up storage modes, setting the aggregation level, querying, partitioning, and using PivotTable Services, I'll point out how these actions affect cube build time and query time.

Storage Modes

When building a cube, you first need to choose a storage mode. The storage mode determines how the data is organized in disk storage. The storage mode you select affects the disk-storage space requirements and the data-retrieval performance. Your choices are Multidimensional OLAP (MOLAP), Relational OLAP (ROLAP), and Hybrid OLAP (HOLAP). MOLAP stores fact data and aggregations on the OLAP server in a space-efficient, highly indexed, multidimensional form. ROLAP creates relational tables to store the aggregations in a data warehouse and calls the warehouse database when it needs data from the fact or aggregation tables to respond to user queries. HOLAP is a hybrid of MOLAP and ROLAP; it stores aggregations in multidimensional format on the OLAP server, but it calls the warehouse database when it needs fact table information. I highly recommend that you choose MOLAP for most of your applications because it's significantly faster than ROLAP and HOLAP for query response and faster than ROLAP for building the cubes. However, you won't want to use MOLAP in some cases, such as when you want to support write-back capability to the OLAP cube. Write-back capability lets the user change the information in an OLAP cube to perform forecasting or what-if scenarios. If you enable a cube partition for write-back, the write-back values are automatically stored in a relational table in the warehouse database. Storing the values this way in a relational table affects only the write-back data. The other cube partitions are still stored in the storage modes that you chose when you built the cube.

Using realtime OLAP is the second scenario where you won't want to use MOLAP. If you want to configure OLAP Services to support realtime OLAP, build the cube based on a ROLAP partition with no aggregations. Other applications can then modify the underlying ROLAP data while OLAP Services queries the cube. The OLAP user can run an MDX query, then continually refresh the query results on the screen to see the data change. When I first saw a demonstration of realtime OLAP, I thought of Home Shopping Network. I imagined their telesales people entering individual sales transactions into one application, and updating the ROLAP partition of a sales cube, while an executive sat in the back room watching the sales in realtime from his OLAP application. By having this information, the executive could make a split-second decision to pull the football phone off the television and replace it with the highly sought travel coffee maker.

Aggregation Level

You can also improve OLAP performance when you set the cube's aggregation level. When you build a cube, you set the aggregation level according to the desired speedup in processing queries. (Speedup describes how much faster queries run with precreated aggregations than without aggregations.) The system estimates the speedup based on the I/O amount that the system requires to respond to queries. The total possible number of aggregations is the product of the number of members from each dimension. For example, if a cube has two dimensions and each dimension has three members, then the total possible number of aggregations is 9 (3 x 3). In a typical cube, the number of aggregations possible is extremely large, so calculating all of them in advance isn't desirable because of the required storage space and the time it takes to create the aggregations. Imagine a cube with four dimensions, each with 10,000 members. The total possible number of aggregations is 10¹⁶. When you tell OLAP Services to calculate aggregations for a 20 percent speedup, OLAP Services picks key aggregations (which are distributed across the cube) to minimize the time required to determine any other aggregations at query time.

To maximize query performance, you might logically conclude that you should aggregate as much as possible to try for a 100 percent speedup. However, this approach typically results in a poor trade-off between cube build time and query performance. A point of marginal return occurs when you precalculate aggregations. OLAP Services can calculate aggregations quickly at query time and the server can cache the aggregations. Microsoft recommends that you use usage-based aggregations when you determine which aggregations you need most based on the end user's queries. Usage-based aggregations start aggregating the cube to about 25 percent speedup. Then you adjust the query logging on the server, so your users can have access to the cube. After you have a good sample of how your users handle the cube's data, OLAP Manager can customize the aggregations based on usage.

You can adjust the query logging by running the OLAP Manager and right-clicking the server name in the tree-view panel. Click Properties and select the Query Log tab page. The default query log setting logs every 10th query. For usage-based optimizations, you'll probably want to log every query so you don't miss usage patterns. Change this setting to 1, and press the Clear Entire Log button to empty the log. (Note that because of the performance cost of logging every query, you won't want to leave the settings this way all the time.) After you use your OLAP applications for a few days, run OLAP Manager again. This time, right-click a cube name that you want to optimize, and click Usage-Based Optimization to start the Usage-Based Optimization wizard. The wizard's second page lets you establish flexible criteria for the queries you want to optimize. You can filter the queries by date, duration, frequency, owner, and destination type, as Screen 1 shows.

After you establish your query-filtering criteria, you can click Next to see a list of selected queries, as Screen 2 shows. Finally, click Next to add or replace the existing aggregations. Microsoft usually recommends adding existing aggregations. After making this selection, the wizard leads you through the same steps as the Storage Design Wizard.

Partitions

If you have the enterprise version of SQL Server, you can create multiple partitions for an OLAP cube. A cube partition is a separately managed unit of storage for the cube. Each partition can have its own aggregation level and storage mode. One common use of partitions is the incremental update feature. Because you aggregate each partition separately, you can minimize the time to do an incremental load by creating a new partition that includes only the newly loaded information.

Aggregating each partition separately is an option you can choose in addition to using the Incremental Update feature. This option is useful if you define the cube slice for each partition. Setting the slice is vital for achieving the best query performance, because the slice information tells the server which partitions have which data. For example, a cube has 10 years of sales data. Ninety percent of the analysis uses the sales data from the most recent 12 months. To optimize this cube's performance, you might want to break it down into two partitions, one with the most recent 12 months of data and another with the older data. Then you can define the recent partition to have 25 percent aggregations optimized by usage, and the older partition can have only 10 percent. You can control the slice (in this case, for the most recent 12 months) by right-clicking a cube partition in the tree view of the OLAP Manager and selecting Edit. This starts the partition wizard. Click Next to go to the second page. Then select the dimension on the left and the dimension members from the tree view on the right that define the slice of the cube you want to store in this partition, as Screen 3 shows. Changing the aggregation level on a per-partition basis lets you focus your performance tuning on the portion of the cube where you see the most benefit.

Many people don't know that a partition is the unit of granularity for threading during a cube load. SQL Server 7.0's OLAP Manager doesn't expose this functionality, but if you wrote your own server application to build a cube by using Decision Support Objects (DSO), you could spawn a separate thread to load each partition of the cube. A multiprocessor server can make a huge difference in how quickly a cube load completes. OLAP Services' DSO is analogous to SQL Server's DMO; it's the object model that accesses OLAP Services. Microsoft used DSO to develop the OLAP Manager.

Cube loading isn't the only scenario in which OLAP Services exploits a multiprocessor system. While executing a query, the OLAP Server spawns a separate thread for each segment of the cube. Each of these threads can run simultaneously on a multiprocessor system. A segment isn't the same as a partition. I'm referring to the segments in messages you might see the OLAP Manager display while it processes a cube, such as Writing to segment 1 or Writing to segment 2. A segment holds 64KB leaf-level aggregations in a cube. A leaf-level aggregation is a unique combination of leaf-level dimension members (one from each dimension). When OLAP Services processes fact-table records to load a cube, it adds each record into a leaf-level aggregation. Rarely does every leaf-level aggregation have an associated fact-table record, so OLAP Services stores only the leaf-level aggregations that have values. OLAP Services stores the non-empty aggregations in segments. If the cube has two partitions, each with two segments, then an MDX query can spawn up to four threads.

PivotTable Services

Now that I've discussed some aspects of the server portion of OLAP Services, let's look at the client, PivotTable Services. The way in which PivotTable Services uses memory affects performance. PivotTable Services uses memory to keep dimension member information and to cache cell values from query results. Allocating enough cache memory is the most important step you can take to increase client performance. Client Cache Size, the connection property, controls cache memory.

Understanding the order in which PivotTable Services executes query elements is helpful for predicting client memory requirements and query performance. After the MDX expression determines which dimension members appear on the axes (rows, columns, etc.), PivotTable Services returns to the client application. PivotTable Services waits until the client application requests a group of cell values to determine the values. If the query contains calculated members, PivotTable Services evaluates the calculated members on the client when requesting the cell values. Here's the performance boost: PivotTable Services tries to minimize the number of trips to the server for cell values by predicting which values the client application will request. For example, if the client application requests the first row of cell values, PivotTable Services can request that the OLAP Server also return the values for the neighboring rows.

The way that PivotTable Services manages dimension member information also affects OLAP query performance. When you establish a connection, PivotTable Services downloads all the members into the cube's smaller dimension levels. If any dimension levels contain more members than a number you specify, PivotTable Services retrieves these larger-level dimension members only as needed at query time. The default setting for this threshold is 1000, but you can change it when the client connection is established with a connection string parameter. You'll want to change the threshold setting if you know that users will be doing many queries at low levels of the dimension hierarchies. If your developers used multidimensional ADO (ADO MD) to write the OLAP client application, you can set the parameter with the connection string to

Large Level Threshold=5000

Whether you're building cubes on the server, building OLAP applications on a client, or designing MDX queries, performance is always an issue. Take advantage of these suggestions, and beat your users' performance expectations.

I give special thanks to Len Wyatt and Marin Bezic, members of Microsoft's SQL Server development team, for their help with the material for this article.