Home > MDX > MDX – Queries

MDX – Queries

I always look forward to learn new concepts in BI area. Recently, I got an opportunity to learn MDX from my team lead Bhavik Merchant and in fact, he explained me the MDX basics in just 3 hrs while developing MDX query required for a report.
I am planning to write MDX series based on my learning besides my daily MDX development work for my reference.
I have written a basic MDX query, for this post and we could see the steps of development
 
MDX query:

SELECT
  {
    [Measures].[Internet Average Sales Amount]
   ,[Measures].[Internet Unit Price]
  } ON COLUMNS
 ,
 NON EMPTY (
	ORDER(
	      FILTER
	           (
	             [Product].[Product].children
	            ,instr([Product].[Product].currentmember.Name , "Yellow")
	           )
               , [Measures].[Internet Unit Price]
	     , DESC
	     )
           )
      ON ROWS
FROM [Adventure Works];

 
Step 1: Get [All] members, this will give the measures at all member level

 
Step 2: Get [children] members using the children level as

 
Step 3: Apply the [FILTER] to filter the result set using [inStr] function, i.e. it is very similar to the LIKE operator at TSQL

 
Step 4: We always interested in nonempty values from the cube so thought to apply [NON EMPTY] function as

 
Step 5: We also use the sorting functionality so used [ORDER] function as

 
Points to consider:
 
1. These are only basic MDX functions and I would like to continue with other MDX functions as MDX series for reference.
2. I am interested in using the MDX queries for SSRS reporting as we have a lot of difference in writing own MDX query than using the MDX query generated by the SSRS query designer.

Advertisements
Categories: MDX Tags:
  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: