Transcript 11_MDX運算式

Charpter11
MDX運算式
MDX基本物件命名
MDX運算式
 利用函數組合成公式，以計算產生新的成員與數值。
 透過結構化的查詢語法，以查詢多維度分析引擎產生完整的資
料集結果
 控制彙總計算以及被呈現的狀態
MDX可分為三種類型
 MDX運算式
 MDX陳述句
 MDX指令碼
多維度分析中，分析的維度不只兩個，因此若要定義正確的對象時，
就必須加入新的敘述句型，將此分別稱為物件、集合以及Tuple。
識別碼命名規則

1.必須是英文字母或是底線開頭

2.名稱內可以是英文字母、基本拉丁文、十進位數字及底線

3.不可與MDX保留字重複

4.具有階層關係的物件名稱間則用「.」連結

5.如果違反以上命名規則，則請將物件名稱利用方括號包住
(例如:[Measure].[銷售金額])
物件宣告
•
多維度分析中，物件可分為兩大類
• 維度物件:集合名詞，代表一系列的屬性(Attribute)基礎物件
• 量值物件
•
物件宣告時，分析者可以自行選擇物件宣告時要採用「屬性
基礎」或者「階層基礎」。
• 屬性基礎是僅宣告個別屬性
• 階層基礎是除了屬性本體外查詢時還能夠獲取內建的階層
關係時此時，需要指定階層關係
EXAMPLE:物件宣告
宣告維度
[維度名稱]
產品維度
[產品別]
宣告階層
[維度名稱].[階層名稱]
產品維度的「產品階層」階層
[產品別].[產品階層]
宣告維度屬性(層級)
[維度名稱].[屬性名稱].[層級名稱]
[維度名稱].[階層名稱].[層級名稱]
產品維度的「產品大分類」階層
[產品別].[產品階層].[產品大分類]
[產品別].[產品大分類].[產品大分類]
宣告屬性(層級)成員
宣告屬性(層級)成員
屬性是單層的階層
屬性是單層的階層
[維度名稱].[屬性名稱].[層級名稱].[成員名稱] [產品別]. [產品層級].[產品大分類].[廚房]
[維度名稱].[屬性名稱].[層級名稱].&[成員鍵值] [產品別].[產品層級].[產品大分類].&[1]
[維度名稱].[階層名稱].[層級名稱].[成員名稱] [產品別]. [產品大分類].[產品大分類].[廚房]
[維度名稱].[階層名稱].[層級名稱].&[成員鍵值] [產品別].[產品大分類].[產品大分類].&[1]
宣告量值
[Measure].[量值名稱]
[Measure].[量值群組].[量值名稱]
宣告維度屬性的成員值(層級屬性)
[維度名稱].[屬性名稱].MemberValue
層級、成員與親族樹
•
利用兩個角度來檢視階層
• 層級
•本身是個抽象的集合名詞
•例如:時間維度會有「年」、「季」、「月」、「日」等層級。
• 成員
•構成層級的實體成員
•例如「2009年」就是「年」層級的成員之一。
親族樹
•
父成員(parent Member)
• 存在於成員上方者(該成員所隸屬的)
•
子成員(Child Member)
• 存在於成員下方者(隸屬於該成員)
•
A
祖先(Ancestors)
• 位於親族樹以上的成員
•
後代(Descendants)
B
C
D
• 位於親族樹以下的成員
•
手足(Sibling)
• 隸屬於同個父成員的成員們的關係
E
G
F
I
J
H
集合
•
一系列類似的物件所構成的
•
可以在{大括號}中以逗號分隔
•
EX:三個年度集合
• {[時間別].[日曆日階層].[日曆年].&[2007], [時間
別].[日曆日階層].[日曆年].&[2008],[時間別].[日曆日
階層].[日曆年].&[2009]}
• 如果集合成員是連續的關係，開頭與結尾中間以冒號分隔
•{[時間別].[日曆日階層].[日曆年].&[2007]:[時間別].[日曆日階
層].[日曆年].&[2009]}
函數介紹
•
函數介紹:傳回指定層級的所有成員集
• 成員運算式.Members
•EX:指定2009年所有日的子成員
descendants ([時間別].[日曆日階層].[日歷年].&[2009], ([時
間別].[日曆日階層].[日歷年])
•
函數介紹:傳回指定成員的子成員集合
• 成員運算式.Children
•
函數介紹:傳回指定成員的祖先成員集合
• Ancestors(成員運算式， 層級運算式 │相隔層級數)
•
函數介紹:傳回指定成員的後代成員集合
• Descendants( {成員運算式│集合運算式} [, (層級運算
式│相隔層級)[,Desc_Flag]])
屬性基礎本身是沒有層級個觀念，如果在宣告物件沒有指定
「階層」概念時，MDX本身層級關係函數就會無法使用 如:
屬性基礎
X
[維度名稱].[屬性名稱].[成員名稱].Children
[時間別].[日曆年].&[2009].Children
階層基礎
O
[維度名稱].[階層名稱].[屬性名稱].[成員名稱].Children
[時間別].[日曆日階層].[日曆年].&[2009].Children
Members取出該屬性(層級)內所有成員
屬性基礎
[維度名稱].[屬性名稱].Members
[產品別].[產品大分類].Members
階層基礎
[維度名稱].[階層名稱].[屬性名稱].Members
[產品別].[產品階層].[產品大分類].Members
Tuple特性
使用座標來表示Cube內的資料格模式稱之為Tuple。
Tuple命名
在小括弧內以逗號分隔
Tuple除了指定交錯的維度之外，同時還必須指定資料格所要列
出來的量值內容。
•//傳回廚具的銷售金額
([產品別].[產品階層].[產品大分類].[廚具],[Measures].[銷
售金額])
•//傳回2006~2008年廚具的銷售金額
({[時間別].[日曆年].[2006年]:[時間別].[日曆年].[2008
年],[產品別].[產品階層].[產品大分類].[廚
具],[Measures].[銷售金額])
MDX運算式

提供兩種不同的計算模式，分別為導出成員(Calculated
Member)以及命名集(Name Set)

前者透過MDX運算式指定新的計算成員，後者是定義數個成員
的集合日後使用者只須拖拉該命名集，就可以得到完整集合成
員內容
新增導出成員
新增導出成員之後，右側畫面會出現導出成員設定視窗。
Step1:新增導出成員
Step2:設計導出成員
設計父屬性資訊
• 設計者可以指定該導出成
員是隸屬於哪個維度屬性，
以及導出成員的父成員為
何。
• 如果導出成員為量值，其
父階層為「Measure」,而
量值無父成員
撰寫運算式_可直接拖拉計算工具的量值與維度
程式碼檢視
這些設定值可透過
MDX語法動態指定
格式字串
指定該導出成
員之顯示格式
基本運算

MDX所產生的導出成員並不會直接產生彙總，而是再查詢時根
據公式產生，只要公式不過度複雜或者數量過狀況下，是不
會影響查詢速度，新的MDX運算式也不需要重新整理Cube，只
需要重新部署即可。
除以零或者空值的錯誤，將除法作一些微調

函數介紹:傳回由邏輯測試決定的兩個值得其中之一
IIF(邏輯運算式,運算式1,運算式2)
EX:計算毛利率
IIF(ISEMPTY([Measures].[銷售金額])=1 OR [Measures].[銷售金
額]=0, NULL, ([Measures].[銷售金額]-[Measures].[產品成本][Measures].[稅金])/[Measures].[銷售金額])
計算平均值
•
假設欲查詢特定年度的平均銷售金額語法
• 函數介紹:傳回加總值
•Sum(集合算式 [,數值運算式])
• 函數介紹:傳回集合中的資料格數目
•Count(集合運算式[,(EXCLUDEEMPTY│INCLUDEEMPTY)])
• EX:計算2008年每月平均銷售量
•Sum(Descendant([時間].[日曆日階層].[日歷年].&[2008],[時間別].[日曆日階層].[日曆
月]),[Measures].[銷售金額]) /Count(DESCENDANTS([時間別].[日曆日階層].[日曆
年].&[2008],[時間別].[日曆日階層].[日曆月]))
•
動態指定時間成員
• 函數介紹:回傳目前成員
•階層運算式.CurrentMember
• EX:計算每月平均值
•SUM(DESCENDANTS([時間別].[日曆日階層].CurrentMember,[時間別].[日曆日階層].[日曆
月]),[Measures].[銷售金額])/Count(DESCENDANTS([時間別].[日曆日階
層].CurrentMember,[時間別].[日曆日階層].[日曆月]))
• 注意:CurrentMember前面僅能接維度或階層，而不能夠接屬性或是層級
比率計算
利用「比率」來將數值轉換為相對大小，能夠清楚的理解分析維度成員的大小關
係
佔全體百分比
 Ex:企業在追蹤各項產品的銷售量之外，也需要知道各項產品佔全公司所有產品的比率
 函數介紹: Root( [ 維度運算式 | Tuple運算式 ])
•傳回指定維度之最高成員
•EX:計算佔全體產品銷售量百分比
– [Measures].[銷售金額] / Root([產品別] , [Measures].[銷售金額])
佔上一層百分比
 使用佔全體百分比時，分母固定都是全體成員。
 EX:拉到產品細項時，能看該產品佔所產品中分類的百分比，拉到產品中分類時，能呈現
該產品中類佔所屬產品大分類的比例
 函數介紹: 成員運算式. Parent
•傳回指定成員的父成員
•EX:計算佔上層銷售量百分比
– [Measures].[銷售金額]/([產品別].[產品階
層] .CurrentMember.Parent,[Measures].[銷售金額])
前期成長率(時間相關分析)
•
前期成長率是將當期銷售量與前期銷售量互相比較
• 函數介紹:成員運算式.PrevMember
•傳回在指定成員同一層級內的前一個成員
• EX:想查詢2008年裡第3季與第2季的比較成長率
•IIF(
IsEmpty(([Measures].[銷售金額]
, [時間別].[日曆日階層].CurrentMember.PrevMember))=1,NULL,
([Measures].[銷售金額]([Measures].[銷售金額],[時間別].[日曆日階層].CurrentMember.PrevMember))
/([Measures].[銷售金額],[時間別].[日曆日階層].CurrentMember.PrevMember))
•為了避免除以零的錯誤，因此加入IIF條件式
去年同期成長率
•
通常會採用同期成長率來作完成長率的比較基礎
• 去年同期成員則需要使用ParallelPeriod函數來定義
• 函數介紹:ParallelPeriod([層級運算式[,索引鍵[,成員運算式]]])
•傳回跟指定成員在父成員的前一成員下同樣相對位置上的成員
• EX:查詢與去年同期的銷售金額是否有成長
•IIF(
IsEmpty(([Measures].[銷售金額],
ParallelPeriod([時間別].[日曆日階層].[日曆年],1,[時間別].[日曆日階
層].CurrentMember)))=1,NULL,
([Measures].[銷售金額]([Measures].[銷售金額],
ParallelPeriod([時間別].[日曆日階層].[日曆年],1,[時間別].[日曆日階
層].CurrentMember)))
/([Measures].[銷售金額],
ParallelPeriod([時間別].[日曆日階層].[日曆年],1,[時間別].[日曆日階
層].CurrentMember))
)
期間累積
•
每個月的年度累積業績是從當年度的第一個月累績至當月
• 函數介紹: PeriodsToDate([階層運算式[,成員運算式]])
•傳回指定時間層級自第一個成員至目前成員集合
•年累積:Ytd([成員運算式])
•季累績:Qtd([成員運算式])
•月累積:Mtd([成員運算式])
•週累積:Wtd([成員運算式])
• Ex:查詢今年開始的銷售金額到目前唯止所累積的銷售金
額
•SUM( PeriodsToDate([時間別].[日曆日階層].[日曆年],[時間
別].[日曆日階層]. CurrentMember),
[Measures].[銷售金額])
•SUM( YTD([時間別].[日曆日階層].CurrentMember),
[Measures].[銷售金額])
移動平均
•
移動平均是每期固定向前N期資料進行平均，要計算移動平均
時，必須要傳回前N期的時間成員集合
• 函數介紹:LastPeriods(索引值 [,成員運算式])
•傳回指定成員之前的成員集合(包含指定成員)
• 函數介紹:成員運算式.Lag(落後數)
•傳回成員層級中在特定成員之前n個成員
• Ex:假設想查詢出目前時間的前三年或季或月的銷售金額
平均
•AVG( LastPeriods(3, [時間別].[日曆日階層].CurrentMember),
[Measures].[銷售金額])
•AVG{ [時間別].[日曆日階層].CurrentMember.Lag(3): [時間
別].[日曆日階層].CurrentMember
},[Measures].[銷售金額])
計算屬性
計算成員預設不屬於任何量值群組
如果希望將計算成員列入指定量值群組，或是放入
特定資料夾中，就必須使用計算屬性
設定完畢後，需重新佈署才可看到結果
命名集
•
如果分析者透過特定的條件來篩選數個成員，如果此篩選模式
相當複雜，但是流程很固定，就可以透過命名集的模式於伺服
器定義。
新增命名集
在運算式中加入集合運算式(大括弧
或是集合函數)
新增「命名集」的例子
某產品經理只想要在多維度分析中瀏覽它所負責的
產品銷售量，此時就可以透過命名集來定義負責商
品的集合
 { [產品別].[產品階層].[產品大分類].[地材] ,
[產品別].[產品階層].[產品大分類].[磁磚]}
•
分析者也可根據過濾函數來新增命名集
• 找出賣得最好的前十名商品
• 找出倒數五名業績最差的業務員
• 找出佔公司前80%業績的重要經銷商
最常用的排序過濾
TopCount:排序後取前N名
 TopCount(集合運算式 , 數量[ , 量值運算式])
ButtonCount:排序後取倒數N名
 ButtonCount(集合運算式 , 數量[ , 量值運算式])
TopSum:排序後至第一名取，取到值累加至N的所有成員集合
 TopSum (集合運算式 , 值[ , 量值運算式])
ButtonSum:排序後至最後一名取，取到值累加至N的所有成員集合
 ButtonSum (集合運算式 , 值[ , 量值運算式])
TopPercent:排序後至第一名起，取到值累加至佔總體N%的所有成員集合
 TopPercent (集合運算式 , 百分比[ , 量值運算式])
ButtonPercent:排序後至最後一名起，取到值累加至佔總體N%的所有成員
集合
 ButtonPercent(集合運算式 , 百分比[ , 量值運算式])
舉例說明
定義銷售量最高的前10名產品中分類集合
TopCount(
[產品別].[產品階層].[產品中分類].Members,
10,
[Measures].[銷售金額]
)
//排序基準
命名集使用
Step1:將命名集放置在過濾條件中
Step2:在拉出欲顯示的欄位與量值