Home > MDX Challenges > MDX # 39 – Find Default Measure using MDX Query

MDX # 39 – Find Default Measure using MDX Query

In Chapter 1 of our book, MDX with SSAS 2012, we’ve devoted a section “Setting a default member of a hierarchy in the MDX script” to show three places where you can set up Default members for hierarchies.

For more information about how to define a default member for hierarchies and whether  you should have a default member or not for hierarchies, please also refer to the following Microsoft Developer Network article:

Define a Default Member

MDX script is an easy place to maintain the default member definition

As the MDX book pointed out that the MDX script is an easy place to maintain the default member definition.

Below is an example of how to define a default member for the Destination Currency hierarchy by issuing an UPDATE DIMENSION command.

/*– Set default member for the Destination Currency cube dimension —-*/

Alter Cube
CurrentCube
Update Dimension [Destination Currency].[Destination Currency],
Default_Member = [Destination Currency].[Destination Currency].[US Dollar];

MDX query is also an easy place to find what the default members are for each hierarchy

As it turned out that MDX query is also an easy place to find what the default members are for each hierarchy. For every report developer, knowing how to find the default measure is also crucial. You can obviously check the dimension and measure group structures in SQL Server Data Tools, or the cube’s MDX script. But the easiest way to find default members for hierarchies and the cube’s default measure is to write your own MDX query using the DefaultMember function. Please refer to the following MSDN article:

DefaultMember (MDX)

The general syntax is:

Hierarchy_Expression.DefaultMember

Three Sample MDX queries to find default members for hierarchies and cube default measure

The following are three examples to find default members for hierarchies and the cube’s default measure.

  •  Find Default Member of Destination Currency hierarchy

WITH MEMBER Measures.[Default Member] AS
[Destination Currency].[Destination Currency].DefaultMember.Name

SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
— Default member for Destination Currency hierarchy: US Dollar

  • Find Default Member of Geography hierarchy

WITH MEMBER Measures.[Default Member] AS
[Geography].[Geography].DefaultMember.Name

SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
— Default member for Geography hierarchy: All Geographies

  • Find Default Measure of the Adventure Works cube

WITH MEMBER Measures.[Default Measure] AS
[Measures].DefaultMember.Name

SELECT
Measures.[Default Measure] ON 0
FROM
[Adventure Works]
— Default cube measure: Reseller Sales Amount

Showing default measure and the value in one query

By placing the default measure directly on the X axis, you can get the default measure name and the value in one query.

SELECT
–Measures.[Default Measure] ON 0
[Measures].DefaultMember on 0
FROM
[Adventure Works]

Reseller Sales Amount
$80,450,596.98

 

 

  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: