When selecting indexes for your data warehousing databases, don’t forget that if you decide to use your data warehousing databases for creating OLAP cubes, that the indexes you select affect the creation and processing time of the cubes themselves. Generally, you should consider adding indexes for each fact table and dimension table, and declaring foreign key relationships between these tables. In addition, consider creating a composite index on all foreign keys in the fact table and creating indexes for every foreign key in the dimension tables. [7.0, 2000, 2005] Updated 4-4-2005
*****
In order of best performance, the OLAP cube storage types include: MOLAP, HOLAP, and ROLAP. In most cases, the performance differences between MOLAP and HOLAP cubes won’t be huge, but ROLAP will always offer the worst performance. You may need to run tests to determine if MOLAP or HOLAP is fastest for your data set.
On the other hand, MOLAP takes up the largest amount of physical space on your storage devices, while HOLAP and ROLAP use close to the same, with HOLAP most likely using the least amount of disk space. [7.0, 2000, 2005] Updated 4-4-2005
*****
When selecting the level of aggregation for your cubes, consider values between 25% and 60%. Generally, values closer to 60% will produce the fastest query results, while at the same time not creating a cube so large that it takes up unnecessary space or takes too long to create. Aggregations over 60% usually take huge amounts of disk space and take a long time to create, and in most cases they won’t speed up query performance noticeably. [7.0, 2000] Updated 4-4-2005
*****
For best performance, the SQL Server with the data warehouse or datamart, should be on a separate computer than the server running OLTP services. [7.0, 2000, 2005] Updated 4-4-2005
*****
If your OLAP cubes are large or very busy, you may want to consider putting the cubes on separate dedicated servers in order to distribute the load. If one particular cube is very busy, duplicate the cube on two or more servers, and then distribute the workload among all the servers. [7.0, 2000, 2005] Updated 4-4-2005
*****
Schedule cube updates on your production servers during less busy periods of the day to reduce the impact on your users. [7.0, 2000, 2005] Updated 4-4-2005
*****
The amount of memory used by the OLAP Server is configurable by going to the server’s “Properties” page and selecting the “Environment” tab. The “Minimum allocated memory” setting defaults to half your current RAM. The “Memory conservation threshold” defaults to the total amount of RAM in the server.
If your server is dedicated, which it should be, consider bumping up the “Minimum allocated memory” setting to about 90% of your server’s total RAM. That way, the OLAP Server will know right away it can have it, rather than figuring out on its own that it is available for its use. [7.0, 2000] Updated 4-4-2005
*****
The number of threads allocated to the OLAP Service is configurable by going to the server’s “Properties” page and selecting the “Environment” tab. The “Maximum number of threads” defaults to two times the number of CPUs in your server. If you have four CPUs, the value will be eight. The maximum value for this setting is 1000.
Check to see how busy your CPUs are by checking the System Object: % Total Processor Time and the System Object: Processor Queue Length. If these values are not indicating a CPU bottleneck, then consider bumping up the number of threads. The more threads there are, the greater the potential performance for the server.
If you decide to make this change, do so scientifically, using the Performance Monitor before and after each change to see if your changes have created a bottleneck. Keep increasing the number of threads until your server no longer shows any improvement in performance, but stop if your server shows signs of a CPU bottleneck. [7.0, 2000] Updated 4-19-2005
*****
If your dedicated OLAP server has lots of RAM, you might want to consider increasing the “Read-ahead buffer size,” which is configurable by going to the server’s “Properties” page and selecting the “Processing” tab. The default value is 4MB. This setting is used to hold data that the OLAP server thinks it might need in the near future. The larger this buffer, the greater the likelihood of success.
As with any changes to a server configuration setting, be sure to scientifically test your changes to see if there is any improvement, or any slow-down, as a result of the changes. You might want to try increasing this setting 2MB at a time, then test. Repeat until the performance no longer improves or gets worse. [7.0, 2000] Updated 4-19-2005
*****
If your dedicated OLAP server has lots of RAM, you might want to consider increasing the “Process buffer size,” which is configurable by going to the server’s “Properties” page and selecting the “Processing” tab. The default value is 4MB. This is the RAM used by the OLAP server for processing work.
As with any changes to a server configuration setting, be sure to scientifically test your changes to see if there is any improvement, or any slow-down, as a result of the changes. You might want to try increasing this setting 4MB at a time, then test. Repeat until the performance no longer improves, or gets worse. [7.0, 2000] Updated 4-19-2005
*****
When designing OLAP cubes, don’t include measures or dimensions that your users won’t use. The way to prevent this is good systems analysis and design. Unused data will increase the size of your cubes and slow performance. [7.0, 2000, 2005] Updated 4-19-2005
*****
When using the Storage Design Wizard, you don’t have to take the recommendation provided. You can experiment with different tradeoffs between storage size and increased performance. If you have the hard disk space, and updating your cubes does not present a time problem, consider increasing the cube’s performance by increasing the number of aggregations stored in the cube. [7.0, 2000] Updated 4-19-2005
*****
Once your OLAP cubes are in production, use the Usage Analysis Wizard to analyze the queries performed on the cubes, and use the Usage-Based Optimization Wizard to tune your cubes for maximum performance. [7.0, 2000] Updated 4-19-2005
*****
If you have the Enterprise version of SQL Server, consider dividing your OLAP cubes into partitions to boost performance. A partition is a separately managed unit of storage, and each partition can have its own storage mode and aggregation level. Partitions provide several performance benefits, including:
You can place cube partitions on different hard disk arrays in order to better distribute the workload across multiple I/O devices.
You can divide cubes into partitions that represent how the data in the cube is used. For example, say you have five years of data available, but that 80% of the queries are against the most recent year, and 20% are against the other four years. Put the most recent year in its own partition, and the remaining four years in a second partition. This way, you can select different aggregations for each partition, which will affect both performance and the size of the cube.
A partition is the unit of granularity for threading during a cube load. This means, that on multiple CPU servers, adding partitions speeds up cube load processing time.
[7.0, 2000] Updated 4-19-2005