MDX - PowerPoint PPT Presentation

1 / 38

About This Presentation

Title:

MDX

Description:

By changing the value of the flag, one can include or exclude descendants or ... [Store].[All Stores].[USA].CHILDREN ON ROWS. FROM [Sales] ... – PowerPoint PPT presentation

Number of Views:474

Avg rating:3.0/5.0

Slides: 39

Provided by: liorr

Category:

more less

Transcript and Presenter's Notes

Title: MDX

1
????? ??????

????? 9
MDX

2
???? ?????

Introduction to Multidimensional Expressions
(MDX)
Using Data Definition Language with SQL Server
2000 Analysis Services Client Applications

3
Select Query

SELECT axis specification ON COLUMNS,
axis specification ON ROWS
FROM cube_name
WHERE slicer_specification

4
Basic Definitions

Dimension
Level
Member
Members Set

5
Simple Example

SELECT Measures.MEMBERS ON COLUMNS,
Store.MEMBERS ON ROWS
FROM Sales

All members of a dimension
6
Result
7
Levels Members

SELECT Measures.MEMBERS ON COLUMNS,
Store.City.MEMBERS ON ROWS
FROM Sales

8
Select Set of Specific Members

SELECT Measures.MEMBERS ON COLUMNS,
Store.Store State.CA,
Store.Store State.WA ON ROWS
FROM Sales

9
Ordered Sets
ORDER(set, expression , ASC DESC BASC
BDESC)
The B for broken hierarchical order

SELECT Measures.MEMBERS ON COLUMNS,
ORDER(Store.Store City.MEMBERS,
Measures.Sales Count, BDESC) ON ROWS
FROM Sales

10
From-To

SELECT Measures.MEMBERS ON COLUMNS,
(Store.Store City.Beverly HillsSpokane
) ON ROWS
FROM SALES

11
Children of Specific Members

SELECT Measures.MEMBERS ON COLUMNS,
Store.Store State.CA.CHILDREN,
Store.Store State.WA.CHILDREN ON ROWS
FROM Sales

12
Result
13
Member and Its Children

SELECT Measures.MEMBERS ON COLUMNS,
Store.Store State.CA,Store.Store
State.CA.CHILDREN,
Store.Store State.WA.CHILDREN,Store.St
ore State.WA ON ROWS
FROM Sales

14
Other members operators that return member

member.PARENT
member.FIRSTCHILD/LASTCHILD
member.PREVMEMBER / NEXTMEMBER
member.LEAD(n)/LAG(n)

15
????? - Ascendants

SELECT Measures.StoreSales,
Measures.Store Cost ON COLUMNS,
Ascendants(Store.All Stores.USA.CA.San
Francisco) ON ROWS
FROM Sales

16
Results
17
?????? - Drill Down

DESCENDANTS(member, level , flags)
By default, only members at the specified level
will be included. By changing the value of the
flag, one can include or exclude descendants or
children before and after the specified level.

18
DESCENDANTS Example

SELECT Measures.MEMBERS ON COLUMNS,
DESCENDANTS(Store.Store Country.USA,
Store City) ON ROWS
FROM Sales

19
Result
20
After Flag
SELECT Measures.MEMBERS ON COLUMNS, DESCENDANTS(
Store.Store Country.USA, Store
City,AFTER) ON ROWS FROM Sales
21
BEFORE FLAG

SELECT Measures.MEMBERS ON COLUMNS,
FROM Sales
DESCENDANTS(Store.Store Country.USA,
Store City,BEFORE) ON ROWS

22
Slice and Dice

SELECT Measures.MEMBERS ON COLUMNS,
Store.Store State.MEMBERS ON ROWS
FROM Sales
WHERE (Time.Year.1997)

23
Slice and Dice

SELECT Store Type.Store Type.MEMBERS ON
COLUMNS,
Store.All Stores.USA.CHILDREN ON ROWS
FROM Sales
WHERE (Measures.Sales Average)

24
Slice and Dice

SELECT Store Type.Store Type.MEMBERS ON
COLUMNS,
Store.All Stores.USA.CHILDREN ON ROWS
FROM Sales
WHERE (Measures.Sales Average,
Time.Year.1997
)

25
CROSSJOIN

Use several dimensions in the same axis
SELECT Promotions.Promotion Name.MEMBERS ON
COLUMNS,
CrossJoin(Store.All Stores.USA.CHILDREN,St
ore.All Stores.CANADA.CHILDREN,Store
Type.Store Type.MEMBERS) ON ROWS
FROM Sales
WHERE (Measures.Sales Average,
Time.Year.1997)

26
Result
27

SELECT GENERATE(Time.Year.MEMBERS,
Time.CURRENTMEMBER, Time.CURRENTMEMBER.CHI
LDREN) ON COLUMNS,
Promotions.All Promotions.CHILDREN ON ROWS
FROM Sales
WHERE (Measures.Unit Sales)

28
Calculated Measure

Calculated members are derived members that is,
they're members that have no basis in the data
itself, but are created via evaluated expressions
in MDX. They're returned by the same processes as
a standard member, and can be created via a rich
set of functions that are present in MDX.
Calculated members extend our capabilities to
manipulate multidimensional data.

29
Example
30
Example 2
31
ADDCALCULATEDMEMBERS

SELECT ADDCALCULATEDMEMBERS(Measures.MEMBERS) ON
COLUMNS,
Store.Store State.CA,
DESCENDANTS(Store.Store State.CA, Store
City) ON ROWS
FROM Sales

32
Calculated Members and Named Sets
WITH MEMBER Measures.ProfitPercent
AS '(Measures.Store Sales - Measures.Store
Cost) / (Measures.Store Cost)', FORMAT_STRING
'.00' SELECT CrossJoin(Time.1997.CHILDREN
,Measures.ProfitPercent,Measures.Store
Sales,Measures.Store Cost) ON
COLUMNS, Store.All Stores.USA.CHILDREN
ON ROWS FROM Sales
33
WITH MEMBER Measures.ProfitPercent
AS '(Measures.Store Sales - Measures.Store
Cost) / (Measures.Store Cost)', FORMAT_STRING
'.00' MEMBER Time.First Half 97 AS
'Time.1997.Q1 Time.1997.Q2' SELECT
CrossJoin(Time.1997.Q1,Time.1997.Q2,
Time.First Half 97 ,Measures.ProfitPercent,Me
asures.Store Sales,Measures.Store Cost) ON
COLUMNS, Store.All Stores.USA.CHILDREN
ON ROWS FROM Sales
34
Solve_Order
The SOLVE_ORDER This property helps decide the
order in which to perform the evaluations when
using multiple calculated members or named sets.
The default value for the solve order is
zero. When SOLVE_ORDERgt1 for profit percentIt
first add the two quarters and then calculate the
profit percent. When SOLVE_ORDERgt1 for
Time.First Half 97 It first calculate profit
percent then add the two quarters.
35
WITH MEMBER Time.First Half 97 AS
'Time.1997.Q1 Time.1997.Q2',
SOLVE_ORDER1 MEMBER Measures.ProfitPercent
AS '(Measures.Store Sales - Measures.Store
Cost) / (Measures.Store Cost)', FORMAT_STRING
'.00' SELECT CrossJoin(Time.1997.Q1,T
ime.1997.Q2,Time.First Half 97
,Measures.ProfitPercent,Measures.Store
Sales,Measures.Store Cost) ON
COLUMNS, Store.All Stores.USA.CHILDREN
ON ROWS FROM Sales
36
Result
37
Relative Measures

WITH MEMBER MEASURES.PercentageSales AS
'(Product.CURRENTMEMBER, Measures.Unit Sales)
/ (Product.CURRENTMEMBER.PARENT,
Measures.Unit Sales)',
FORMAT_STRING '.00'
SELECT MEASURES.Unit Sales, MEASURES.Percentage
Sales ON COLUMNS,Product.MEMBERS ON ROWS
FROM Sales