Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Tips for Querying OLAP Databases with MDX : Page 2

MDX is a SQL-like query language used to retrieve data from OLAP databases and to define Key Performance Indicators (KPI) in SQL Server 2005 Analysis Services.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Tip 1: "MDX Looks Like T-SQL, Tastes Like T-SQL—but…"
Here are some starter queries to help you get a feel for MDX. This first query retrieves sales amount by Customer State. Note the clauses ON COLUMNS, ON ROWS, and FROM [CubeName]:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS, [Customer].[State-Province].CHILDREN ON ROWS FROM [Adventure Works]

Either from this MDX query, or from the next set of queries, you'll discover some immediate differences between MDX and SQL:

  • You explicitly state the COLUMNS and ROWS that you want for the result set.
  • You don't issue JOIN statements.
  • You will usually query from one OLAP cube. In rare instances, you may need to retrieve data from a second cube. Tip 13 covers this with the LOOKUPCUBE function.
When you view the results of the first MDX query, you'll also notice that the result set contains a row for every state, even those with NULL results. You can use the NON EMPTY statement to filter out NULL results:

-- Filter out NULL results SELECT [Measures].[Internet Sales Amount] ON COLUMNS, NON EMPTY [Customer].[State-Province].CHILDREN ON ROWS FROM [Adventure Works]

Of course, many MDX queries will need to return multiple measures, broken out by multiple dimensions. To return sales and freight by Product Subcategory within state, you'll need to wrap curly braces around the column names, and parentheses around the dimensions:

-- Use {} to enclose multiple measures -- Use () to enclose multiple dimensions SELECT { [Measures].[Internet Sales Amount], [Measures].[Internet Freight Cost] } ON COLUMNS, NON EMPTY ( [Customer].[State-Province].CHILDREN, [Product].[Subcategory].CHILDREN ) ON ROWS FROM [Adventure Works]

Tip 2: Can I Take Your "Order"?
Most results sets need to be sorted in a particular order. The MDX equivalent of the SQL ORDER BY statement is the ORDER function.

The ORDER function receives three parameters: the set definition to be sorted (i.e., show States and Subcategories down the left), the data to sort on (Sales Amount), and whether to sort in ascending or descending sequence:

-- Use ORDER (will sort within customer) SELECT [Measures].[Internet Sales Amount] ON COLUMNS, NON EMPTY ORDER( ([Customer].[State-Province].CHILDREN, [Product].[Subcategory].CHILDREN ), [Internet Sales Amount],DESC) ON ROWS FROM [Adventure Works]

This MDX query produces the following results:

-- Result set is ordered by -- Category sales within State Alabama Tires and Tubes $37.29 Alberta Road Bikes $14,022.43 Alberta Touring Bikes $5,510.49 Alberta Mountain Bikes $2,294.99 Alberta Bike Racks $240.00 Arizona Mountain Bikes $2,071.42 Arizona Tires and Tubes $32.60

Notice one thing: The results are sorted by sales descending, within the state. In other words, the sort "respected" the hierarchy. However, you may want to rank all state/subcategories combinations, regardless of the hierarchy—in other words, you may want to "break" the hierarchy. MDX provides the BDESC (or BASC, for ascending), commands if you want the sort to break the hierarchy.

Tip 3: To Sir, "With" Love
One of my favorite movies is "To Sir, With Love". Well, one of my favorite MDX language features also begins with WITH.

The first few MDX queries I discussed in this article retrieved existing measures. However, many queries need to return calculations based on existing measures (for example, per unit calculations, percentages, etc.) You do this in standard SQL syntax with something like (Sales / Units AS SalesPerUnit). In MDX, you do this by creating a calculated member, using the WITH MEMBER statement, and then by using the calculated member in the query. Note that you can format the display of the calculated member with the FORMAT_STRING statement:

WITH MEMBER [Measures].[GrossProfitPerUnit] AS [Measures].[Internet Gross Profit] / [Measures].[Internet Order Quantity], FORMAT_STRING = '$0.00' -- Result set SELECT { [Measures].[Internet Gross Profit], [Measures].[Internet Order Quantity], [Measures].[GrossProfitPerUnit] ON COLUMNS, NON EMPTY ([Customer].[State-Province].CHILDREN, [Product].[Subcategory].CHILDREN) ON ROWS FROM [Adventure Works]

If you need to create multiple calculated members, you can use the following syntax:

WITH MEMBER [Measures].[GrossProfitPerUnit] AS [Measures].[Internet Gross Profit] / [Measures].[Internet Order Quantity], FORMAT_STRING = '$0.00' MEMBER [Measures].[NetProfitPerUnit] AS [Measures].[Internet Sales Amount] / [Measures].[Internet Order Quantity], FORMAT_STRING = '$0.00' SELECT…

You can also use the WITH statement to build a customer SET definition. A SET is essentially a collection (usually an ordered collection) of members (i.e., TUPLES) from one or more hierarchies. The code below defines a SET called OrderedSales—you can abstract out the Order logic into the SET definition, and then refer to the SET in the SELECT statement:

WITH SET [OrderedSales] AS ORDER( ([Customer].[State-Province].CHILDREN, [Product].[Subcategory].CHILDREN ), [Internet Sales Amount],BDESC) SELECT [Measures].[Internet Sales Amount] ON COLUMNS, NON EMPTY [OrderedSales] on rows FROM [Adventure Works]

Many MDX queries use calculated members and sets together. Suppose you want to sort on a calculated member. You would create the calculated member first and then create a SET with an ORDER function that uses the calculated member:

WITH MEMBER [Measures].[GrossProfitPerUnit] AS [Measures].[Internet Gross Profit] / [Measures].[Internet Order Quantity], FORMAT_STRING = '$0.00' SET [OrderedSales] AS ORDER( ([Customer].[State-Province].CHILDREN, [Product].[Subcategory].CHILDREN ), [GrossProfitPerUnit],BDESC) SELECT {[Measures].[Internet Sales Amount], [GrossProfitPerUnit]} ON COLUMNS, NON EMPTY [OrderedSales] on rows FROM [Adventure Works]

Thanks for your registration, follow us on our social networks to keep up-to-date