Exploring Additional Multi Dimensional Expression (MDX) Functions in SQL Server Analysis Services – Part 2

SQL Server Analysis Services - Additional functions in MDX

In my previous article Exploring MDX (Multi Dimensional Expression) in SQL Server Analysis Services, we discussed how to write simple queries with MDX to query multi dimensional cubes. In this article, we will continue with the same example and add some more functions.

We will also discuss how to format the data when using MDX.

1. Let us see how to format the data

WITH MEMBER Measures.[Order Quantity Formatted] AS
([Measures].[Order Quantity] ),FORMAT_STRING="#,##,#.00"
SELECT NON EMPTY {Measures.[Order Quantity Formatted]
} ON COLUMNS,
NON EMPTY [Product].[Product Category Key].MEMBERS on ROWS
FROM DemoCube

The result looks as follows:

If we change the format string to ="#,##,#.##” the result is as follows

2. Let us add a time dimension to the cube. Open the previously existing DSV (Data Source View). Right click on the empty area and select Add/Remove tables > add DimTime tab. Give the friendly name as ‘Time’.

3. Right click on Dimensions in Solution Explorer > select New Dimension and select the radio button for Use Existing Tables. In Specify Source Information dialog, select as shown below:

Keep the name for the dimension as Time and click Finish

4. Double click on the cube in Solution Explorer and select Cube Structure tab. Right click on the empty area for Dimensions and add Time dimension as a new Cube Dimension. We see that all the three dates - ship date, order date and due date are added.

5. Let us add more attributes to this dimension. Select Time Dimension from the Solution Explorer > drag and drop EnglishMonthName, CalendarQuarter, CalendarYear from Data Source View into attributes.

6. Create a hierarchy named Calendar with Calendar Year - Calendar Quarter – English Month name – Time Key. Change the name column for the Time Key to FullAlternateDateKey and build and deploy. If we browse in BIDS we see data as shown

7. Let us use this in a MDX query now.

SELECT [Measures].[Order Quantity] ON 0,
TOPCOUNT ([Order Date].[Time
Key].MEMBERS,5,[Measures].[Order Quantity]) ON 1
FROM DemoCube

will give the result as shown below:

8. Now let us use the WHERE clause. The WHERE clause determines which dimension or member is to be used as a slicer. A query can have multiple axes, but when a query has 3 axes we will not be able to display it (in SSMS). For this the WHERE clause has been provided.

SELECT [Measures].[Order Quantity] on COLUMNS,
[Product].[Subcategory].[Product Subcategory Key].&[2]
on rows
FROM DemoCube
WHERE ([Order Date].[Calendar].[Time Key].[2001-08-01 00:00:00.000] )

We can also have more than one member in WHERE

SELECT [Measures].[Order Quantity] on COLUMNS,
[Product].[Subcategory].[Product Subcategory Key].&[2] on rows
FROM DemoCube
WHERE ([Order Date].[Calendar Year].&[2001],
[Order Date].[English Month Name].&[August])

9. Let us see how certain functions can be used in calculated members

WITH MEMBER Measures.Total AS SUM
( { [Order Date].[Calendar].[Calendar Year].&[2001]
, [Order Date].[Calendar].[Calendar Year].&[2003]},
[Measures].[Sales Amount])
SELECT Measures.Total ON 0
,NON EMPTY [Product].[Product-Category].[Product Category Key].Members ON 1
FROM DemoCube

In this query we are using function SUM where sum of two years 2001 and 2003 for Sales amount is calculated. The result is as shown.

Similarly we can find average by using AVG function.

10. Following query returns maximum quarterly sales for each category

WITH MEMBER Measures.[Max Value] AS Max
([Order Date].[Calendar].[Calendar Quarter]
, [Measures].[Sales Amount]
)
SELECT Measures.[Max Value] ON 0,
NON EMPTY [Order Date].[Calendar].[Calendar Quarter] *
[Product].[Product-Category].[Product Category Key].MEMBERS
ON 1
FROM DemoCube

Max can be replaced by min if required.

11. Let us see following query which uses ‘PrevMember’ which returns previous member in the same level as the specified member

This query returns 2002 as the previous year for 2003

SELECT
[Order Date].[Calendar].[Calendar Year].&[2003].PrevMember
ON 0
FROM DemoCube

Similarly NextMember can be used.

12. Following query returns user name of the user that is executing the query

WITH MEMBER Measures.[Name] AS UserName
SELECT Measures.[Name] ON COLUMNS
FROM DemoCube

In this article we discussed how the WHERE clause works in MDX. We also discussed some functions like topcount, sum, avg, min, max, prevmember etc.

In next article we will discuss how iif and count functions can be used in MDX. We will also discuss how parallel period works.