Tips for Optimizing SQL Server OLAP/Analysis Services -- Cube Performance

When a cube is processed, temporary disk space is required. For best performance, this disk space should be on a disk array running RAID 0 (disk striping without fault tolerance), RAID 1 (disk mirroring or duplexing), or RAID 10 (disk striping and mirroring). Since fault tolerance is not really a critical factor when processing a cube (the process can be easily restarted), you might want to consider RAID 0 for the absolutely best performance.

Be sure that any disk array you select for temporary disk files has plenty of empty space. As a cube is processed, it is not unusual for the amount of temporary disk space to exceed the size of the cube itself.

By default, temporary disk space is located on the same drive as Analysis Services is installed, but this location can be easily changed, following these steps:

From Analysis Manager (or OLAP Manager), right-click on the appropriate server and choose "Properties."

Next to the "Temporary file folder" box, click "Browse." From here, select an appropriate logical drive.

You just then stop, and then restart the Analysis Service (MSSQLServerOLAPService).

[7.0, 2000] Updated 4-19-2005

*****

OLAP and Analysis Services offers a powerful tool called the Usage-Based Optimization Wizard. This tool uses what is called the Query Log to help you optimize your cube's aggregations based on actual usage of the cubes by users. The Query Log is an actual log of the queries that are run against it. By default, one 1 out of every 10 queries is stored in the log. The Wizard uses the queries it finds in the log to provide you with recommendations on how to optimize the aggregations of your cubes.

There are two different ways that the Query Log can affect the performance of your OLAP or Analysis Services servers. First, the 1 out of every 10 queries that is logged may not be enough to successfully identify and optimize all the queries that are being run against your cubes. Instead, 1 out of every 2, or 1 out of every 5 might be a better choice for logging queries.

Fortunately, you can change this setting, anywhere from 1 out of 1, to 1 out of 10,000. To do this, follow these steps:

From Analysis Manager (or OLAP Manager), right-click on the appropriate server and choose "Properties," the click on the "Logging Tab."

By the option, "Write to long once per ___ queries," you can specify the valid range for logging (1-10,000).

As you might expect, logging queries frequently could negatively affect performance of the server. That's why it is a good idea to limit how long you collect queries in the Query Log. You might decide to collect queries for a few hours, a few days, or even for a few weeks, until you feel that a representative number of queries have been logged so that the Wizard has enough data to make a proper recommendation. But once your testing is over, you should consider turning off the Query Log, which can be done from the same dialog box as described above.

As you may have already guessed, the second way that the Query Log can affect performance is because it takes some CPU and I/O resources to maintain the log. If few queries are run, then running the log won't have much affect on performance. But if many queries are being run all the time, you should consider turning logging off most of the time, and only turning it on when you need to collect data for optimization with the Wizard. [7.0, 2000] Updated 4-19-2005

*****

Often, cube processing times can be significantly reduced by using the "Optimize Schema" option in the Tools menu. When this option is selected, what it does is to try and identify unnecessary joins between dimension and fact tables, and removes them for you automatically. By reducing unnecessary joins, cube processing time is significantly sped up.

For this option to work most efficiently, the following conditions must be in place:

The dimension table must be a shared dimension table. It must also have been processed at least once before you run the "Optimize Schema" option.

The member key column for the lowest level of the dimension table must also contain keys that relate to the fact table and the dimension table, and this must also be the only key that relates the fact table to the dimension table.

The keys in the member key column for the lowest level in the dimension table must also be unique.

The lowest level of the dimension table must be represented in the cube. While the level can be hidden, it must not be disabled.

[7.0, 2000] Updated 4-19-2005