Check List for Performance Optimisation in SQL Server Analysis Service 2005

Introduction
No matter what your platform or the tools that you are using, performance is almost always the number one feature that customers require. In the case of SQL Server Analysis Service (SSAS), there are four areas where we can improve the performance. They are database design, processing, querying and configuring SSAS.

Key Attribute
For each dimension, there should be a key attribute. If you have a choice between selecting a key attribute from an integer or varchar you should select an integer column as your key attribute. Allocating an integer column as a key attribute not only improves the query performance but also requires less storage. If you include an integer as the key attribute, then you need to have an integer column for the FACT table to link with dimension table. This will again assist to improve the query performance.

In addition, try to avoid composite keys for key attributes. In the case where you have multiple columns for key attributes, include a surrogate key and define it as the key attribute.

Data Types and Length
No matter whether it is Cube or just a database, you need to select appropriate size for the attributes. It is very common to see the assignment of lengthy data sizes for attributes such as customer addresses and name. The best option is to break down the attributes into several attributes so that you can predict the length of the attribute. For example in case of the address you may not be sure of the length of the address hence you may tend to assign an arbitrary length like varchar(300). Instead you can break down address into Address I, Address II, City, Town, Post Code and assign data lengths.

Removing Unnecessary Attributes
Even though you have hundreds of columns in your dimension table, it does not mean that you need to include all the columns as attributes in the cubes. Analyse the reports and queries and decide what data you need in the dimensions. Also, you can include a perspective and drop the unnecessary attributes. What you have to remember is that you can only create perspectives in the Enterprise and Developer editions of SQL Server 2005.

Attribute Hierarchy Optimized State
Under the Advanced option for Dimension attributes you can specify the level of optimization applied to the attribute hierarchy as illustrated below:





There are two options available named FullyOptimized and NotOptimized. When the option is set to FullyOptimized, indexes will be created against the attributes. For the attributes which are not used frequently, you can set the option of NotOptimized. By setting this option you will save time processing and resources such as disk space.

Turn off Attribute Hierarchy
For most of the cube attributes are used for analysis. However, there may be attributes which you don’t need for analysis. For example, in a customer dimension attributes like birth date, join date will not be used for analysis. Instead they will be used as just as information. For those attributes you can turn off attributes hierarchy.



As you can see there are two options available. They are True and False. If the attribute hierarchy is not enabled or AttributeHoerarchyEnabled is set to False, then the attribute cannot be used in a user-defined hierarchy.

Define Hierarchy Relationships
Most of the time an attribute within a dimension is related to another attribute by a one-to-many relationship. A typical example is customer geography. For a customer it is geography location, country ? province ? city ? post code. You can improve your cube processing and query performance by defining those relationships in the relevant dimension. The attribute relationship will help the serve build efficient indices. When a new relationship is created you should remove redundant relationships. Otherwise, these redundant attribute relationships may prevent data from being aggregated when a non-key attribute is used as a granularity attribute in a cube.

You can create the relationship by dragging and dropping it to the place named . The following image shows how you would see the customer cube, after creating the relationships.



Selecting Appropriate Process Type
When processing a cube there are three types of processing:

• ProcessFull—Erases all the data and rebuilds the dimension data and structure.
• ProcessUpdate—Implements inserts, updates, and deletes based on the type of attribute relationships in the dimension. Information on the different types of attribute relationships is included later in this article.
• ProcessAdd— Provides an optimized version of ProcessUpdate that only handle data insertions.

By selecting the correct process type you can reduce the time to process a cube.

Process Scheduled Cube Process in Command Line
Most of the time, you need to configure your cube to process at a pre-defined schedule. You have the luxury of using the SQL Server Integration Services (SSIS) package to process the cube by giving a schedule. If you are running the SSIS package at the command prompt it will be much faster. You can execute the SSIS package by following command.

DTEXEC /f

Partitioning
SSAS uses partitions to contain cube data. Small cubes might use one part, while for medium and large scale cubes may be split into groups of data which is called partitioning.

As a practice one partition should have 2GB or 15 million rows for effective performance. Unnecessary partitioning may degrade you cube performance instead of improving it. Also too many partitions makes it difficult to manage.

A point to note about Partitioning in SSAS is that this feature is available only with enterprise and developer editions of SQL Server.

Configuring SSAS
You have the ability to install SSAS on a server which has multi-processors. Doing so allows several partitions to be updated in parallel.

Ideally you can configure SSAS and the database services on two separate servers. In the scenario where only one server is available there will be competition for resources which you can be eliminated by using two servers. However, you need to make sure you have good network connectivity between the two servers. Otherwise any performance gains will be reduced. Something to consider before using two servers is the possible licensing implications.

Make sure that you install the latest SQL Server 2005 Service packs and the appropriate patches. For example, SP2 of SQL Server 2005 has fix for the following bu, SQL Server Analysis Services 2005 system performance may be slow after you use the ADOMD.NET data provider to run an MDX query that returns a large result set from a cube (KB 919731).

Summary
The following is the summary of the actions and the effect of that action. You can see that what the benefits that you will achive after performing each action.

Actions	Cube Processing	Query Performance	Disk Resources
Data Length and Sizes	x	x	x
Key Attributes	x	x
Removing Unnecessary Attributes	x		x
Set Attribute Hierarchy Optimized State to FullyOptimized	x		x
Turn off Attribute Hierarchy	x		x
Define Hierarchy Relationships between attributes	x	x
Partitioning		x
Selecting Appropriate Process Type	x
Cube Process at Command Line	x
Configuring SSAS	x	x