Here
are some practice queries using the DWSimpleSales Cube that are commented out
that you can copy and paste, retype, and/or use as a guide to drag and drop to
recreate.
//MDX
Example With Optional Syntax Items
SELECT
{
[Measures].[SalesDollars] } ON COLUMNS,
{
[DimProducts].[Product].[Prod1] } ON ROWS
FROM [DWSimpleSales]
WHERE ( [DimSalesDates].[SalesYear].&[2015]
);
//MDX
Example Without Optional Syntax Items (compare to statement directly above)
SELECT
Measures.SalesDollars
ON COLUMNS,
--Curly
braces { } are not required but recommended as they define result set
DimProducts.Product.Prod1
ON ROWS
--Square
brackets [ ] are not required but recommended as they define object identifier
FROM DWSimpleSales
--Parentheses
( ) are not required but recommended as they define function parameters or
coordinate set
WHERE DimSalesDates.SalesMonth
--Semicolon
; is not required but recommended as it defines statement end and prevents
"red squiggly line" on next SELECT statement (see below)
//More
MDX Syntax Examples
--Optional
WHERE Clause
SELECT
[Measures].[SalesUnits]
ON COLUMNS,
[DimProducts].[Product].[Prod1]
ON ROWS
FROM [DWSimpleSales]
--Not
Whitespace Sensitive (Not recommended to write a statement like this; just
shows that if inadvertent white spaces are included, it will not affect the results)
SELECT [Measures].[SalesUnits]
ON COLUMNS ,
[DimProducts]. [Product].
[Prod1]
ON ROWS
FROM
[DWSimpleSales]
--Not
Case Sensitive (Again, not recommended to write a statement like this; just
shows that if inadvertent case mistakes are made, it will not affect the
results)
sElEcT [mEaSuReS].[sAlEsUnItS]
oN cOlUmNs,
[dImPrOdUcTs].[pRoDuCt].[pRoD1]
oN rOwS
fRoM [dwSIMPLESales]
//DefaultMember
Function Examples
SELECT
FROM [DWSimpleSales];
--Different
statement will yield same results
SELECT
{
[Measures].DefaultMember } ON COLUMNS,
{(--Parentheses ( ) are required to define a coordinate set
[DimProducts].[Product].DefaultMember
,[DimSales].[SalesId].DefaultMember
,[DimSalesDates].[SalesYear].DefaultMember)} ON ROWS
FROM [DWSimpleSales];
//All
Function vs. DefaultMember Function Examples
SELECT
{
[Measures].DefaultMember
--All
Member cannot be used on the Measures dimension
} ON COLUMNS,
{(--Again, parentheses ( ) are required to define a coordinate
set
[DimProducts].[All]
,[DimSales].[All]
,[DimSalesDates].[All]
)} ON ROWS
FROM [DWSimpleSales];
--Since
[SalesDollars] is the default member attribute in the cube, this statement will
yield the same results
SELECT
{[Measures].[SalesDollars]}
ON COLUMNS,
{(
[DimProducts].[All]
,[DimSales].[All]
,[DimSalesDates].[All]
)} ON ROWS
FROM [DWSimpleSales];
//Key
vs. Name Column Identifier Examples
--Using
Attribute Key Identifier
SELECT
{
[Measures].[SalesUnits] } ON COLUMNS,
{ --Dimension.Hierarchy.Level.Member by Key
[DimProducts].[Product].&[1]
} ON ROWS
FROM [DWSimpleSales];
--Using
Attribute Name Column Identifier
SELECT
{
[Measures].[SalesUnits] } ON COLUMNS,
{ --Dimension.Hierarchy.Level.Member by Name
[DimProducts].[Product].[Prod1]
-- Using Name
} ON ROWS
FROM [DWSimpleSales];
//Positions
In Queries
SELECT
{
[Measures].[SalesUnits] } ON COLUMNS,
{
[DimProducts].[Product].&[Prod2] } ON ROWS
FROM [DWSimpleSales];
--Alternate
query using Axis keys and keywords instead of ON COLUMNS and ON ROWS keywords
SELECT
{
[Measures].[SalesUnits] } ON AXIS(0),
{
[DimProducts].[Product].&[Prod2] } ON AXIS(1)
FROM [DWSimpleSales];
--Axis
collection uses parentheses (like Visual Basic code) not brackets [like C#
code]
--Alternate
query using only Axis keys without Axis keywords
SELECT
{
[Measures].[SalesUnits] } ON 0,
{
[DimProducts].[Product].&[Prod2] } ON 1
FROM [DWSimpleSales];
//Ordinal
Positions Cannot Be Skipped In Axes
--Query
will generate error because axis 0 is not included
SELECT
{
[DimProducts].[Product] } ON 1
FROM [DWSimpleSales];
--Message:
"Axis numbers specified in a query must be sequentially specified, and
cannot contain gaps."
--Query
will execute without error because axis 0 is included
SELECT
{ [DimProducts].[Product]
} ON 0
FROM [DWSimpleSales];
--Results:
All 51.84
Now
find other MDX query examples on the Internet or in books and attempt to create
them using the DWSimpleSales cube.
Writing and studying MDX queries is the best way to learn and understand MDX.
No comments:
Post a Comment