MDX-How can I setup default dimension member in cube calculation script?
User Rating: / 8
PoorBest 
Written by Vidas Matelis   
Sunday, 08 April 2007 18:13

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]

Result:

All Geographies
$80,450,596.98

Now lets apply default member to be Canada:

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

Run query to test results:

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

Results:

Canada
$14,377,925.60

 
Comments (5)
5 Wednesday, 30 May 2012 10:59
KKR
ALTER CUBE [Page Views]
UPDATE DIMENSION [Operating System].[Operating System]
,[Operating System].[Operating System].DEFAULTMEMBER=[Operating System].[Operating System].&[0];

THIS IS NOT WORKING FOR ME IN 2005
4 Wednesday, 24 February 2010 14:08
Vidas M.
This works just with SSAS 2005/2008.
3 Wednesday, 24 February 2010 14:02
Carlos De Abreu
Is this applicable to both Analysis 2000 and Analysis 2005?
2 Tuesday, 21 April 2009 11:01
V.M.
This change is done during run time, so you will not see defaul member changed in BIDS studio. In BIDS studio you can just change default member property.
Vidas M.
1 Tuesday, 21 April 2009 08:15
Windel
This works. But then when I open the Analysis database in the Visual Studio, I don't see the changes made. Any thoughts?

Tags: faq, mdx
 

XL Cubed