// First SELECT, everything, default measure SELECT FROM [Analysis Services Tutorial]
// First WHERE = slicer SELECT FROM [Analysis Services Tutorial] WHERE [Order Date].[Calendar Year].&[2006]
// [Order Date] - dimension // [Calendar Year] - attribute hierarchy // &[2006] - member
SELECT [Measures].[Reseller Sales-Sales Amount] ON COLUMNS FROM [Analysis Services Tutorial] WHERE [Order Date].[Calendar Year].&[2006]
SELECT [Measures].[Reseller Sales-Sales Amount] ON 0 FROM [Analysis Services Tutorial] WHERE [Order Date].[Calendar Year].&[2006]
// 0 = COLUMNS - always first
SELECT [Measures].[Reseller Sales-Sales Amount] ON COLUMNS, [Product].[Product Line].MEMBERS ON ROWS FROM [Analysis Services Tutorial] WHERE [Order Date].[Calendar Year].&[2006]
/* ------------------------------------------------------------------- Each attrib hierarchy within the dimensions of the cube forms AN AXIS.
Along each axis, each member of attrib hierarchy, including the (All) member, occupies a position.
Measures (dim) are also assigned an axis. Measures attrib hier does not have (All) member.
Points in cube can be referenced using a tuple.
Level 1 Leaf Level [Accessories] [Bikes] [Clothing] [Components]
<-- All Products -- Accessories -- Bikes -- Clothing -- Components -->
REFERENCING:
[Dimension].[Hierarchy].[Member]
---------------------------------------------------------------------- */ SELECT FROM [Analysis Services Tutorial] WHERE ( [Product].[Product Line].&[R], [Due Date].[Calendar Year].[All], [Due Date].[Fiscal Year].[All], [Measures].[Reseller Sales-Sales Amount] )
/* Points in cube space are occupied by cells.
The default cells' properties are:
- VALUE (aggregated measure value)
- FORMATTED_VALUE */
SELECT FROM [Analysis Services Tutorial] WHERE ( [Product].[Product Line].&[R], [Due Date].[Calendar Year].[All], [Due Date].[Fiscal Year].[All], [Measures].[Reseller Sales-Sales Amount] ) CELL PROPERTIES FORMATTED_VALUE, FORMAT_STRING, VALUE, FONT_NAME, FORE_COLOR, BACK_COLOR, ACTION_TYPE
/* [Dimension].[Hierarchy].[Member] - can be ambiguous
[Dimension].[Hierarchy].[Level].[Member]
User hierarchies
User user hier to build tuples.
*/
SELECT FROM [Analysis Services Tutorial] WHERE [Due Date].[Calendar Date].[Calendar Year].[CY 2005].[H2 CY 2005].[Q4 CY 2005].[December 2005].&[20051228] //[Due Date].[Fiscal Date].[Fiscal Year].&[2006].&[2006]&[1].&[2006]&[2].&[2005]&[December].&[20051228]
SELECT FROM [Analysis Services Tutorial] WHERE ( //[Due Date].[Calendar Date].[Calendar Year].[CY 2005].[H2 CY 2005].[Q4 CY 2005].[December 2005].&[20051228] [Due Date].[Fiscal Date].[Fiscal Year].&[2006].&[2006]&[1].&[2006]&[2].&[2005]&[December].&[20051230] )
SELECT FROM [Analysis Services Tutorial] WHERE ( [Order Date].[Calendar Year].&[2005], [Order Date].[Fiscal Date].[Fiscal Year].&[2006] )
/* SETS
Set is a collection of tuples.
*/
SELECT { ( [Order Date].[Calendar Year].[CY 2005], [Geography].[Geographies].[Country-Region].&[Australia] ), ( [Order Date].[Calendar Year].[CY 2006], [Geography].[Geographies].[Country-Region].&[Germany] ), ( [Order Date].[Calendar Year].[CY 2007], [Geography].[Geographies].[Country-Region].&[United States] ), ( [Order Date].[Calendar Year].[CY 2008], [Geography].[Geographies].[Country-Region].&[France] )
} ON COLUMNS, { ([Product].[Product Line].[Road]), ([Product].[Product Line].[Mountain]), ([Product].[Product Line].[Accessory]), ([Product].[Product Line].[Components]), ([Product].[Product Line].[Touring]) } ON ROWS FROM [Analysis Services Tutorial]
// Distinct
SELECT { ( [Order Date].[Calendar Year].[CY 2008],[Geography].[Geographies].[Country-Region].[United States] )
} ON COLUMNS, DISTINCT( { ([Product].[Product Line].[Road]), ([Product].[Product Line].[Mountain]), ([Product].[Product Line].[Mountain]) } ) ON ROWS FROM [Analysis Services Tutorial]
// This:
SELECT { ([Customer].[Gender].&[F], [Customer].[Marital Status].&[S]), ([Customer].[Gender].&[M], [Customer].[Marital Status].&[S]) } ON 0, { ([Product].[Product Line].[Road]), ([Product].[Product Line].[Mountain]) } ON 1 FROM [Analysis Services Tutorial]
// gives same results as:
SELECT { ([Customer].[Gender].&[F]), ([Customer].[Gender].&[M]) } ON 0, { ([Product].[Product Line].[Road]), ([Product].[Product Line].[Mountain]) } ON 1 FROM [Analysis Services Tutorial] WHERE ([Customer].[Marital Status].&[S]) //([Geography].[Geographies].[Country-Region].[United States])
/* Axes:
0..127 Axis(0) 0 COLUMNS Axis(1) 1 ROWS PAGES SECTIONS CHAPTERS */
// Members function /* hier.Members - returns all members from all levels of hier including (All) member hier.level.Members - returns only level members
Members implicitly converts each member into a tuple consisting of 1 member reference (that's why it returns set). */
SELECT { ([Customer].[Gender].&[F]), ([Customer].[Gender].&[M]) } ON 0, { ([Product].[Product Line].[Road]), ([Product].[Product Line].[Mountain]) } ON 1 FROM [Analysis Services Tutorial] WHERE ([Geography].[Geographies].[Country-Region].[United States])
// with members:
SELECT { ([Customer].[Gender].&[F]), ([Customer].[Gender].&[M]) } ON 0, { [Product].[Product Line].Members } ON 1 FROM [Analysis Services Tutorial] WHERE ([Geography].[Geographies].[Country-Region].[United States])
// members on all levels:
SELECT { [Customer].[Gender].Members } ON 0, { [Product].[Product Line].Members } ON 1 FROM [Analysis Services Tutorial] WHERE ([Geography].[Geographies].[Country-Region].[United States])
// MeasureGroupMeasures function:
SELECT { [Customer].[Gender].Members } ON 0, { MeasureGroupMeasures("Internet Sales") } ON 1 FROM [Analysis Services Tutorial]
// Crossjoin function // Crossjoin({Set1}, {Set2}, ...) - (iloczyn kartezjañski zbiorów)
SELECT { ([Due Date].[Fiscal Date].[Fiscal Year].&[2007]), ([Due Date].[Fiscal Date].[Fiscal Year].&[2008]) } ON 0, Crossjoin( { [Product].[Product Model Lines].[Product Line].Members }, { ([Measures].[Reseller Sales Count]), ([Measures].[Reseller Sales-Sales Amount]) } ) ON 1 FROM [Analysis Services Tutorial]
// cross-join operator: *
// SELECT { ([Due Date].[Fiscal Date].[Fiscal Year].&[2007]), ([Due Date].[Fiscal Date].[Fiscal Year].&[2008]) } ON 0, { [Product].[Product Model Lines].[Product Line].Members } * { ([Measures].[Reseller Sales Count]) //,([Measures].[Reseller Sales-Sales Amount]) } DIMENSION PROPERTIES MEMBER_KEY, Key0 ON 1 FROM [Analysis Services Tutorial] //
// SELECT { ([Due Date].[Fiscal Date].[Fiscal Year].&[2007]), ([Due Date].[Fiscal Date].[Fiscal Year].&[2008]) } ON 0, NON EMPTY { [Product].[Product Model Lines].[Product Line].Members } * { ([Measures].[Reseller Sales-Sales Amount]) } DIMENSION PROPERTIES MEMBER_KEY, Key0 ON 1 FROM [Analysis Services Tutorial] //
// Limiting sets
// Perfect cross join (iloczyn kartezjañski):
SELECT { ([Due Date].[Calendar Date].[Calendar Year].&[2005]), ([Due Date].[Calendar Date].[Calendar Year].&[2006]), ([Due Date].[Calendar Date].[Calendar Year].&[2007]) } * { ([Measures].[Reseller Sales Count]), ([Measures].[Reseller Sales-Sales Amount]) } ON 0, { ([Product].[Category].[Category].Members) } ON 1 FROM [Analysis Services Tutorial] WHERE ([Geography].[Country-Region].&[United States])
// Not perfect cross join between Category and Subcategory: // Categories and Subcategories are related (hierarchy)
SELECT { ([Due Date].[Calendar Date].[Calendar Year].&[2005]), ([Due Date].[Calendar Date].[Calendar Year].&[2006]), ([Due Date].[Calendar Date].[Calendar Year].&[2007]) } * { ([Measures].[Reseller Sales Count]), ([Measures].[Reseller Sales-Sales Amount]) } ON 0, { ([Product].[Category].[Category].Members) * ([Product].[Subcategory].[Subcategory].Members) } ON 1 FROM [Analysis Services Tutorial] WHERE ([Geography].[Country-Region].&[United States])
// Exists( {Set1}, {Set2} )
SELECT { ([Order Date].[Calendar Date].[Calendar Year].&[2007]), ([Order Date].[Calendar Date].[Calendar Year].&[2010]), ([Order Date].[Calendar Date].[Calendar Year].&[2008]) } * { ([Measures].[Internet Sales-Sales Amount]), ([Measures].[Reseller Sales-Sales Amount]) } ON 0, { [Product].[Category].[Category].Members } * { [Product].[Color].[Color].Members } ON 1 FROM [Analysis Services Tutorial] WHERE ([Geography].[Country-Region].&[United States], [Product].[Product Categories].[Subcategory].[Mountain Bikes])
--
SELECT { ([Order Date].[Calendar Date].[Calendar Year].&[2007]), ([Order Date].[Calendar Date].[Calendar Year].&[2010]), ([Order Date].[Calendar Date].[Calendar Year].&[2008]) } * { ([Measures].[Internet Sales-Sales Amount]), ([Measures].[Reseller Sales-Sales Amount]) } ON 0, Exists ( { [Product].[Category].[Category].Members} * {[Product].[Color].[Color].Members }, { [Product].[Product Categories].[Subcategory].[Mountain Bikes] } ) ON 1 FROM [Analysis Services Tutorial] --WHERE --([Customer].[Location].[Country-Region].&[United States])
// NON EMPTY // A lot of nulls: SELECT { [Measures].[Reseller Sales-Sales Amount] } ON 0, { [Due Date].[Month Name].&[2008]&[June] } * {[Employee].[Employee Name].Members} ON 1 FROM [Analysis Services Tutorial]
// SELECT { [Measures].[Reseller Sales-Sales Amount] } DIMENSION PROPERTIES [MEMBER_KEY], Key0 ON 0, NON EMPTY { [Due Date].[Month Name].&[2008]&[June] } * {[Employee].[Employee Name].Members} DIMENSION PROPERTIES [MEMBER_KEY], Key0 ON 1 FROM [Analysis Services Tutorial] //
// Working with expressions
// - Except, Set // IS // : Range, Set // + Union, Set (removes duplicates)
// Calculated members
SELECT { ([Date].[Calendar Date].[Calendar Year].&[2008]), ([Date].[Calendar Date].[Calendar Year].&[2007]) } ON 0, { ([Product].[Category].[Accessories]), ([Product].[Category].[Bikes]), ([Product].[Category].[Clothing]), ([Product].[Category].[Components]) } ON 1 FROM [Analysis Services Tutorial]
WITH MEMBER [Product].[Category].[All Products].[X] AS 1 + 1 SELECT { ([Date].[Calendar Date].[Calendar Year].&[2008]), ([Date].[Calendar Date].[Calendar Year].&[2007]) } ON 0, { ([Product].[Category].[Accessories]), ([Product].[Category].[Bikes]), ([Product].[Category].[Clothing]), ([Product].[Category].[Components]) } ON 1 FROM [Analysis Services Tutorial]
WITH MEMBER [Product].[Category].[All Products].[X] AS 1 + 1 SELECT { ([Date].[Calendar Date].[Calendar Year].&[2008]), ([Date].[Calendar Date].[Calendar Year].&[2007]) } ON 0, { ([Product].[Category].[Accessories]), ([Product].[Category].[Bikes]), ([Product].[Category].[Clothing]), ([Product].[Category].[Components]), ([Product].[Category].[X]) } ON 1 FROM [Analysis Services Tutorial]
// AllMembers
WITH MEMBER [Product].[Category].[All Products].[X] AS 1 + 1 SELECT { ([Date].[Calendar Date].[Calendar Year].&[2008]), ([Date].[Calendar Date].[Calendar Year].&[2007]) } ON 0, { [Product].[Category].AllMembers } ON 1 FROM [Analysis Services Tutorial]
// Dynamic
WITH MEMBER [Product].[Category].[All Products].[Bikes And Accessories] AS ([Product].[Category].[Bikes]) + ([Product].[Category].[Accessories]) SELECT { ([Date].[Calendar Date].[Calendar Year].&[2008]), ([Date].[Calendar Date].[Calendar Year].&[2007]) } ON 0, { [Product].[Category].AllMembers } ON 1 FROM [Analysis Services Tutorial]
WITH MEMBER [Product].[Category].[All Products].[Percent Bikes] AS ([Product].[Category].[Bikes] / [Product].[Category].[All Products]), FORMAT_STRING="Percent" SELECT { ([Measures].[Internet Sales-Sales Amount]), ([Measures].[Reseller Sales-Sales Amount]) } ON 0, { [Product].[Category].AllMembers } ON 1 FROM [Analysis Services Tutorial]
// SOLVE_ORDER
WITH MEMBER [Measures].[Combined Sales Amount] AS (([Measures].[Internet Sales-Sales Amount]) + ([Measures].[Reseller Sales-Sales Amount])), SOLVE_ORDER=1
MEMBER [Product].[Category].[All Products].[Percent Bikes] AS ([Product].[Category].[Bikes] / [Product].[Category].[All Products]), FORMAT_STRING="Percent",SOLVE_ORDER=2 SELECT { [Measures].[Internet Sales-Sales Amount], [Measures].[Reseller Sales-Sales Amount], [Measures].[Combined Sales Amount] } ON 0, { [Product].[Category].AllMembers } ON 1 FROM [Analysis Services Tutorial]
// [Dimension].[Hierarchy].CurrentMember
//IIF = inline IF //IIF(logical expression, expression1, expression2)
WITH MEMBER [Measures].[Parent Name] AS [Product].[Product Categories].CurrentMember.Parent.Name SELECT {[Measures].[Parent Name]} ON 0, {[Product].[Product Categories].AllMembers} ON 1 FROM [Analysis Services Tutorial]
WITH MEMBER [Measures].[Parent Name] AS IIF( [Product].[Product Categories].CurrentMember.Properties("LEVEL_NUMBER", TYPED) = 0, "N/A", [Product].[Product Categories].CurrentMember.Parent.UniqueName ) SELECT {[Measures].[Parent Name]} ON 0, {[Product].[Product Categories].AllMembers} ON 1 FROM [Analysis Services Tutorial]
WITH MEMBER [Measures].[Parent Name] AS IIF( [Product].[Product Categories].CurrentMember.Properties("LEVEL_NUMBER", TYPED) = 0, "N/A", [Product].[Product Categories].CurrentMember.Parent.UniqueName ) MEMBER [Measures].[Percent Parent] AS ([Measures].[Reseller Sales-Sales Amount]) / ([Product].[Product Categories].CurrentMember.Parent, [Measures].[Reseller Sales-Sales Amount]), FORMAT_STRING="Percent"
SELECT { [Measures].[Parent Name], [Measures].[Reseller Sales-Sales Amount], [Measures].[Percent Parent] } ON 0, {[Product].[Product Categories].AllMembers} ON 1 FROM [Analysis Services Tutorial]
WITH MEMBER [Measures].[Parent Name] AS IIF( [Product].[Product Categories].CurrentMember.Properties("LEVEL_NUMBER", TYPED) = 0, "N/A", [Product].[Product Categories].CurrentMember.Parent.UniqueName ) MEMBER [Measures].[Percent Parent] AS IIF( [Product].[Product Categories].CurrentMember.Properties("LEVEL_NUMBER", TYPED) = 0, "N/A", ([Measures].[Reseller Sales-Sales Amount]) / ([Product].[Product Categories].CurrentMember.Parent, [Measures].[Reseller Sales-Sales Amount]) ) , FORMAT_STRING="Percent" SELECT { [Measures].[Parent Name], [Measures].[Reseller Sales-Sales Amount], [Measures].[Percent Parent] } ON 0, {[Product].[Product Categories].AllMembers} ON 1 FROM [Analysis Services Tutorial]
// ---------------------- // Special member functions // [Dimension].[Hierarchy].DefaultMember - All, first // [Dimension].[Hierarchy].UnknownMember - Null, error // [Dimension].[Hierarchy].DataMember
// EXISTING - restrict per current context
WITH MEMBER [Measures].[Products Count] AS Count(EXISTING [Product].[Model Name].[Model Name].Members) SELECT { [Measures].[Reseller Sales-Sales Amount], [Measures].[Products Count] } ON 0, { [Product].[Category].[Category].Members } ON 1 FROM [Analysis Services Tutorial]