As most of us that work with MSAS are aware, dimensions as defined in Analysis Services contain many properties, one of which is the default member. The default member can have far-reaching effects from the perspective of information consumers, because they are often not even aware of the property, usually can’t control it, and would not likely relish controlling it even if they could. Yet the default member affects the results of their reporting and analysis ambitions because it “fills in the blanks” with regard to the setting of any unspecified dimensions in the MDX queries that they generate, be it from enterprise reporting systems, the Pivot Table Service (for example, within the new Excel OLAP add-in, which I plan to discuss in a subsequent article), or through any other vehicle they use to query an MSAS cube.
 

The WHERE clause that lies within every MDX query (whether it is explicit or not) is the fulcrum of the default member in reporting and analysis. In its job of describing slicer dimensions, the WHERE clause is “subsidized” by MSAS itself, because MSAS supplements any dimensions that the WHERE clause leaves unspecified, and which do not appear in an axis assignment, with the default member it derives from the dimension properties settings inside the cube structure. All dimensions are thus accounted for, allowing for precision in the data retrieval from the OLAP cube.
The default member, by default, is typically the All level for a given dimension, unless the “default” empty state of the property is changed. If the property is empty and there is no All level in place for the dimension, the default member is an assigned member inside the highest level of the dimension.

Regardless of the setting of the default member property, its use in the WHERE clause of MDX queries is not often clear to information consumers, many of which understand little more about MultiDimensional eXpressions than how to spell “MDX.” I constantly encounter cases where those who query cubes do not realize that all dimensions are specified, whether they name them or not, and that the impact of MSAS’ “assumptions” can affect the outcome of the results they obtain in their querying efforts.

This becomes particularly noticeable with the Time dimension(s), which, as in the sample cubes that accompany MSAS, is typically without an All level in its structure(s). The effects of the default member within the Time dimension are likely to have to be managed in most business environments. While we can certainly manually maintain the default member from an administrative perspective, this only adds overhead to our already resource-challenged environments.

In this article, we will explore one approach to enhancing the behavior of the default member. We will:

• Determine the existing default member setting for the dimension;
• Provide a simple solution through customization of the default member for a Time dimension;
• Provide a more elaborate solution to provide a completely dynamic default member;
• Explain the results we obtain from the MDX that we use to accomplish each solution.

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.
MSAS, MSSQL, SQL, Server, Analysis, Services, Default, Member, DefaultMember, Dynamic, Tail, Filter, Item, Cube, MDX, Tail(), Filter(), Item(), .Item
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.