04 September 2008

MDX-How can I setup default dimension member in cube calculation script?

Q: How can I setup default dimension member in Calculation script?

A: You can use ALTER CUBE statement. Syntax:

ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION [dimension name], DEFAULT_MEMBER='[default member]';

Example:
At first lets test what is default value

SELECT [Geography].[Geography].DefaultMember ON 0 FROM [Adventure Works]

Now lets apply default member to be Canada:

ALTER CUBE [Adventure Works] UPDATE DIMENSION [Geography].[Geography]
, DEFAULT_MEMBER = [Geography].[Geography].[Country].&[Canada];