Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Comparison of Queries Written in T-SQL and SQL Server MDX


By:   |   Read Comments (18)   |   Related Tips: > Analysis Services Development

Problem

Beginning to learn and comprehend SQL Server Analysis Services (SSAS) MDX queries can be difficult after one has spent years writing queries in T-SQL. When trying to write SQL Server MDX queries, oftentimes I would think to myself, "How would I write this query in T-SQL?"

Solution

The solution presented in this tip will demonstrate a T-SQL Query followed by an SQL Server MDX query that will return the same results. Seven examples will be presented in order of increasing complexity. The examples presented here were created in SQL Server 2012 Management Studio using the SQL Server AdventureWorksDW2012 and the Analysis Services AdventureWorksDW2012Multidimensional-SE databases. Please note that the Adventure Works SSAS Cube is built from the data contained in the AdventureWorksDW2012 database.

Example 1

In T-SQL Query 1, we select the count of records in the table FactInternetSales.

-- T-SQL Query 1
SELECT COUNT(*) AS [Internet Order Quantity]
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales]

T-SQL Query 1

In SQL Server MDX Query 1, we select [Measures].[Internet Order Quantity] which is defined as the count of rows in the table FactInternetSales.

-- SQL Server MDX Query 1
SELECT                                                                                  
NON EMPTY                                                                                
{                                                                                                                                         
  [Measures].[Internet Order Quantity]                                                   
} ON COLUMNS                                                                     
FROM [Adventure Works]

SQL Server MDX Query 1

Example 2

In T-SQL Query 2, we add the summation of the SalesAmount column for all records in the table FactInternetSales. The round() function was added so the format of the output from the T-SQL query will match the output of the SQL Server MDX query.

-- T-SQL Query 2
SELECT COUNT(*) AS [Internet Order Quantity],
ROUND(SUM(SalesAmount),2) AS [Internet Sales Amount]
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales]

T-SQL Query 2

In SQL Server MDX Query 2, we add [Measures].[Internet Order Quantity] which is defined as the sum of the SalesAmount column in the table FactInternetSales. Please note that the measure Internet Order Quantity is configured in the cube to display as currency.

-- SQL Server MDX Query 2
SELECT                                                                                  
NON EMPTY                                                                                
{                                                                                                                                         
  [Measures].[Internet Order Quantity],  
  [Measures].[Internet Sales Amount]                                                 
} ON COLUMNS                                                                     
FROM [Adventure Works]  

SQL Server MDX Query 2

Example 3

In T-SQL Query 3, we start the process of analyzing the count and amount grouped by the education level of the customer base. We add the inner join to the DimCustomer table using the CustomerKey column. Also, we add the EnglishEducation column from the DimCustomer table to the SELECT, GROUP BY and ORDER BY clauses.

-- T-SQL Query 3
SELECT
d1.EnglishEducation AS [Customer Education Level], 
COUNT(*) AS [Internet Order Quantity],
ROUND(SUM(f1.SalesAmount),2) AS [Internet Sales Amount]
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] f1
INNER JOIN [AdventureWorksDW2012].[dbo].[DimCustomer] d1
   ON f1.CustomerKey=d1.CustomerKey
GROUP BY d1.EnglishEducation
ORDER BY d1.EnglishEducation

T-SQL Query 3

In SQL Server MDX Query 3, we add a block of code to handle slicing of the selected measures by the Education attribute of the Customer dimension.

-- SQL Server MDX Query 3
SELECT                                                                                  
NON EMPTY                                                                                
{                                                                                                                                         
  [Measures].[Internet Order Quantity],  
  [Measures].[Internet Sales Amount]                                                 
} ON COLUMNS,      
NON EMPTY                                                                                
{ (                                                                                      
  [Customer].[Education].[Education].ALLMEMBERS                                    
) } ON ROWS                                                                                                                                             
FROM [Adventure Works]    

SQL Server MDX Query 3

Example 4

In T-SQL Query 4, we add a CASE statement to convert the values in the HouseOwnerFlag column from an integer to a string representation of the data. We add the HouseOwnerFlag column to both the GROUP BY and ORDER BY clauses.

-- T-SQL Query 4
SELECT
d1.EnglishEducation AS [Customer Education Level], 
CASE 
   WHEN d1.HouseOwnerFlag = 0 THEN 'No'
   ELSE 'Yes'
END AS [House Owner],
COUNT(*) AS [Internet Order Quantity],
ROUND(SUM(f1.SalesAmount),2) AS [Internet Sales Amount]
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] f1
INNER JOIN [AdventureWorksDW2012].[dbo].[DimCustomer] d1
   ON f1.CustomerKey=d1.CustomerKey
GROUP BY d1.EnglishEducation, d1.HouseOwnerFlag
ORDER BY d1.EnglishEducation, d1.HouseOwnerFlag

T-SQL Query 4

In SQL Server MDX Query 4, we add a line of code to handle slicing of the selected measures by the Home Owner attribute of the Customer dimension. Please note the asterisk instead of a comma at the end of the line [Customer].[Education].[Education].ALLMEMBERS.

-- SQL Server MDX Query 4
SELECT                                                                                  
NON EMPTY                                                                                
{                                                                                                                                         
  [Measures].[Internet Order Quantity],  
  [Measures].[Internet Sales Amount]                                                 
} ON COLUMNS,      
NON EMPTY                                                                                
{ (                                                                                      
  [Customer].[Education].[Education].ALLMEMBERS *
  [Customer].[Home Owner].[Home Owner].ALLMEMBERS
) } ON ROWS                                                                                                                                             
FROM [Adventure Works]

SQL Server MDX Query 4

Example 5

In T-SQL Query 5, we add a WHERE clause to only return the rows where the ShipDate is from 2005 through 2007 inclusive.

-- T-SQL Query 5
SELECT
d1.EnglishEducation AS [Customer Education Level], 
CASE 
   WHEN d1.HouseOwnerFlag = 0 THEN 'No'
   ELSE 'Yes'
END AS [House Owner],
COUNT(*) AS [Internet Order Quantity],
ROUND(SUM(f1.SalesAmount),2) AS [Internet Sales Amount]
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] f1
INNER JOIN [AdventureWorksDW2012].[dbo].[DimCustomer] d1
   ON f1.CustomerKey=d1.CustomerKey
WHERE year(f1.ShipDate)>=2005 and year(f1.ShipDate)<=2007 
GROUP BY d1.EnglishEducation, d1.HouseOwnerFlag
ORDER BY d1.EnglishEducation, d1.HouseOwnerFlag

T-SQL Query 5

In SQL Server MDX Query 5, we add a FROM clause to specify the range of the calendar years from the Ship Date role-playing dimension. Please note the colon (:) is used between the low and high values of the range. Also, we had to add a closing parenthesis at the end of FROM [Adventure Works]. The counts and amounts are decreasing due to the FROM clause specifying specific calendar years.

-- SQL Server MDX Query 5
SELECT                                                                                  
NON EMPTY                                                                                
{                                                                                                                                         
  [Measures].[Internet Order Quantity],  
  [Measures].[Internet Sales Amount]                                                 
} ON COLUMNS,      
NON EMPTY                                                                                
{ (                                                                                      
  [Customer].[Education].[Education].ALLMEMBERS  *
  [Customer].[Home Owner].[Home Owner].ALLMEMBERS
) } ON ROWS                            
FROM (SELECT ([Ship Date].[Calendar Year].&[2005]:            
              [Ship Date].[Calendar Year].&[2007]) ON COLUMNS 
FROM [Adventure Works])   

SQL Server MDX Query 5

Example 6

In T-SQL Query 6, we add an inner join to the DimGeography table on the GeographyKey to allow for the use of geography data in the query. We have also added to the WHERE clause to remove the data for those customers who live in the United States.

-- T-SQL Query 6
SELECT
d1.EnglishEducation AS [Customer Education Level], 
CASE 
   WHEN d1.HouseOwnerFlag = 0 THEN 'No'
   ELSE 'Yes'
END AS [House Owner],
COUNT(*) AS [Internet Order Quantity],
ROUND(SUM(f1.SalesAmount),2) AS [Internet Sales Amount]
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] f1
INNER JOIN [AdventureWorksDW2012].[dbo].[DimCustomer] d1
   on f1.CustomerKey=d1.CustomerKey
INNER JOIN [AdventureWorksDW2012].[dbo].[DimGeography] d2 
   on d1.GeographyKey=d2.GeographyKey
