MDX

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

• ????? 11
• ????? MDX

2
Basic Components

• Dimension
• Level
• Member
• Tuple Set
• Set

3
Case Study
4

Dimension
Member
Level
5
Referring a Member
Time.1997.Q1 Time.1998 Time.1998.Q2
.PrevMember ? Time.1998.Q1 Time.1998.Q
2.NextMember ? Time.1998.Q3 Time.1998.
Q2.Parent ? Time.1998 Time.1998.FirstChil
d ? Time.1998.Q1 Time.1998.LastChild ?
Time.1998.Q4 Time.1998.Q3.Lag(-2) ?
Time.1998.Q1
6
TupleCell

• (Store.USA.CA,Time.1997)
• (Store.USA,Measures.Amount)
• (Store.USA)

7
Default Member

• If Specified use specified.
• If There is all levels ? use all.
• Otherwise use first Member of the First Level.

8
Tuple Set

• tuple1,tuple2,
• (Store.USA,Measures.Amount),(Store.USA
  )

9
Tuple Set ? Usually Member Set

• Store.Canada,Store.USA,
  Store.Mexico
• Time.1997.Q1Time.1997.Q4
• Time.AllMembers
• Time.Year.AllMembers
• Time.1997.Children
• Descendants(Time.1997,Month) ? 1,2,..,12
• Descendants(Time.1997,Quarter,After)?
  1,2,..,12
• Descendants(Time.1997,Month,Before)
  ?1997,Q1,Q2,Q3,Q4
• Ascendants(Time.1997.Q1.1)?1,Q1,1997

10
Select Query

• SELECT set ON COLUMNS,
• set ON ROWS
• FROM cube_name
• WHERE tuple

11
Ex 1.

• Write a query for selecting the 1997s Net Income
  budget amount vs. Net Income actual amount for
  each state in the USA.

12
Solution 1

• select
• Store.USA.CHILDREN on rows,
• Category.Current Year's Budget,Category.Cu
  rrent Year's Actuals on columns
• from budget
• where (Time.1997,Measures.Amount,Account
  .Net Income)

13
Default Member

• If Specified use specified.
• If There is all levels ? use all.
• Otherwise use first Member of the First Level.

• select
• Store.USA.CHILDREN on rows,
• Category.Current Year's Budget,Category.Cu
  rrent Year's Actuals on columns
• from budget
• where (Account.Net Income)

14
CROSSJOIN

• Multiple Dimensions on same axis.
• CrossJoin (Set1,Set2)

15
Ex 2.

• Write a query for presenting the Net Income
  budget amount vs. Net Income actual amount for
  each state in the USA and for each quarter in
  1997.

16
Solution 2.

• select
• Crossjoin(Store.USA.CHILDREN,Time.1997.CHI
  LDREN) on rows,
• Category.Current Year's Budget,Category.Cu
  rrent Year's Actuals on columns
• from budget
• where (Account.Net Income)

17
Calculated Members and Named Sets
WITH MEMBER Measures. AS EXPRESSION
18
Ex 3.

• Write a query for presenting the Net Income
  budget in K for each state in the USA and for
  each quarter in 1997.

19
Solution 3

• WITH MEMBER Measures.amountk AS
  'Measures.Amount/1000'
• select
• Store.USA.CHILDREN on rows,
• Time.1997.CHILDREN on columns
• from budget
• where (Measures.amountk,Category.Current
  Year's Budget,Account.Net Income)

20
Ex 4.

• Write a query for presenting the percentage of
  Actuals Net Income in 1997 for each state in the
  USA relatively to the total actuals net income
  in USA.

21
Solution 4

• WITH MEMBER Measures.percamnt AS
  '(Measures.Amount,Store.CURRENTMEMBER)/(Mea
  sures.Amount,Store.USA)'
• select
• Store.USA.CHILDREN on columns
• from budget
• where (Measures.percamnt,Category.Current
  Year's Actuals,Account.Net Income,Time.199
  7)

22
Ex 5.

• Write a query for presenting the difference
  between Net Income budget and Net Income actual
  amount for each state in the USA and for each
  quarter in 1997.

23
lSolution 5

• WITH MEMBER Measures.diff AS '(Measures.Amount
  ,Category.Current Year''s Budget)-(Measures.
  Amount,Category.Current Year''s Actuals)'
• select Store.USA.CHILDREN on rows,
  Time.1997.CHILDREN on columns
• from budget
• where (Measures.diff,Account.Net Income)