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.
Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL
Server 2000 Analysis Services, and the related Books Online
and Samples. Images are from a Windows 2003 Server
environment, upon which I have also implemented MS Office 2003, but the
steps performed in the articles, together with the views that result, will be
quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis
Services" or "MSAS"). The same is generally true,
except where differences are specifically noted, when MS Office 2000 and
above are used in the environment, in cases where MS Office components
are presented in the article.
Introduction
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.
