How to Write Basic MDX Query

How to write basic MDX query

When writing MDX queries you need to make a clear distinction between the data and metadata. Cube data is what is being presented to the users, for example you would show the sales amount for Volkswagen Polo 2014 and Volkswagen Vento 2015. Metadata is how is the structuring of the data in the dimensions, for example, the product dimension could have levels of brand, make, category, model and year.

Few of the basic things which one needs to keep in mind while writing MDX queries.

Let’s have a quick look at it.

Member : [time].[January 8th, 2009]

Default member : ALL

Tuple: Combination of members ex- ([time].[January 8th, 2015) would be a tuple even though it refers to only one dimension.

Tuple referring multiple dimensions: ([product].[Volkswagen], [time].[January 8th, 2015])

Invalid tuple :([product].[Volkswagen], [time].[January], [time].[February]), referring 1 or more dimension at the same time.

Set : Collection of one or multiple tuples having the same dimensionality.

Valid Set :{([product].[Volkswagen], [time].[January]), ([product].[Audi], [time].[December])}

Invalid Set : Combination of ([product].[Volkswagen], [time].[January]) cannot be combined with ([time].[February], [product].[Audi]) because they specify dimensions in reverse order.

Empty Set : ({}).

Writing a simple MDX query :

SELECT  
    { [Measures].[Sales Amount],   
        [Measures].[Tax Amount] } ON COLUMNS,  
    { [Date].[Fiscal].[Fiscal Year].&[2014],   
        [Date].[Fiscal].[Fiscal Year].&[2015] } ON ROWS  
FROM [Sales]  
WHERE ( [Sales Territory].[Northwest])

• The SELECT clause sets the query axes as the Sales Amount and Tax Amount members of the Measures dimension, and the 2014 and 2015 members of the Date dimension.
• The FROM clause indicates that the data source is the Sales cube.
• The WHERE clause defines the slicer axis as the Northwest member of the Sales Territory dimension.We can also use ordinal positions for these axes.

SELECT  
    { [Measures].[Sales Amount],   
        [Measures].[Tax Amount] } ON 0,  
    { [Date].[Fiscal].[Fiscal Year].&[2014],   
        [Date].[Fiscal].[Fiscal Year].&[2015] } ON 1 
FROM [Sales]  
WHERE ( [Sales Territory].[Northwest]).



	

		

			
			
Best Open Source Business Intelligence Software Helical Insight is Here
		
		

			
		
		

			A Business Intelligence Framework
		
		

			Free
Download