CLASS ROOM MDX QUERIES (FEW)

MDX QUERIES

SELECT FROM [Adventure Works DW2012]

 

SELECT [Due Date].[Calendar Year] ON COLUMNS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS

FROM [Adventure Works DW2012]

SELECT [Due Date].[Calendar Year].[Calendar Year] ON COLUMNS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Calendar Year].[Calendar Year] ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name] ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].MEMBERS ON COLUMNS,

[Due Date].[Calendar Year].[Calendar Year] ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].ALLMEMBERS ON COLUMNS,

[Due Date].[Calendar Year].[Calendar Year] ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].ALLMEMBERS ON COLUMNS,

[Due Date].[Calendar Year].MEMBERS ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].ALLMEMBERS ON COLUMNS,

[Due Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Calendar Year].[Calendar Year].ALLMEMBERS ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

[Due Date].[Calendar Year].[Calendar Year] ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

{[Due Date].[Hierarchy].[Calendar Year].&[2005],[Due Date].[Hierarchy].[Calendar Year].&[2006]} ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Due Date].[Calendar Year].&[2007],[Due Date].[Calendar Year].&[2008]} ON COLUMNS,

{[Measures].[Sales Amount],[Measures].[Tax Amt]}ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

({[Due Date].[Calendar Year].&[2007],[Due Date].[Calendar Year].&[2008]},

[Dim Product].[Color].[Color]) ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

[Due Date].[Hierarchy].[Calendar Year].&[2006].CHILDREN ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

[Due Date].[Hierarchy].[Calendar Year].&[2006].CHILDREN.CHILDREN ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name].&[2007]&[June].PARENT ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name].&[2007]&[June].PARENT.PARENT.PARENT ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name].&[2007]&[June].PARENT.PARENT.PARENT.PARENT

ON ROWS FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],

[Due Date].[Hierarchy].[English Month Name]) ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],

[Due Date].[Hierarchy].[Calendar Quarter]) ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],1) ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],5) ON ROWS

FROM [Adventure Works DW2012]

  Level 3/4/5/6….Result is Same

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON COLUMNS,

ANCESTORS([Due Date].[Hierarchy].[English Month Name].&[2007]&[February],[Due Date].[Hierarchy].[Calendar Semester]) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

ANCESTORS([Due Date].[Hierarchy].[English Month Name].&[2007]&[OCTOBER],1) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

ANCESTORS([Due Date].[Hierarchy].[English Month Name].&[2007]&[OCTOBER],0) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

ASCENDANTS([Due Date].[Hierarchy].[English Month Name].&[2007]&[OCTOBER]) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

HIERARCHIZE(ASCENDANTS([Due Date].[Hierarchy].[English Month Name].&[2007]&[OCTOBER]))

ON ROWS FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name].&[2007]&[OCTOBER].PARENT.CHILDREN ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name].&[2007]&[OCTOBER].SIBLINGS ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name].&[2007]&[OCTOBER].PARENT.FIRSTCHILD ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name].&[2007]&[OCTOBER].PARENT.LASTCHILD ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name].&[2007]&[OCTOBER].PARENT.FIRSTSIBLING ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name].&[2007]&[OCTOBER].PARENT.LASTSIBLING ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],1,SELF) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],2,SELF) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],2,SELF_AND_BEFORE) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Semester].&[2008]&[1],2,SELF_AND_BEFORE) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],0,SELF_AND_AFTER) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],2,BEFORE_AND_AFTER) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],2,BEFORE) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

DESCENDANTS([Due Date].[Hierarchy].[Calendar Year].&[2007],2,AFTER) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

{[Due Date].[Hierarchy].[Calendar Year].&[2005]:[Due Date].[Hierarchy].[Calendar Year].&[2008]} ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

{NULL:[Due Date].[Hierarchy].[Calendar Year].&[2008]} ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

{[Due Date].[Hierarchy].[Calendar Year].&[2005]:NULL} ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[Calendar Year].&[2007].LAG(2) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[Calendar Year].&[2007].LAG(-2) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[Calendar Year].&[2007].LEAD(2) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[Calendar Year].&[2007].LEAD(-2) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[English Month Name].&[2007]&[August].LEAD(-3):[Due Date].[Hierarchy].[English Month Name].&[2007]&[August].LEAD(3) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

HEAD([Due Date].[Hierarchy].[Calendar Year],3)ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

TAIL([Due Date].[Hierarchy].[Calendar Year],3) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[Calendar Year].&[2008].PREVMEMBER ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[Hierarchy].[Calendar Year].&[2008].NEXTMEMBER ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

ORDER([Due Date].[English Month Name].[English Month Name],[Measures].[Sales Amount],DESC) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

ORDER(NONEMPTY([Due Date].[English Month Name].[English Month Name]),[Measures].[Sales Amount],ASC) ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

[Due Date].[English Month Name].[English Month Name] ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

NON EMPTY [Due Date].[English Month Name].[English Month Name] ON ROWS

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON COLUMNS,

NONEMPTY ([Due Date].[English Month Name].[English Month Name]) ON ROWS

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,

EXCEPT([Due Date].[Calendar Year].[Calendar Year],{[Due Date].[Calendar Year].&[2006],[Due Date].[Calendar Year].&[2009]}) ON 1

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,

([Due Date].[Calendar Year].[Calendar Year]-{[Due Date].[Calendar Year].&[2006],[Due Date].[Calendar Year].&[2009]}) ON 1

FROM [Adventure Works DW2012]

SELECT FROM [Adventure Works DW2012] WHERE [Due Date].[Calendar Year].&[2005]

SELECT [Measures].[Sales Amount] ON 0

FROM [Adventure Works DW2012] WHERE [Due Date].[Calendar Year].&[2005]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,

NONEMPTY(([Dim Product].[Color].[Color],[Dim Product].[English Product Name].[English Product Name])) ON 1

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,

NON EMPTY([Dim Product].[Color].[Color],[Dim Product].[English Product Name].[English Product Name])

ON 1

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,

TOPCOUNT([Due Date].[Calendar Year].[Calendar Year],3) ON 1

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,

TOPCOUNT([Due Date].[Calendar Year].[Calendar Year],3,[Measures].[Sales Amount]) ON 1

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,

BOTTOMCOUNT([Due Date].[Calendar Year].[Calendar Year],3) ON 1

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,

BOTTOMCOUNT([Due Date].[Calendar Year].[Calendar Year],3,[Measures].[Sales Amount]) ON 1

FROM [Adventure Works DW2012]

SELECT {[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,

BOTTOMCOUNT(NONEMPTY([Due Date].[Calendar Year].[Calendar Year]),3,[Measures].[Sales Amount]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

PERIODSTODATE([Due Date].[Hierarchy].[Calendar Year],[Due Date].[Hierarchy].[English Month Name].&[2008]&[May]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount]*[Due Date].[Calendar Year].[Calendar Year]

*[Due Date].[English Month Name].[English Month Name]ON 0

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

PERIODSTODATE([Due Date].[Hierarchy].[Calendar Quarter],[Due Date].[Hierarchy].[English Month Name].&[2007]&[NOVEMBER]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

LASTPERIODS(5,[Due Date].[Hierarchy].[English Month Name].&[2008]&[MARCH])ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

LASTPERIODS(-5,[Due Date].[Hierarchy].[English Month Name].&[2008]&[MARCH])ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

OPENINGPERIOD([Due Date].[Hierarchy].[English Month Name]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

OPENINGPERIOD([Due Date].[Hierarchy].[Calendar Year]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

CLOSINGPERIOD([Due Date].[Hierarchy].[Calendar Year]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

OPENINGPERIOD([Due Date].[Hierarchy].[English Month Name],[Due Date].[Hierarchy].[Calendar Year].&[2007]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

OPENINGPERIOD([Due Date].[Hierarchy].[English Month Name],[Due Date].[Hierarchy].[Calendar Semester].&[2007]&[2]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

CLOSINGPERIOD([Due Date].[Hierarchy].[English Month Name],[Due Date].[Hierarchy].[Calendar Year].&[2007]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

YTD([Due Date].[Hierarchy].[Calendar Year].&[2008]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

YTD([Due Date].[Hierarchy].[English Month Name].&[2006]&[August]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

YTD([Due Date].[Hierarchy].[Calendar Quarter].&[2008]&[3]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

QTD([Due Date].[Hierarchy].[English Month Name].&[2006]&[OCTOBER]) ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

QTD ([Due Date].[Hierarchy].[English Month Name].&[2006]&[November].&[2006-11-07T00:00:00])ON 1

FROM [Adventure Works DW2012]

SELECT [Measures].[Sales Amount] ON 0,

MTD ([Due Date].[Hierarchy].[English Month Name].&[2006]&[November].&[2006-11-07T00:00:00])ON 1

FROM [Adventure Works DW2012]