Default Measures

Today I started seeing some weird results when I was using NonEmpty function. If I just used NonEmpty without a filtering measure like
   Nonempty([Time].[Time].[Time])
 
then I wasn’t getting any rows returned. But if I put a filtering measure in there like
 
Nonempty([Time].[Time].[Time],[Measures].[MyMeasure]) then I was getting data back. It occured to me that I had not even thought about what my default measure was. So I ran the following query
 
Select measures.defaultmember on 0
from mycube
 
To my surprise it was a measure that would not really be applicable to most of my calculations. I was a little perplexed at why SSAS chose that measure as my default, so after some "Binging", I found this excerpt in one of Mosha’s old SSAS site pages
 
"For the default measure, the first non-hidden measure is chosen. If all measures are hidden, then the first measure is chosen. “First” here refers to the first measure in the measures collection in AMO and DDL (but not in MDSCHEMA_MEASURES, which is always alphabetically sorted). "
 
So I promptly changed the default measure to something that would have more meaning to the calculations I was writing.
 
To change the default measure, just simply put the following code in the bottom of your MDX script
 
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER= <whatever measure you want to use>
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s