SQL Server 2008 - Worth the Wait
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: Next Page>>
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset