Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 14, 2005

Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification

By William Pearson

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.


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM