Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SSAS - MDX Basic Queries

Basic MDX Query Design

This article describes how to write some basic "starter queries" after creating, Deploying, and Processing a Cube in SSMS by connecting to an Anslysis Services datasource. Please see SSAS Cubes to create a cube from a Data Warehouse.

Dimensions using Children vs Members

The following 2 queries use the Measures of Sales Amount & Reseller Sales Amount for axis 0 (or columns) and the Product Category Name on axis 1 (or rows). The first one uses the children of the dimension.

select { Measures.[Sales Amount],
[Measures].[Sales Amount - Fact Reseller Sales] } on 0,
[Dim Product Category].[Category Name].children on 1
FROM AdventureWorksProductsDW2012;

MDX Dimension query Children

The second query is the same as the first, but uses Members of the Product Category and now includes "All" as one of the rows displayed.

select { Measures.[Sales Amount],
[Measures].[Sales Amount - Fact Reseller Sales] } on 0,
[Dim Product Category].[Category Name].members on 1
FROM AdventureWorksProductsDW2012;

MDX Dimension query Members

Slicer Axis

The next query is similar to the query above except that it uses the Country Canada in the slicer axis in the Where clause to provide only sales figures for that country.

select { Measures.[Sales Amount] } on 0,
[Dim Product Category].[Category Name].members on 1
FROM AdventureWorksProductsDW2012
where [Dim Customer].[English Country Region Name].&[Canada];

MDX Dimension Slicer in WHERE clause

This next query uses a Measures dimension in the slicer axis and includes the Order Date Years in the Columns and State Province names in the Rows.

SELECT [Order Date].[Calendar Year].children on 0,
NON EMPTY [Dim Geography].[State Province Name].Members ON 1
FROM [AdventureWorksProductsDW2012]
where {[Measures].[Sales Amount]};

MDX Measure Slicer in WHERE clause

Cross Join in Query

This query uses a Cross Join between the Country United States and the States in columns and the Measure Sales Amount in the slicer axis to provide all sales amounts in the row by State.

SELECT [Dim Geography].[English Country Region Name].[United States] *
[Dim Geography].[State Province Name].Members ON 0
FROM [AdventureWorksProductsDW2012]
WHERE Measures.[Sales Amount];

MDX Cross Join Query with Slicer

This next query uses the Sales Amount in the Columns and the Cross Join of the United States and States Members in the rows

select Measures.[Sales Amount] on 0,
[Dim Geography].[English Country Region Name].[United States] *
[Dim Geography].[State Province Name].Members on 1
FROM [AdventureWorksProductsDW2012];

MDX Cross Join in Rows

This Cross Join example of Subcategories and Sales Amounts over Order Years uses a tuple of subcategories which displays all Subcategories but only Amounts for the Product Subcategories of Road Bikes and Mountain Bikes.

SELECT {[Measures].[Sales Amount]} *
[Dim Product Subcategory].[English Product Subcategory Name].members ON COLUMNS,
{[Order Date].[Calendar Year].MEMBERS} ON ROWS
FROM [AdventureWorksProductsDW2012]
WHERE({[Dim Product].[Product Subcategory Key].&[1],
[Dim Product].[Product Subcategory Key].&[2]});

MDX Cross Join Subcategory Sales Amounts over Order Years

Here we are using NON EMPTY to remove other empty members from results - both empty years and columns from data returned.

SELECT Non Empty {[Measures].[Sales Amount]}
* [Dim Product Subcategory].[English Product Subcategory Name].members ON COLUMNS,
Non Empty {[Order Date].[Calendar Year].MEMBERS} ON ROWS
FROM [AdventureWorksProductsDW2012]
WHERE({[Dim Product].[Product Subcategory Key].&[1],
[Dim Product].[Product Subcategory Key].&[2]});

MDX Cross Join Subcategory Sales Amounts over Order Years Non Empty

And finally, we add a Having stmt to limit the output to only sales Amounts > $5,000,000.

SELECT non empty {[Measures].[Sales Amount]} *
[Dim Product Subcategory].[English Product Subcategory Name].members ON COLUMNS,
non empty {[Order Date].[Calendar Year].MEMBERS}
HAVING Measures.[Sales Amount] > 5000000 ON ROWS
FROM [AdventureWorksProductsDW2012]
WHERE({[Dim Product].[Product Subcategory Key].&[1],
[Dim Product].[Product Subcategory Key].&[2]});

MDX Cross Join Subcategory Sales Amounts with Having Clause

Calculated Members

In the Connection Dialog, click on Options and add Additional Connection String information to allow Subqueries to be executed by adding Subqueries=1 or Subqueries=2. If this is not done, the default of Subqueries=0 is automatically chosen and an error is generated when attempting to execute the query on the Calculated Member

Analysis Server Connection Parameter for Subqueries=2

Below is the result that you will see in the Messages window in SSMS if you do not add the Subqueries text to the Connection String when opening the SSAS connection.

Executing the query ...
A set has been encountered that cannot contain calculated members.
Execution complete

Create the Member for the query. In this case, the member includes All Subcategories of Bikes.

CREATE MEMBER [AdventureWorksProductsDW2012].[Dim Product Subcategory].[English Product Subcategory Name].[All Bikes]
AS AGGREGATE(
{
[Dim Product Subcategory].[English Product Subcategory Name].&[Mountain Bikes],
[Dim Product Subcategory].[English Product Subcategory Name].&[Road Bikes],
[Dim Product Subcategory].[English Product Subcategory Name].&[Touring Bikes]
}
)

Execute the query for the calculated member and see the sales amounts across each year for All Bikes sold.

  Select [Order Date].[Calendar Year].members on 0,
  [Dim Product Subcategory].[English Product Subcategory Name].allmembers on 1
  from (Select {[Dim Product Subcategory].[English Product Subcategory Name].[All Bikes]} on 0
   from [AdventureWorksProductsDW2012] )
  Where [Measures].[Sales Amount];

MDX Query on Calculated Member