Unlocking the Mystery of MDX

Unlocking the
Mystery of MDX
Bryan Smith
brysmi@microsoft.com

Objectives
Obj ti
Explain h d behind
E l i the modell b hi d MDX
Explore lessons learned

The i
Th views and opinions expressed in this ....
d ii d i thi

(3)

x
-4 -3 -2 -1 0 1 2 3 4

y

4
(3, 2)

3
2
1
x
-4 -3 -2 -1 1 2 3 4

-2
-3
-4

y

4
(3, 2, 4)

3
2
1
x
-4 -3 -2 -1 1 2 3 4

-2
z
-3
-4

one-dimensional (x) single

two-dimensional (x, y) double

three-dimensionall
h di i (x, )
( y, z) triple
il

(a1, a2, a3, a4)
four-dimensional (x, y, z, ?) quadruple

four-dimensional
f di i l (a
( 1 , a2 , a3 , a4 ) quadruple
d l

five-dimensional (a1, a2, a3, a4, a5) quintuple

six-dimensional (a1, a2, a3, a4, a5, a6) sextuple

seven-dimensional (a1, a2, a3, a4, a5, a6, a7) septuple

eight dimensional
eight-dimensional (a1, a2, a3, a4, a5, a6, a7, a8) octuple

four-dimensional
four dimensional (a1, a2, a3, a4) quadruple

five-dimensional (a1, a2, a3, a4, a5) quintuple

six-dimensional (a1, a2, a3, a4, a5, a6) sextuple

seven-dimensional (a1, a2, a3, a4, a5, a6, a7) septuple

eight dimensional
eight-dimensional (a1, a2, a3, a4, a5, a6, a7, a8) octuple

n-dimensional (a1, a2, … an) tuple

My Simple Cube

Measure Group

Σ Measure

Dimension

Attribute

My Simple Cube

Reseller Sales Internet Sales

6 Σ Reseller Sales Amount Σ Internet Sales Amount

Date Product Geography
1 Fiscal Year 3 Category 5 Country

2 Calendar Year 4 Subcategory

Product
P d t
Category

All Products

Accessories Bikes Clothing Components

Components
C
([Bikes])

Clo
othing
Bike
es
Accessories
A
Alll Products
Category

[Product].[Category]
P d t
Product

( (
a1,
[CY 2002], [Date].[Calendar Year].[CY 2002],
[All Periods],
a2, [Date].[Fiscal Year].[All Periods],
[Bikes],
a3, [Product].[Category].[Bikes],
[All Products],
a4, [Product].[Subcategory].[All Products],
[All Geographies],
a5, [Geography].[Country].[All Geographies],
[Reseller Sales Amount]
a6 [Measures].[Reseller Sales Amount]
) )

(
[Geography].[Country].[All Geographies],
[Product].[Category].[Bikes],
[Measures].[Reseller Sales Amount],
[ ][
[Date].[Fiscal Year].[All Periods],
][ ]
[Product].[Subcategory].[All Products],
[Date].[Calendar Year].[CY 2002]
)

(
( [Date].[Calendar Year].[CY 2002],
[Date].[Calendar Y ] [CY
[D ] [C l d Year].[CY 2002], ] [ ][
[Date].[Fiscal Year].[All Periods],
][ ],
[Product].[Category].[Bikes] [Product].[Category].[Bikes]
) [Product].[Subcategory].[All Products],
[Geography].[Country].[All Geographies],
[Measures].[Reseller Sales Amount]
)

Attributes Partial Rule 1 Rule 2 Rule 3 Completed
Tuple Default Member (All) Member First Member Tuple

Date.Calendar Year CY 2002 CY 2002
Date.Fiscal Year All Periods All Periods
Product.Category Bikes Bikes
Product.Subcategory All Products All Products
Geography.Country All Geographies All Geographies
Measures Reseller Sales Amount Reseller Sales Amount

SELECT
[Date].[Calendar Year].Members ON COLUMNS,
[Product].[Category].Members ON ROWS
FROM [My Simple Cube]

All Periods CY 2001 CY 2002 CY 2003 CY 2004
All Products $80,450,597 $8,065,435 $24,144,430 $32,202,669 $16,038,063
Accessories $571,298 $20,235 $92,735 $296,533 $161,794
Bikes $66,302,382 $7,395,349 $19,956,015 $25,551,775 $13,399,243
Clothing $1,777,840 $34,376 $485,587 $871,864 $386,013
Components $11,799,077 $34,376 $3,610,092 $5,482,497 $2,091,012

MDX
SELECT
[Date].[Calendar Y ] M b
[D ] [C l d Year].Members ON COLUMNS,
COLUMNS
[Product].[Category].Members ON ROWS
FROM [My Simple Cube]

SQL
SELECT
e.CalendarYear,
d.EnglishProductCategoryName,
SUM(a.SalesAmount)
FROM FactResellerSales a
INNER JOIN DimProduct b
ON a.ProductKey=b.ProductKey
d b d
INNER JOIN DimProductSubcategory c
ON b.ProductSubcategoryKey=c.ProductSubcategoryKey
INNER JOIN DimProductCategory d
ON c.ProductCategoryKey=d.ProductCategoryKey
INNER JOIN DimDate e
ON a.OrderDateKey=e.DateKey
GROUP BY
e.CalendarYear,
d.EnglishProductCategoryName

MDX
WITH MEMBER [Measures].[Total Sales Amount] AS
[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]
[M ] [I Sl A ] [M ] [R ll S l A ]
SET [Top 10 Products of 2003] AS
TOPCOUNT(
[Product].[Product].[Product].Members, 10,
([Measures].[Total Sales Amount], [Date].[Calendar Year].[CY 2003])
)
SELECT
[Measures].[Total Sales Amount] ON COLUMNS,
[Top 10 Products of 2003] ON ROWS
FROM [Step-by-Step]
WHERE ([Date].[Calendar Year].[CY 2004])

SQL
SELECT
m.EnglishProductName,
o.TotalSalesAmount
FROM dbo.DimProduct m
INNER JOIN ( -- TOP 10 PRODUCTS OF 2003
SELECT TOP 10
a.ProductKey, SUM(a.SalesAmount) AS TotalSalesAmount
d ( l ) l l
FROM (
SELECT
x.productkey, x.salesamount
FROM dbo.FactInternetSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey=y.DateKey
WHERE y.CalendarYear=2003
UNION ALL
SELECT
x.productkey, x.salesamount

Lessons Learned
L L d
Clearly d fi
Cl l define audience
di
Align technology with audience
Actively engage audience

Thank You!
brysmi@microsoft.com

Unlocking the Mystery of MDX

Recommended

Recommended

More Related Content

Viewers also liked

Viewers also liked (20)

More from DATAVERSITY

More from DATAVERSITY (20)

Recently uploaded

Recently uploaded (20)

Unlocking the Mystery of MDX