WHERE year(f1.ShipDate)>=2005 and year(f1.ShipDate)<=2007
AND  d2.EnglishCountryRegionName<>'United States'
GROUP BY d1.EnglishEducation, d1.HouseOwnerFlag
ORDER BY d1.EnglishEducation, d1.HouseOwnerFlag

T-SQL Query 6

In SQL Server MDX Query 6, we add a FROM clause to remove the data for those customers who live in the United States. Please note the minus sign (-) before the curly bracket after the word SELECT. Also, we had to add another closing parenthesis at the end of FROM [Adventure Works].

-- SQL Server MDX Query 6
SELECT                                                                                  
NON EMPTY                                                                                
{                                                                                                                                         
  [Measures].[Internet Order Quantity],  
  [Measures].[Internet Sales Amount]                                                 
} ON COLUMNS,      
NON EMPTY                                                                                
{ (                                                                                      
  [Customer].[Education].[Education].ALLMEMBERS  *
  [Customer].[Home Owner].[Home Owner].ALLMEMBERS
) } ON ROWS                            
FROM (SELECT ([Ship Date].[Calendar Year].&[2005]:            
              [Ship Date].[Calendar Year].&[2007]) ON COLUMNS 
FROM(SELECT(-{ [Customer].[Customer Geography].[Country].&[United States]}) ON COLUMNS 
FROM [Adventure Works]))

SQL Server MDX Query 6

Example 7

In T-SQL Query 7, we add to the WHERE clause so only information about Female customers is returned. Again, we had to add another closing parenthesis at the end of FROM [Adventure Works].

-- T-SQL Query 7
SELECT
d1.EnglishEducation AS [Customer Education Level], 
CASE 
   WHEN d1.HouseOwnerFlag = 0 THEN 'No'
   ELSE 'Yes'
END AS [House Owner],
COUNT(*) AS [Internet Order Quantity],
ROUND(SUM(f1.SalesAmount),2) AS [Internet Sales Amount]
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] f1
INNER JOIN [AdventureWorksDW2012].[dbo].[DimCustomer] d1
   ON f1.CustomerKey=d1.CustomerKey
INNER JOIN [AdventureWorksDW2012].[dbo].[DimGeography] d2 
   ON d1.GeographyKey=d2.GeographyKey
WHERE year(f1.ShipDate)>=2005 and year(f1.ShipDate)<=2007
AND  d2.EnglishCountryRegionName<>'United States'
AND  d1.Gender='F'
GROUP BY d1.EnglishEducation, d1.HouseOwnerFlag
ORDER BY d1.EnglishEducation, d1.HouseOwnerFlag

T-SQL Query 7

In SQL Server MDX Query 7, we add a FROM clause to select where the Gender attribute of the customer dimension equals F for Female.

-- SQL Server MDX Query 7
SELECT                                                                                  
NON EMPTY                                                                                
{                                                                                                                                         
  [Measures].[Internet Order Quantity],  
  [Measures].[Internet Sales Amount]                                                 
} ON COLUMNS,      
NON EMPTY                                                                                
{ (                                                                                      
  [Customer].[Education].[Education].ALLMEMBERS  *
  [Customer].[Home Owner].[Home Owner].ALLMEMBERS
) } ON ROWS                            
FROM (SELECT ([Ship Date].[Calendar Year].&[2005]:            
              [Ship Date].[Calendar Year].&[2007]) ON COLUMNS 
FROM(SELECT ( -{ [Customer].[Customer Geography].[Country].&[United States]}) ON COLUMNS
FROM(SELECT ( { [Customer].[Gender].&[F] } ) ON COLUMNS 
FROM [Adventure Works])))

SQL Server MDX Query 7
Next Steps


Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, November 25, 2015 - 2:02:10 AM - Ashish Kumar Gupta Back To Top

SELECT

d1.FrenchEducation AS [Customer Education Level], 

COUNT(*) AS [Internet Order Quantity],

ROUND(SUM(f1.SalesAmount),2) AS [Internet Sales Amount]

FROM [AdventureWorksDW2008R2].[dbo].[FactInternetSales] f1

INNER JOIN [AdventureWorksDW2008R2].[dbo].[DimCustomer] d1

   ON f1.CustomerKey=d1.CustomerKey

GROUP BY d1.FrenchEducation

ORDER BY d1.FrenchEducation

 

what mdx query i write if i replace english education with FrenchEducation for adventure works?

please help me.

 

This post is very helpfull 


Monday, September 14, 2015 - 12:10:01 PM - mona Back To Top

Below is my current query. There are two date parameters   r.AcceptedDate (to and from)

 SELECT (SELECT SUM(t.TepTotal)
  FROM [RELET2007].[dbo].[RELETProjectsReports] r
  INNER JOIN [RELET2007].[dbo].[RELETProjectsReportsTEP] t on r.RELETPRPID = t.RELETPRPID
   Where r.TEDACReceiveDate < '10-1-2014'

   AND r.AcceptedDate >= '2-1-2015'
AND r.AcceptedDate < '2-10-2015') 'Backlog',
  (SELECT SUM(t.TepTotal)
  FROM [RELET2007].[dbo].[RELETProjectsReports] r
  INNER JOIN [RELET2007].[dbo].[RELETProjectsReportsTEP] t on r.RELETPRPID = t.RELETPRPID
  Where r.TEDACReceiveDate >= '10-1-2014'
  AND r.AcceptedDate >= '2-1-2015'
AND r.AcceptedDate < '2-10-2015') 'REST OF WORLD';

 


Tuesday, April 22, 2014 - 1:49:54 AM - Rahil Back To Top

Thanks for this post, i was searching for the difference between MDX and SQL query and i got it here.

Very helpful post for beginners.


Sunday, March 23, 2014 - 2:09:13 PM - Assad Back To Top

Also, if we can get some WHERE/FILTER etc side by side in SQL and MDX....would be very helpfull!

 

E.g.: How to fillter record on particular member when that dimension is also part of SELECT? (which is generally not allowed on MDX)


Saturday, March 22, 2014 - 8:58:54 PM - Assad Back To Top

Thank You! 

This is very helpfull for user like me who have worked on T-SQL whole life and now could not get out "SQL mind-set" while learning MDX. So viewing side by side clears alot of MDX concepts.

Please keep writing more on similar SQL-MDX side-by-side topic.


Wednesday, September 11, 2013 - 8:16:25 AM - gourav Back To Top

 it's very useful , can you also write some differences on the performance of the SQL and MDX


Thursday, May 16, 2013 - 4:06:41 PM - Maroof Khan Back To Top

The camparative examples between SQL and MDX give a good solid foundation to to start learning MDX.


Sunday, May 05, 2013 - 1:21:55 PM - abc Back To Top

Nice post.

But a question about example #1,

for [FactInternetSales], it's true, because the OrderQuantity column is always 1 for all rows,

but for [FactResellerSales], it's not true, because the OrderQuantity column is not always 1 for all rows.

 


Tuesday, April 23, 2013 - 6:27:26 AM - Divya Back To Top

Great post!! Really helpful to fight with the fear of MDX!!


Friday, April 19, 2013 - 12:32:44 PM - Gailk Back To Top

Finally a way to see that MDX isn't so bad after all -- totally makes sense when I see T-SQL lined up against MDX.  I'll be sharing this with our team of hard core SQL folks.


Wednesday, April 03, 2013 - 12:10:03 PM - Vivekanand Kola Back To Top

i need some more information about mdx querys by using all the function with example,can you provide realtime requirement example.


Monday, April 01, 2013 - 9:36:16 AM - Boris Tyukin Back To Top

nice post, but it does not really show where MDX shines and simplify things a lot comparing to SQL and vice versa. I was hoping to see some examples of very long and ***bersome T-SQL and very neat and compact MDX for analytical queries - this is the whole point of MDX vs. T-SQL. Also there is no need to use subqueries to filter data - WHERE would be more efficient in this case.


Thursday, March 28, 2013 - 10:42:41 AM - Cary Davis Back To Top

Bookmarked!  Nothing like framing something you don't know in the context of something you do know.  Great examples.


Thursday, March 28, 2013 - 10:04:27 AM - Raj Back To Top

Very very helpful..


Thursday, March 28, 2013 - 9:02:10 AM - John Back To Top

Very cool.


Thursday, March 28, 2013 - 8:44:31 AM - dhason padmakumar Back To Top

Very useful.


Thursday, March 28, 2013 - 3:05:23 AM - Ranjith Back To Top

Interesting...


Thursday, March 28, 2013 - 1:26:07 AM - Yadav Back To Top

Very Nice.. :-)


Learn more about SQL Server tools