/***********************************************
 HAILING FREQUENCIES
***********************************************/

/* Just 1 axis */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
FROM [Borg]

/************************************************/

/* 2 axes */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].[Star Trek: The Original Series] ON ROWS
FROM [Borg]

/************************************************/

/* 2 axes using numeric shorthand instead of axis aliases */

SELECT
	  [Measures].[Episode Count] ON 0 -- COLUMNS
	, [Series].[Series Name].[Star Trek: The Original Series] ON 1 -- ROWS
FROM [Borg]

/************************************************/

/* VIEW MEMBER PROPERTIES TO GET ID OF MEMBER */

/* 2 axes using "&" with member ID value */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].&[1] ON ROWS
FROM [Borg]

/************************************************/

/* Adding in the Slicer Axis */

/* First, adding in more Rows */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].members ON ROWS
FROM [Borg]

/* Now add slicer */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].members ON ROWS
FROM [Borg]
WHERE [Date].[Date Hierarchy].[Calendar Year].[1966]

/* NOTE: Same rows/columns returned. Just values returned changed */

/***********************************************
 THE TROUBLE WITH TUPLES DEMO
***********************************************/

/* The basic tuple. References EVERY dimension in the cube. */
/* Surrounded by ( ) when more than one hierarchy is specified */

SELECT
	 (
		  [Series].[Series Name].[Star Trek: The Original Series]
		, [Season].[Season Name].[All]
		, [Date].[Date Hierarchy].[All]
		, [Measures].[Episode Count]
	  ) ON COLUMNS
FROM [Borg]

/************************************************/

/* Partial tuple from Hailing Frequencies Demo */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].[Star Trek: The Original Series] ON ROWS
FROM [Borg]

/* NOTE this returns the same cell */

/***********************************************
 A SHIRT OF A DIFFERENT COLOR: SETS DEMO
***********************************************/

/* Basic set with single-part tuples */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, {
		  [Series].[Series Name].[Star Trek: The Original Series] -- Tuple A
		, [Series].[Series Name].[Star Trek: The Next Generation] -- Tuple B
	  } ON ROWS
FROM [Borg]

/************************************************/

/* Basic set with multi-part tuples */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, {
		  ([Series].[Series Name].[Star Trek: The Original Series]
			, [Season].[Season Name].[Season 01]) -- Tuple A
		, ([Series].[Series Name].[Star Trek: The Next Generation]
			, [Season].[Season Name].[Season 01]) -- Tuple B
	  } ON ROWS
FROM [Borg]

/************************************************/

/* Named set */

WITH SET MyFavorites AS
{
	  [Series].[Series Name].[Star Trek: The Original Series] -- Tuple A
	, [Series].[Series Name].[Star Trek: The Next Generation] -- Tuple B
}
SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, MyFavorites ON ROWS
FROM [Borg]

/***********************************************
WARP 9 DEMO 1
***********************************************/

/* Calculated Member */

WITH MEMBER CountPlus2 AS
	[Measures].[Episode Count] + 2
SELECT
	  {[Measures].[Episode Count],CountPlus2} ON COLUMNS
	, [Series].[Series Name].[Star Trek: The Original Series] ON ROWS
FROM [Borg]

/************************************************/

/* The Range operator ":" */

WITH SET TheEighties AS
{
	  (
	  [Date].[Date Hierarchy].[Calendar Year].[1980] -- Start
	  :[Date].[Date Hierarchy].[Calendar Year].[1989] -- End
	  )
}
SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, TheEighties ON ROWS
FROM [Borg]

/************************************************/

/* NON EMPTY Keyword */

WITH SET TheEighties AS
{
	  (
	  [Date].[Date Hierarchy].[Calendar Year].[1980]
	  :[Date].[Date Hierarchy].[Calendar Year].[1989]
	  )
}
SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, NON EMPTY TheEighties ON ROWS
FROM [Borg]

/***********************************************
WARP 9 DEMO 2
***********************************************/

/* .members on Attribute Hierarchy */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].members ON ROWS
FROM [Borg]

/* Note the inclusion of the [All] member */

/************************************************/

/* .members on User Hierarchy, without specifying level*/

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, NON EMPTY [Date].[Date Hierarchy].members ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note ENTIRE hierarchy is flattenned out and returned in order */

/************************************************/

/* .members on User Hierarchy, specifying level*/

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, NON EMPTY [Date].[Date Hierarchy].[Calendar Year].members ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note just the specified level's members are returned */

/***********************************************
WARP 9 DEMO 3
***********************************************/

/* .children on Attribure Hierarchy */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].children ON ROWS
FROM [Borg]

/* Note the exclusion of the [All] member */
/* [All] member is treated as the input member */

/* See? I'm totally not making that up */
SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].[All].children ON ROWS
FROM [Borg]

/************************************************/

/* .children on User Hierarchy, without specifying level*/

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, NON EMPTY [Date].[Date Hierarchy].children ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note the exclusion of the [All] member */

/************************************************/

/* .children on User Hierarchy, specifying member */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Date].[Date Hierarchy].[Calendar Year]
		.[1987].children ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note the Calendar Quarter values within 1987 are returned */

/************************************************/

/* .parent on Attribute Hierarchy */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name]
		.[Star Trek: The Next Generation].parent ON ROWS
FROM [Borg]

/* Note this just returend the [All] member */

/************************************************/

/* .parent on User Hierarchy */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Date].[Date Hierarchy]
		.[Calendar Quarter].[1987-Q3].parent ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note the Calendar Year 1987 is returned */

/************************************************/

/* .siblings on Attribute Hierarchy */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].[Star Trek: The Next Generation]
		.siblings ON ROWS
FROM [Borg]

/* Note [Star Trek: The Next Generation] and all of its siblings */

/************************************************/

/* .siblings on User Hierarchy */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q3]
		.siblings ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note all children of Calendar Year 1987 are returned
ie: 1987-Q3 and all of its siblings */

/************************************************/

/* .firstchild on Attribute Hierarchy */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].firstchild ON ROWS
FROM [Borg]

/* Note [Star Trek: The Original Series] is returned */

/************************************************/

/* .firstchild on User Hierarchy */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q4]
		.firstchild ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note [Calendar Month].[1987-10] is returned
which is the first month of [1987-Q4] */

/************************************************/

/* .lastchild on Attribute Hierarchy */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Series].[Series Name].lastchild ON ROWS
FROM [Borg]

/* Note [Star Trek: Enterprise] is returned */

/************************************************/

/* .lastchild on User Hierarchy */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q3]
		.lastchild ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note [Calendar Month].[1987-09] is returned
which is the last month of [1987-Q3] */

/***********************************************
WARP 9 DEMO 4
***********************************************/

/* CROSSJOIN Function */

SELECT
	  NON EMPTY [Season].[Season Name].children 
		* [Measures].[Episode Count] ON 0
	, [Series].[Series Name].children ON 1
FROM [Borg]

/* NOTE 7 Seasons return * 5 Series yields 35 result Cells */

/************************************************/

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Date].[Date Hierarchy].[Calendar Month].members
		* [Date].[Calendar Year].[1966] ON ROWS
FROM [Borg]

/* NOTE Two Hierarchies in same dimension only yield
Tuples that actually exist */

/***********************************************
WARP 9 DEMO 5
***********************************************/

/* .lead() */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Date].[Date Hierarchy].[Calendar Month].[1989-01].lead(3) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note [Calendar Month].[1989-04] is returned
which is 3 months later than [1989-01] */

/************************************************/

/* .lag() */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, [Date].[Date Hierarchy].[Calendar Month].[1989-01].lag(1) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note [Calendar Month].[1988-12] is returned
which is 1 month earlier than [1989-01] */

/************************************************/

/* ParallelPeriod() Example 1 */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, ParallelPeriod(
		  [Date].[Date Hierarchy].[Calendar Year] -- Level to traverse
		, 1 -- How many members to go back on the Level
		, [Date].[Date Hierarchy].[Calendar Month].[1990-01] -- Where to start
		) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note [Calendar Month].[1989-01] is returned
which is 1 year earlier than [1989-01] */

/************************************************/

/* ParallelPeriod() Example 2*/

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, ParallelPeriod(
		  [Date].[Date Hierarchy].[Calendar Quarter] -- Level to traverse
		, 1 -- How many members to go back on the Level
		, [Date].[Date Hierarchy].[Calendar Month].[1990-01] -- Where to start
		) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note [Calendar Month].[1989-10] is returned
which is 1 quarter earlier than [1990-01] */

/************************************************/

/* PeriodsToDate() */

SELECT
	  [Measures].[Episode Count] ON COLUMNS
	, PeriodsToDate(
		  [Date].[Date Hierarchy].[Calendar Year] -- Period to encompass
		, [Date].[Date Hierarchy].[Calendar Month].[1990-04] -- Where to start
		) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

/* Note each of the Calendar Months up to and including
[Calendar Month].[1990-04] are returned */



