MSAS: Optimization Tuning Part 1

Page 1 of 3

Author : Exforsys Inc. Published on: 14th Apr 2005

SQL Server 2000 Analysis services comes with certain features that optimize performance without the intervention of the Administrator. The Storage Engine is optimized by incorporating the following features;

Record size and Data types should be kept as short as possible in the Fact tables and should only include fields for measures and indexed key columns. The Measure fields should use the smallest data type consistent with the measure data but the data type should be large enough to contain summarized values and prevent overflow when aggregations are calculated. Even saving two bytes per row can result in smaller fact tables in the database and this will go a long way in enhancing performance.

Feature	Description
Data Type sizes	Size limitation of data types were increased dramatically.
Databases and files	Database creation is simple as the databases reside on the operating system files instead of on logical devices. The databases are mapped to their own set of files and pages.
Dynamic memory	Memory allocation and usage is optimal and the simplified design minimized contention with other resource managers
Dynamic row level locking	Both data rows and index entries have full row level locking. The Dynamic locking chooses the optimal level of lock for all database operations automatically. The improved concurrency feature requires no tuning and the database supports hints for forcing a particular level of locking
Dynamic space management	Automatic grow and shrink is allowed within configurable limits. This minimizes the need for the data base administrator intervention. There is no need to pre-allocate space and manage data structures.
Evolution	This is an architecture designed for extensibility with a foundation for object relational featues.
Large memory support	Memory addressing is supported for greater than 4 gigabytes in conjunction with Windows NT Server 5.0, alpha processor based systems and other techniques.
Log manager	The design of the log manager helps improver performance for truncation, online backup and recovery operations.
Read Ahead	The read ahead logic improves performance and removes manual tuning
Text and image	Text and image data are stored separately and optimally
Unicode	Native Unicode is implemented, with Open Database Connectivity (ODBC) and OLE DB Unicode application programming interfaces are improved with multilingual support.

Ads

Apart from the above, SQL Server 2000 Analysis services also comes with inbuilt Server Error Logs which logs in all the errors that occur. It is important to frequently review these logs and fine tune the server performance by identifying frequent and troublesome errors.

Optimizing the Data Warehouse Database:

Analysis services creates multidimensional presentations of the Data warehouse data. It reads and organizes the data into multidimensional objects such as dimensions and cubes. It uses the relational database to access the data warehouse database when creating and processing dimensions and cubes. Therefore the schema design and the relational database performance have a significant impact on the performance of the cubes and objects in the data warehouse database.

Record size and Data types should be kept as short as possible in the Fact tables and should only include fields for measures and indexed key columns. The Measure fields should use the smallest data type consistent with the measure data but the data type should be large enough to contain summarized values and prevent overflow when aggregations are calculated. Even saving two bytes per row can result in smaller fact tables in the database and this will go a long way in enhancing performance.

Data warehouse update strategy should be designed to take care of changes and reduce the need for frequent reprocessing of cubes.

Optimizing Cube schemas is extremely useful in cube processing. This can be done by selecting the Optimize schema command on the tools menu of the Cube Editor. Analysis services then modifies the schema to eliminate joins between the fact table and dimension tables wherever possible. The conditions appurtenant thereto are that the dimensions must be shared dimensions and must have been processed. The member key column for the lowest level of the dimension must contain keys that relate to the fact table and the dimension table. The keys in the member key column for the lowest level of the dimension must be unique and the lowest level of the dimension must be represented in the cube—the levels disabled property must be set to NO and the level must be hidden.

Ads

The cube schemas are optimized ignoring the dimension tables in the database and it reads only the fact table. Processing time is reduced if cubes are optimized and optimization is applied to all partitions of a cube. Cube schemas should not be optimized if the cube depends on inner joins between the fact table and the dimension tables for excluding fact rows for cube content. Since schema optimization removes joins, all dimension tables will not be displayed while specifying drill through options. The tables will have to be joined to the schema by using SQL statements.

During Cube schema optimization the member key column is treated as the lowest level of a dimension. If the dimension meets the required conditions, the member key column property for the lowest level of the dimension is changed to refer to the foreign key in the fact table instead of a key in the dimension table.

The cube schema optimization can be modified by the user. One or more dimensions can be removed from the optimization by changing the lowest level member key column property to refer to the original column in the dimension table.

Calculated measures are stored in cubes. When cubes have multiple calculated measures setting the right solve order will improve performance and accuracy.

Partitions are very valuable when used in large cubes. Each partition can be separately processed. It should be noted however that partitions may make the cube very complex and become a liability in certain implementations. They may also set artificial limitations to data design and queries.

Linked cubes that run across multiple servers are useful, but the processing complexity and difficulties in implementation almost preclude their usage.