Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sherry Li's BI Corner

Always wanting to publish novels, but having the fortune to work in the data warehouse corner of technology, Sherry Li started to write the mysteries of the Microsoft Business Intelligence. She writes everything from T-SQL to MDX, ETL to Expressions to Scripting, Reporting to Cubes. You can find her writings at bisherryli.wordpress.com.

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

 

 


Comments

Leave a comment on the original post [bisherryli.com, opens in a new window]

Loading comments...