SQL Server – Analysis Services – MDX – Member References

Background

Reading through Sample MDX Statements and confused about the role of the ampersand (&).

And, so googled for help, but nothing quite came up.  And, so ventured down the more steady path of reading through Books.

 

Introduction

Members can be referenced via a variety of ways.  The basic pathway been through the Dimension name, its Hierarchy, and the attribute value.

In the case of the attribute value one can use the name value or the key value.

Keep in mind that each attribute has a couple of properties that are important.

Attribute Properties

The Attribute property definition affects how an Attribute is displayed and referenceable.

Column Definition Sample Data
Type User Defined Data type Calendar – Year
Calendar – Quarter
Calendar – Month
Calendar – Week
Calendar – Day
KeyColumns References the combination columns that make the Column values unique Year, Month
NameColumn Descriptive Name that will show up when an attribute such as Time.Month_Name is chosen then the contents of the Time.Month_Name such as January, March will show up
ValueColumn Underlying Value when an attribute such as Time.Month is chosen then the contents of the Time.Month such as 01, 03 will show up

 

 

 

Attribute Definition Values

Here is a quick snapshot of how our Attribute Definitions are specified:

Column Type NameColumn ValueColumn
Date Day Time.Date_Name (WChar) Time.PK_Date (Date)
DateAsYYYYMMDD Day Time.DateAsYYYYMMDD
(WChar)
Date_Name
Day_Of_Month DayOfMonth Time.Day_Of_Month_Name (WChar) Time.Day_Of_Month (Integer)
Day_Of_Year DayOfYear Time.Day_Of_Year_Name (WChar) Time.Day_Of_Year (WChar)
 Month Months Time.Month_Name (WChar) Time.Month (WChar)
 Year Years Time.Year_Name (WChar) Time.Year_Name (WChar)

 

Attribute Hierarchies

The Hierarchies defined on a Dimension also plays a role in how Dimension Attributes are “spelled” out.

AttributeHierarchies

 

Data

Data Contents

Here is screenshot that shows the data generated by the Dimension Wizard when we chose to use it to create a Time Dimension.

 

DataContents

 

 

 

Queries

Here are some sample code – Transact SQL and MDX Statements that shows subtle differences between Name and Key value references.

Queries – Get data for March – Third Month of the Year

Transact SQL


select
	  tblTime.[Month_Of_Year]
	, sum(tblSales.[SubTotal]) as SubTotal
from   dbo.[Time] tblTime
	  inner join [dbo].[SalesOrderHeader] tblSales
		on tblTime.DateAsYYYYMMDD = tblSales.OrderDateAsYYYYMMDD
where tblTime.[Month_Of_Year] = 3
group by
	  tblTime.[Month_Of_Year]


 

MDX – Using Name Value


select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		[Time].[Month Of Year].[Month 3]
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

MDX – Using Key Value

Keep an eye on the ampersand that precedes the bracket around our value column.


select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		[Time].[Month Of Year].&[3]
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

Queries – Get data for 2014

Transact SQL

select
		  tblTime.[Year]
		, sum(tblSales.[SubTotal]) as SubTotal
from   dbo.[Time] tblTime
		inner join [dbo].[SalesOrderHeader] tblSales
			on tblTime.DateAsYYYYMMDD 
                             = tblSales.OrderDateAsYYYYMMDD
where tblTime.[Year] = '2014-01-01 00:00:00.000'
group by
	  tblTime.[Year]

 

MDX – Using Name Value


select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	       (
		  [Time].[Year].[Calendar 2014]
	      )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

MDX – Using Key Value

Keep an eye on the ampersand that precedes the bracket around our value column.


select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		[Time].[Year].&[2014-01-01T00:00:00] 
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

Queries – Get data for March 27th

Get data for March 27th (Dec 86) and Dec 25th (Day of Year = 359)

Transact SQL

select
  	  datepart(dy, tblTime.[PK_Date]) as [dayofYear]
	, sum(tblSales.[SubTotal]) as SubTotal
from   dbo.[Time] tblTime
	  inner join [dbo].[SalesOrderHeader] tblSales
		on tblTime.DateAsYYYYMMDD = tblSales.OrderDateAsYYYYMMDD
where (
	     (tblTime.[Day_Of_Year] = 86) -- March 31st 
	  or (tblTime.[Day_Of_Year] = 359) --- December 25th
      )
group by
	  datepart(dy, tblTime.[PK_Date])

 

MDX – Using Name Value

select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (

		{
			  ([Time].[Day Of Year].[Day 95])
		 	, ([Time].[Day Of Year].[Day 359])
		}

	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

MDX – Using Key Value

Keep an eye on the ampersand that precedes the bracket around our value column.


select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		{
			  ([Time].[Day Of Year].&[95])
		 	, ([Time].[Day Of Year].&[359])
		}
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

Listening To

D’banj – Bother You
(Footage taken from the feature film “Half of a Yellow Sun” – Directed by Biyi Bandele)
Written by Oyebanjo Daniel Oladapo and Lionel Richie

 

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

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