By: Dallas Snider | Comments (18) | Related: > 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]
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]
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]
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]
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
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]
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
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]
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
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])
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
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]))
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
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])))
Next Steps
- Copy and paste these SQL Server MDX queries into the SQL Server Management Studio query editor window. Experiment with subtle changes to see what works and what doesn't work. This should help to build your confidence and ability to write SQL Server MDX queries.
- Please refer to the following tips for further assistance with SQL Server MDX:
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
