Querying Your Cube - Getting Started with SQL Server 2012 Cube Development - page 136

--Query 5.24
SELECT {[Order Date].[Hierarchy].[Calendar
Year], [Order Date].[Hierarchy].[All]} ON 0,
{[Product].[Product Hierarchy].[Product
Category], [Product].[Product
Hierarchy].[All]} ON 1
FROM [Adventure Works DW2012]
WHERE [Measures].[Sales Amount];
This query is actually quite simple, but there are not many end users that would be
able to write it.
Another advantage of Excel as a client is that you actually get a plus sign before
every single hierarchy level. The reason to this is that Excel actually understands
that it is a hierarchy that it deals with. Because of this, you can drill in the hierarchy,
and Excel will send a new query down to Analysis Services.
These are the actual queries that are sent down to the cube by the PivotTable and
when you click on the Bikes category to drill down to the subcategory level in the
hierarchy:
--Query 5.25
SELECT NON EMPTY
Hierarchize({DrilldownLevel({[Order
Date].[Hierarchy].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON
COLUMNS ,
NON EMPTY
Hierarchize({DrilldownLevel({[Product].[Product
Hierarchy].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON
ROWS
FROM [Adventure Works DW2012]
WHERE ([Measures].[Sales Amount]) CELL
PROPERTIES VALUE, FORMAT_STRING, LANGUAGE,