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]
Previous Page
