Home > MDX > MDX – Queries 2

MDX – Queries 2

Here developing a simple yet powerful concept of MDX query.
 
Requirement: Need to get the top 10 internet selling products group by countries
 
Step 1: Starting with TopCount Function to understand the bottom line of all the functions required for the requirement
Here, getting top 10 products in all the regions as

SELECT 
  {[Measures].[Internet Sales Amount]} ON 0
  ,TOPCOUNT(
	ORDER (    [Geography].[Country].CurrentMember
                   * [Product].[Product].[Product].MEMBERS
                   , [Measures].[Internet Sales Amount]
            	, DESC 
                )
	      , 10
           ) ON 1
FROM [Adventure Works];


 
Step 2: Use the Generate Function to group the result set to each member of the country

SELECT 
  {[Measures].[Internet Sales Amount]} ON 0
 ,Generate
  (
    [Geography].[Country].[Country].MEMBERS
   ,TopCount
    (
      Order
      (
          [Geography].[Country].CurrentMember
        * 
          [Product].[Product].[Product].MEMBERS
       ,[Measures].[Internet Sales Amount]
       ,DESC
      )
     ,10
    )
  ) ON 1
FROM [Adventure Works];


 
Step 3: Here we could also rewrite the above query without using ORDER function as
 

SELECT 
  {[Measures].[Internet Sales Amount]} ON 0
 ,Generate
  (
    [Geography].[Country].[Country].MEMBERS
   ,TopCount
    (
         [Geography].[Country].CurrentMember
        * 
          [Product].[Product].[Product].MEMBERS
       ,10,[Measures].[Internet Sales Amount]
     )
  ) ON 1
FROM [Adventure Works];

 
Points to consider:
 
1. I am writing these MDX queries based on my needs in development besides logical link in the MDX functions
2. I would like to continue this MDX series further with other functions

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: