Best Practices for Performance Tuning in SSAS Cubes

 

If you are looking for Best Practices for Performance Tuning in SSAS Cubes, you are in right place. 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 relationship to key attributes, not both.

SSAS Performance Tuning

• Optimize Cube And Measure Group Design
• Define Effective Aggregations
• Use Partitions
• Write Efficient MDX
• Use The Query Engine Cache Efficiently
• Ensure Flexible Aggregations Are Available To Answer Queries
• Tune Memory Usage
• Tune Processor Usage

1. Optimize Cube And Measure Group Design

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, all attributes). 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.

If you want to enrich your career and become a professional in SSAS, then visit Mindmajix - a global online training platform: "SSAS Online Training" This course will help you to achieve excellence in this domain.

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 processing 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 fewer 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, a 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 cause a 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.

[ Related Article: SSAS Interview Questions for Beginners ]

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 nonempty 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 subqueries where possible

For example

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

You can frequently use the descendant's function to resolve arbitrary shapes by using a smaller number of subqueries than queries than returning the same result that is 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 that 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 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 the cache by using a subselect 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 until 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 up to 3GB of memory.

Reduce the value of 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 instances 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 the 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. ScaleOut 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 server 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.

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSRS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator