Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
March 14, 2005
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: