MSBI-MDX CLASS ROOM QUERIES

SELECT FROM [cb_Adventure Works DW2012]
---
SELECT [Measures].[Fact Internet Sales Count] ON COLUMNS
FROM [cb_Adventure Works DW2012]
---
SELECT
{
[Measures].[Sales Amount],
[Measures].[Tax Amt]
}
 ON 0
FROM [cb_Adventure Works DW2012]
----
WITH
MEMBER DIFF AS [Measures].[Sales Amount]-[Measures].[Tax Amt]
MEMBER CONTVAL AS 100
SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt],DIFF,CONTVAL} ON 0
FROM [cb_Adventure Works DW2012]
----
WITH
MEMBER DIFF AS [Measures].[Sales Amount]-[Measures].[Tax Amt]
MEMBER CONTVAL AS 100
MEMBER PHONE_CNT AS COUNT([Dim Customer].[Email Address].CHILDREN)
SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt],DIFF,CONTVAL,PHONE_CNT} ON 0
FROM [cb_Adventure Works DW2012]
---
/*
WITH
MEMBER DIFF AS [Measures].[Sales Amount]-[Measures].[Tax Amt]
MEMBER CONTVAL AS 100
MEMBER PHONE_CNT AS COUNT([Dim Customer].[Email Address].CHILDREN)
*/
SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
[Dim Product].[Product Key] ON 1
FROM [cb_Adventure Works DW2012]
----

SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
[Dim Product].[Product Key].CHILDREN ON 1
FROM [cb_Adventure Works DW2012]
---

SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
NONEMPTY([Dim Product].[Product Key].CHILDREN) ON 1
FROM [cb_Adventure Works DW2012]
---

SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
NONEMPTY([Dim Product].[Product Key].MEMBERS) ON 1
FROM [cb_Adventure Works DW2012]
--

SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
ORDER(NONEMPTY([Dim Product].[Product Key].CHILDREN),
 [Measures].[Sales Amount],DESC)ON 1
FROM [cb_Adventure Works DW2012]
---
SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
TOPCOUNT(ORDER(NONEMPTY([Dim Product].[Product Key].CHILDREN),
 [Measures].[Sales Amount],DESC),5)  ON 1
FROM [cb_Adventure Works DW2012]
----
SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
HEAD(ORDER(NONEMPTY([Dim Product].[Product Key].CHILDREN),
 [Measures].[Sales Amount],DESC),5)  ON 1
FROM [cb_Adventure Works DW2012]
--
SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
BOTTOMCOUNT(ORDER(NONEMPTY([Dim Product].[Product Key].CHILDREN),
 [Measures].[Sales Amount],DESC),5)  ON 1
FROM [cb_Adventure Works DW2012]
--
SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
TAIL(ORDER(NONEMPTY([Dim Product].[Product Key].CHILDREN),
 [Measures].[Sales Amount],DESC),5)  ON 1
FROM [cb_Adventure Works DW2012]
--

SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
(
[Dim Product].[Product Key].CHILDREN,
[Dim Customer].[First Name].CHILDREN
)
 ON 1
FROM [cb_Adventure Works DW2012]
--
SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
CROSSJOIN(
[Dim Product].[Product Key].CHILDREN,
[Dim Customer].[First Name].CHILDREN
)
 ON 1
FROM [cb_Adventure Works DW2012]
--
SELECT
{[Measures].[Sales Amount],[Measures].[Tax Amt]} ON 0,
([Dim Product].[Product Key].CHILDREN) * ([Dim Customer].[First Name].CHILDREN)
)
 ON 1
FROM [cb_Adventure Works DW2012]
---

SELECT
[Measures].[Internet Sales-Sales Amount] ON 0,
(
[Product].[Product Name].children,
[Date].[Calendar Year].children
)
 ON 1
FROM [Analysis Services Tutorial]
---
SELECT
[Measures].[Internet Sales-Sales Amount] ON 0,
(
[Product].[Product Name].children,
[Date].[Calendar Year].&[2005]
)
 ON 1
FROM [Analysis Services Tutorial]
--
SELECT
[Measures].[Internet Sales-Sales Amount] ON 0,
(
[Product].[Product Name].children,
{[Date].[Calendar Year].&[2005],[Date].[Calendar Year].&[2008]}
)
 ON 1
FROM [Analysis Services Tutorial]
---

SELECT
[Measures].[Internet Sales-Sales Amount] ON 0,
(
[Product].[Product Name].children,
[Date].[Calendar Date].[Calendar Year].[2005]:[2008]
)
 ON 1
FROM [Analysis Services Tutorial]
---

SELECT
[Measures].[Internet Sales-Sales Amount] ON 0

FROM [Analysis Services Tutorial]
where (
[Product].[Product Name].children,
[Date].[Calendar Date].[Calendar Year].[2005]:[2008]
)
--

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

[Date].[Calendar Date].[Calendar Year].&[2005] on 1

FROM [Analysis Services Tutorial]
where [Date].[Calendar Date].[Calendar Year].&[2005]

--failed query
---------------------------------
SELECT
[Measures].[Internet Sales-Sales Amount] ON 0,
[Product].[Product Name].firstchild on 1

FROM [Analysis Services Tutorial]

---
SELECT
[Measures].[Internet Sales-Sales Amount] ON 0,
[Product].[Product Name].lastchild on 1

FROM [Analysis Services Tutorial]
---
SELECT [Measures].[Internet Sales-Sales Amount] ON 0,
FILTER([Product].[Model Name].MEMBERS,
VBA!INSTR(1,[Product].[Model Name].CURRENTmember.NAME,'Gloves')>0) ON 1
from [Analysis Services Tutorial]
---
SELECT [Measures].[Internet Sales-Sales Amount] ON 0,
 filter([Product].[Product Name].children,
 [Measures].[Internet Sales-Sales Amount]>90000) ON 1
from [Analysis Services Tutorial]
--