Page 148 - DCAP606_BUSINESS_INTELLIGENCE

Unit 10: Retrieving Data from Analysis Services

The most widespread use of an MDX query is to extract values from an OLAP cube to populate Notes
a report. A cube has dimensions, but a report does not. Reports have axes. An axis can include
members from more than one dimension.

!
Caution A report generally doesn’t show all of the data comprised in a cube.
The basic Multidimensional Expressions (MDX) query is the SELECT statement.

10.2.1 SELECT Statement Syntax

The following syntax shows a basic SELECT statement that includes the use of the SELECT,
FROM, and WHERE clauses:
[ WITH <SELECT WITH clause> [ , <SELECT WITH clause> ... ] ]
SELECT [ * | ( <SELECT query axis clause>
[ , <SELECT query axis clause> ... ] ) ]
FROM <SELECT subcube clause>
[ <SELECT slicer axis clause> ]
[ <SELECT cell property list clause> ]
The MDX SELECT statement supports optional syntax, such as the WITH keyword, the use of
MDX functions and the ability to return the values of specific cell properties as part of the query.

10.2.2 SELECT Statement Example

The following example shows a basic MDX query that uses the SELECT statement. This query
returns a result set that contains the 2010 and 2011 sales and tax amounts for the North sales
territories.
SELECT
{ [Measures].[Sales],
[Measures].[Tax] } ON COLUMNS,
{ [Date].[Fiscal].[Fiscal Year].&[2010],
[Date].[Fiscal].[Fiscal Year].&[2011] } ON ROWS
FROM [Adventure Works]
WHERE ( [Sales Territory].[North] )
In this example, the query defines the following result set information:
The SELECT clause sets the query axes as the Sales and Tax members of the Measures
dimension, and the 2010 and 2011 members of the Date dimension.

The FROM clause indicates that the data source is the Adventure Works cube.
The WHERE clause defines the slicer axis as the North member of the Sales Territory
dimension.

Notice that the query example also uses the COLUMNS and ROWS axis aliases. The ordinal
positions for these axes could also have been used.

Example: The following example shows how the MDX query could have been written to
use the ordinal position of each axis:
SELECT
{ [Measures].[Sales],

LOVELY PROFESSIONAL UNIVERSITY 143