In this article, we will explore another tool that MSAS offers for the enhancement of cube processing, the Optimize Schema option. Optimize Schema attempts to identify unnecessary joins between our fact and dimension tables, and then to remove them. In many cases, the tool works effectively to accomplish this, leading to a significant reduction in a cube’s processing time.  Elimination of the joins means more rapid resolution of MSAS’ queries to the relational database, upon which our cube is dependent as a data source.  This, in turn, means that data is pulled into Analysis Services in less time, contributing to a more rapid cube build overall.

The operation of the Optimize Schema option takes advantage of a common circumstance within the construction of many star or snowflake schemas:  the foreign key that serves as the basis of a join between the fact table and a given dimension table is identical to the member key itself. When this is the case, MSAS can eliminate the join, and source the member key directly from the fact table, instead of relying upon a join to the dimension table to obtain the key.

In this article, we will examine the use of Optimize Schema in making our cubes process faster. To accomplish this objective, we will:

• Overview the Optimize Schema option;
• Create a copy of the Warehouse sample cube for use in our practice session;
• Prepare the cube copy further by processing;
• Discuss the Member Key Column property, and examine existing settings within our practice cube;
• Discuss possible considerations in determining the appropriateness of the use of the Optimize Schema option in our respective business environments;
• Perform a practice exercise within which we employ the Optimize Schema option;
• Examine some of the effects of using Optimize Schema.

Read the Article ...

About the Series:

This article is a member of the series Introduction to MSSQL Server 2000 Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services, with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.
Optimize, Schema, Performance, Member, Key, Column, Join, Cube, Editor, Dimension, Analysis, Services, MSAS, Property, Query, Processing, MDX, Cube, design, Calculated, Member, Property, Query, processing, Performance, Editor, MDX, MSSQL, Server, Analysis, Services, optimization

All Contents Copyright Island Technologies Inc. ^®  Island Technologies^® and Island Technologies Inc.^®  are registered trademarks of Island Technologies Inc., protected in the United States and other countries.  For information, contact Island.