Download - Unlocking the Mystery of MDX
![Page 2: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/2.jpg)
![Page 3: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/3.jpg)
Obj tiObjectives
E l i h d l b hi d MDXExplain the model behind MDX
Explore lessons learned
![Page 4: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/4.jpg)
Th i d i i d i thiThe views and opinions expressed in this ....
![Page 5: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/5.jpg)
(3)
1 2 3 4-1-2-3-4x
0
![Page 6: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/6.jpg)
y
(3, 2)34
12
1 2 3 4-1-2-3-4x
-2-3
-4
![Page 7: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/7.jpg)
y
(3, 2, 4)34
12
-2-3 1 2 3 4-1-4x
z-2
-3-4
![Page 8: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/8.jpg)
(x) singleone-dimensional
(x, y) doubletwo-dimensional
( ) i lh di i l (x, y, z) triplethree-dimensional
four-dimensional (x, y, z, ?) quadruple
(a1, a2, a3, a4)
![Page 9: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/9.jpg)
( ) d lf di i l (a1, a2, a3, a4) quadruplefour-dimensional
(a1, a2, a3, a4, a5) quintuplefive-dimensional
(a1, a2, a3, a4, a5, a6) sextuplesix-dimensional
(a a a a a a a a ) octupleeight-dimensional
(a1, a2, a3, a4, a5, a6, a7) septupleseven-dimensional
(a1, a2, a3, a4, a5, a6, a7, a8) octupleeight dimensional
![Page 10: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/10.jpg)
(a a a a ) quadruplefour dimensional (a1, a2, a3, a4) quadruplefour-dimensional
(a1, a2, a3, a4, a5) quintuplefive-dimensional
(a1, a2, a3, a4, a5, a6) sextuplesix-dimensional
(a a a a a a a a ) octupleeight-dimensional
(a1, a2, a3, a4, a5, a6, a7) septupleseven-dimensional
(a1, a2, a3, a4, a5, a6, a7, a8) octupleeight dimensional
(a1, a2, … an) tuplen-dimensional
![Page 11: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/11.jpg)
My Simple Cube
Measure Group
MeasureΣΣ
Dimension
Attribute
![Page 12: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/12.jpg)
My Simple Cube
Reseller Sales Internet Sales
Reseller Sales AmountΣΣ Internet Sales AmountΣΣ6
Date
Fiscal Year
Product
Category
Geography
Country1 3 5Fiscal Year
Calendar Year
Category
Subcategory
Country1 3
2 4
5
![Page 13: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/13.jpg)
P d tProduct
Category
All Products
Accessories Bikes Clothing Components
![Page 14: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/14.jpg)
P d tProduct
Category
([Bikes])[Product].[Category]
([Bikes])
Bike
Clo CAAl es othing
Com
ponents
Accessories
ll Products
![Page 15: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/15.jpg)
(
a1,
a2,
(
[Date].[Calendar Year].[CY 2002],
[Date].[Fiscal Year].[All Periods],
(
[CY 2002],
[All Periods],
a3,
a4,
a5,
a6
[Product].[Category].[Bikes],
[Product].[Subcategory].[All Products],
[Geography].[Country].[All Geographies],
[Measures].[Reseller Sales Amount]
[Bikes],
[All Products],
[All Geographies],
[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]
)
![Page 16: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/16.jpg)
(
[Date].[Calendar Year].[CY 2002],
[Date].[Fiscal Year].[All Periods],(
[D ] [C l d Y ] [CY ] [ ] [ ] [ ],
[Product].[Category].[Bikes]
[Product].[Subcategory].[All Products],
[Geography].[Country].[All Geographies],
[Measures].[Reseller Sales Amount]
)
[Date].[Calendar Year].[CY 2002],
[Product].[Category].[Bikes]
)
)
Rule 3
First Member
Rule 2
(All) Member
Rule 1
Default Member
Attributes Partial
Tuple
Completed
Tuple
All Periods
All Products
Date.Calendar Year CY 2002
Date.Fiscal Year
Product.Category Bikes
Product.Subcategory
CY 2002
All Periods
Bikes
All Products
All Geographies
Reseller Sales Amount
Geography.Country
Measures
All Geographies
Reseller Sales Amount
![Page 17: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/17.jpg)
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
![Page 18: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/18.jpg)
SELECT
[D ] [C l d Y ] M b ON COLUMNS
MDX
[Date].[Calendar Year].Members ON COLUMNS,
[Product].[Category].Members ON ROWS
FROM [My Simple Cube]
SELECT
SQL
e.CalendarYear,
d.EnglishProductCategoryName,
SUM(a.SalesAmount)
FROM FactResellerSales a
INNER JOIN DimProduct b
d b dON a.ProductKey=b.ProductKey
INNER JOIN DimProductSubcategory c
ON b.ProductSubcategoryKey=c.ProductSubcategoryKey
INNER JOIN DimProductCategory d
ON c.ProductCategoryKey=d.ProductCategoryKey
INNER JOIN DimDate eINNER JOIN DimDate e
ON a.OrderDateKey=e.DateKey
GROUP BY
e.CalendarYear,
d.EnglishProductCategoryName
![Page 19: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/19.jpg)
WITH MEMBER [Measures].[Total Sales Amount] AS
[M ] [I S l A ] [M ] [R ll S l A ]
MDX
[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]
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
SELECT
FROM [Step-by-Step]
WHERE ([Date].[Calendar Year].[CY 2004])
SQL
m.EnglishProductName,
o.TotalSalesAmount
FROM dbo.DimProduct m
INNER JOIN ( -- TOP 10 PRODUCTS OF 2003
SELECT TOP 10
d ( l ) l la.ProductKey, SUM(a.SalesAmount) AS TotalSalesAmount
FROM (
SELECT
x.productkey, x.salesamount
FROM dbo.FactInternetSales x
INNER JOIN dbo.DimDate yINNER JOIN dbo.DimDate y
ON x.OrderDateKey=y.DateKey
WHERE y.CalendarYear=2003
UNION ALL
SELECT
x.productkey, x.salesamount
![Page 20: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/20.jpg)
![Page 21: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/21.jpg)
![Page 22: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/22.jpg)
L L dLessons Learned
Cl l d fi diClearly define audience
Align technology with audience
Actively engage audience
![Page 23: Unlocking the Mystery of MDX](https://reader037.vdocuments.us/reader037/viewer/2022110204/55d4fb30bb61eb9a528b4579/html5/thumbnails/23.jpg)
Thank [email protected]