Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
July 19, 2004
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.
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 cannot 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 maintain the default member manually, 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: