Best Practices for Performance Tuning in SSAS Cubes

SSAS Performance Tuning

1) Optimize Cube And Measure Group Design

  • Define cascading attribute relationships for example day > Month > Quarter > year  and define user hierarchies of related attributes (called natural hierarchies) within each dimension as Appropriate for your data
  • Remove redundant relationships between attributes to assist the query execution engine in generating the appropriate query plan. Attributes need to have either a direct or an indirect relationships to key attribute, not both.
  • Keep cube space as small as possible by only including measure groups that are needed
  • Place measures that are queried together in the same measure group. A query that retrieves measures from multiple measure groups requires multiple storage engine operations
  • Minimize the use of large parent – child hierarchies .
  • In parent child hierarchies, aggregations are created only for the key attribute and the top attribute (for example, the all attribute). Unless it is disabled.
  • Optimize many-to-many dimension performance, if used. When you query the data measure group by the many dimension, a run-time “join” is performed between the data measure group and the intermediate measure group using the granularity  attributes of each dimension that the measure groups have in common.

2) Define Effective Aggregations

  • Define aggregations to reduce the number of records that the storage engine needs to scan from disk to satisfy a query.
  • Avoid designing an excessive number of aggregations. Excessive aggregations reduce processesing performance and may reduce query performance
  • Enable the analysis services query log to capture user query patterns and use this query log when designing aggregations

3) Use Partitions

  • Define partitions to enable analysis services to query less data to resolve a query when it cannot be resolved from the data cache or from aggregations. Also define the partitions to increase parallelism when resolving queries
  • For optimum performance, partition data in a manner that matches common queens. A very common choice for partitions is to select an element of time such as day, month, quarter, year or some combination of time elements in most cases, partitions should contain fewer than 20 million record size and each measure group should contain fewer than 2,00 total partitions. Also avoid defining partitions containing fewer than two million records.
  • Too many partitions causes as slowdown in metadata operations, and too few partitions can result in missed opportunities for parallelism
  • Define a separate ROLAP partition for REAL-time ROLAP partition in its own measure group.

4) Write Efficient MDX

  • Remove empty tuples from your result set to reduce the time spent by the query execution engine serializing the result set.
  • Avoid Run-time checks in an MDX calculation that result in a slow execution path. If you use the case and If functions to perform condition checks which must be resolved many times during query resolution, you will have a slow execution path.

Rewrite these queries using the SCOPE function to quickly reduce the calculation space to which on MDX calculation refers.

  • Use Non-Empty-Behavior where possible to enable the query execution engine to we bulk evaluation mode. However, if you use Non-Empty-Behavior incorrectly, you will return incorrect results.
  • Use EXISTS rather than filtering on member properties to avoid a slow execution path. Use the non empty and exists functions to enable the query execution engine to use bulk evaluation mode.                      
  • Perform string manipulations within analysis services stored procedures using server-side ADOMD.NET rather than with string manipulation functions such as str to member and str to set.
  • Rather than using the lookup cube function, use multiple measure groups in the same cube wherever possible.
  • Rewrite MDX queries containing arbitrary shapes to reduce excessive sub queries where possible

For example
The set {(Gender.male, customer.USA), (Gender.Female, Customer.Canada)} is an arbitrary Set.

You can frequently use the descendants function to resolve arbitrary shapes by using a smaller number of sub queries than queries than return the same result that are written using other functions.

  • Rewrite MDX queries that result in excessive perfecting where possible. Perfecting is a term used to describe cases where the query execution engine requests more information from the storage engine than is required to resolve the query at hand for reasons of perceived efficiency.


5) Use The Query Engine Cache Efficiently

  • Ensure that the analysis services computer has sufficient memory to store query results in memory for re-use in resolving subsequent queries.
  • Define calculations in the MDX script calculations in the MDX script have a global scope that enables the cache related to these queries to be shared across sessions for the same set of security permissions.
  • Rewrite MDX queries containing arbitrary shapes to optimize caching.

For Example
In some cases you can rewrite queries such that they can be resolved entirely from cache by using a sub select in the From clause rather than a WHERE clause. In other cases, a WHERE clause may be a better choice.

6) Ensure Flexible Aggregations Are Available To Answer Queries

  • Note that incrementally updating a dimension using Process update on a dimension drops all flexible aggregations affected by updates and deletes and by default, does not re-create them unitl the next full process
  • Ensure that aggregations are re-created by processing affected objects, configuring lazy processing, performing Process Indexes on affected partitions, or performing full processing on affected partitions.

7) Tune Memory Usage

  • Increase the size of the paging files on the analysis services server or add additional memory to prevent out-o memory errors when the amount of virtual memory allocated exceeds the amount of physical memory on the analysis services server.
  • Use Microsoft windows advanced server® or Datacenter server with SQL server 2005 enterprise edition (or SQL server 2005 Developer Edition) when you are using SQL server 2005 (32-bit) to enable Analysis services to address upto 3GB of memory.
  • Reduce the value for the memory/Low Memory limit property below 75 percent when running multiple instances of analysis services or when running other applications on the same computer.
  • Reduce the value for the Memory/Total Memory limit property below 80 percent when running multiple instance of analysis services or when running other applications on the same computer.
  • Keep a gap between the memory / low memory Limit property and the memory /total memory limit property – 20 percent is frequently used.
  • When query thrashing is detected in a multi user environment, contact Microsoft support for assistance in modifying the memory heap type.
  • When running on non-uniform memory access (NUMA) architecture and virtual AIIOC takes a very long time to return or appears to stop responding, upgrade to SQL server 2005 SP2 and contact Microsoft support for assistance with appropriate settings for pre-allocating NUMA memory.

8) Tune Processor Usage

  • To increase parallelism during querying for servers with multiple processors, consider modifying the thread pool query Mastheads and Thread pool process max thread options to be a number that depends on number of server processors.
  • A general recommendation is to set the thread poolQuery Mastheads to a value of less than or equal to two times the number of processors on the server.

For Example
If you have an eight – processor server, the general guideline is to set this value to no more than 16.

A general recommendation is to set the thread poolprocessmastheads options to a value or less than or equal to 10 times the number of processors on the server.

  • This property controls the number of threads used by the storage engine during querying operations as well as during processing operations.

For example
If you have an eight – processor server, the general guideline is setting this value to no more than 80

 9) Scale Up Where Possible

  • Use a 64-bit architecture for all large systems.
  • Add memory and processor resources and upgrade the disk I/O subsystem, to alleviate query performance bottlenecks on a single system.
  • Avoid linking dimensions or measure groups across server and avoid remote partitions whenever possible because these solutions do not perform optimally

10) Scale Out When You Can No-Longer Scale Up

  • If your performance bottleneck is process or utilization on a single system as a result of a multi-user query workload, you can increase query performance by using a cluster of analysis services servers to service query requests can be load balanced across two analysis services servers, or across a larger number of analysis services servers to support a large number of concurrent users (this is called a server farm). Load balancing clusters generally scale linearly.
  • When using a cluster of analysis services servers to increase query performance, perform processing on a single processing sever and then synchronize the processing and the query servers using the XMLA synchronize statement, copy the database directory using robo copy or some other file copy utility, or use the high speed copy a facility of SAN storage solutions.

Enroll for Live Instructor Led ONLINE MSBI TRAINING


Get Updates on Tech posts, Interview & Certification questions and training schedules