How to set a default member of a dimension to a calculated member in SQL Server 2005 Analysis Services (915856)

INTRODUCTION

Consider the following scenario. In Microsoft SQL Server 2005 Business Intelligence Development Studio, you set the default member for a dimension to a calculated member, and then you deploy the cube. In this scenario, you receive an error message that is similar to the following:

DefaultMember: The member '[<<CalculatedMemberName>>]' was not found in the cube when the string, [<<DimensionName>>].[<<CalculatedMemberName>>], was parsed.

Note This behavior is by design.

You can set the default member of a dimension to a calculated member. However, when you set the default member of a dimension to a calculated member, you must use a slightly different approach than the approach that is used in SQL Server 2000 Analysis Services. This article describes the approach that you must use and explains why the behavior is different in SQL Server 2005 Analysis Services.

MORE INFORMATION

This behavior is by design. When you specify the default member in the user interface (UI), the default member sets the DefaultMember attribute property or the DefaultMeasure cube property in data definition language (DDL). The DDL is applied before the MDX script that is associated with the object is executed. Therefore, when the DDL tries to set the default member, the calculated member has not been created by the MDX script.

When you set the default members in the Analysis Manager UI in SQL Server 2000 Analysis Services, a new ALTER CUBE command is generated. The ALTER CUBE command is then added to the cube commands collection. The cube command that is executed after the calculated member is defined prevents the error. The same behavior can occur in SQL Server 2005 Analysis Services. However, the MDX script for the cube is used instead of the cube commands collection. For example, you can include the following command at the end of the MDX script for a cube that sets the default member to the Profit calculated member of the measures dimension.

ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER=[Measures].[Profit]

Note The placement of the ALTER CUBE statement may affect the result of other statements in the MDX script. For example, named sets and SCOPE subcubes are evaluated by using the current context of the cube. This typically involves default members. The results of these statements may change if the default member has been set to a calculated member before their execution compared to just using the system determined default member.

One disadvantage of using the ALTER CUBE statement in the MDX script is that the statement must be added to the MDX script of every cube where the default member must be set.