MDX 业务实现

<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 680460288 22 0 262145 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 680460288 22 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:宋体; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-font-kerning:1.0pt;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:42.55pt; mso-footer-margin:49.6pt; mso-paper-source:0; layout-grid:15.6pt;} div.Section1 {page:Section1;} -->

1. 计算每个时间段的平均销售价格：

WITH

MEMBER [Measures].[Avg Sales Price] As

[Measures].[Sales] / [Measures].[Quantity]

select {[Measures].[Sales], [Measures].[Quantity],[Measures].[Avg Sales Price]} ON COLUMNS,

  {[Time].[All Years].[2003], [Time].[All Years].[2004], [Time].[All Years].[2005]} ON ROWS

from [SteelWheelsSales]

2. 所有年度各个产品销售一览

select CrossJoin ({[Time].[Years].Members},{[Measures].[Sales],[Measures].[Quantity]}) ON COLUMNS,

  {[Product].[All Products].[Classic Cars], [Product].[All Products].[Motorcycles], [Product].[All Products].[Planes], [Product].[All Products].[Ships], [Product].[All Products].[Trains], [Product].[All Products].[Trucks and Buses], [Product].[All Products].[Vintage Cars]} ON ROWS

from [SteelWheelsSales]

3. 计算每年各种产品占销售总额的比例（占有率）

with

  member [Measures].[Percent] as

'[Measures].[Sales] / ([Measures].[Sales],[Product].[All Products])',

Format_string = '0.00%'

select

crossJoin([Time].[Years].Members,{[Measures].[Sales],[Measures].[Percent] }) ON COLUMNS,

  {[Product].[All Products].[Classic Cars], [Product].[All Products].[Motorcycles], [Product].[All Products].[Planes], [Product].[All Products].[Ships], [Product].[All Products].[Trains], [Product].[All Products].[Trucks and Buses], [Product].[All Products].[Vintage Cars]} ON ROWS

from [SteelWheelsSales]

4. 计算各时期销售额的环比增长（环比）

with

  member [Measures].[Sales Increase] as

'[Measures].[Sales] - ([Measures].[Sales],[Time].[Years].CurrentMember.PrevMember)'

select

CrossJoin({[Product].[All Products]},{[Measures].[Sales],[Measures].[Sales Increase]})

ON COLUMNS,

{[Time].[Years].Members }

ON ROWS

from [SteelWheelsSales]

5. 计算销售额的同比增长（同比）

with

  member [Measures].[SameCompare] as

'[Measures].[Sales] - ([Measures].[Sales],

ParallelPeriod ([Time].[Years],1,[Time].[Years].CurrentMember))'

select

CrossJoin({[Product].[All Products]},{[Measures].[Sales],[Measures].[SameCompare] })

ON COLUMNS,

{Descendants([Time].[Years].CurrentMember,[Time].[Quarters],Self_and_before)}

ON ROWS

from [SteelWheelsSales]

6. 计算产品的受欢迎程度（这里是根据各种产品卖出的数量计算）

with

  member [Measures].[Welcome Degree] as

'[Measures].[Quantity] / ([Measures].[Quantity],[Product].CurrentMember.Parent)',

Format_string = '0.00%'

select

crossJoin([Time].[Years].Members,{[Measures].[Sales],[Measures].[Welcome Degree] }) ON COLUMNS,

  {[Product].[All Products].[Classic Cars], [Product].[All Products].[Motorcycles], [Product].[All Products].[Planes], [Product].[All Products].[Ships], [Product].[All Products].[Trains], [Product].[All Products].[Trucks and Buses], [Product].[All Products].[Vintage Cars]} ON ROWS

from [SteelWheelsSales]

7. 阶段平均销售额（量）

--Avg(Descendants([Time].CurrentMember,[Time].[Months]),[Measures].[Quantity])=

Sum(Descendants([Time].CurrentMember,[Time].[Months]),[Measures].[Quantity]) / Count(Descendants([Time].CurrentMember,[Time].[Months]))

 

with

  member [Measures].[Period Quantity] as

'Sum(Descendants([Time].CurrentMember,[Time].[Months]),[Measures].[Quantity]) / Count(Descendants([Time].CurrentMember,[Time].[Months]))'

select

crossJoin([Time].[Years].Members,{[Measures].[Quantity],[Measures].[Period Quantity] })

ON COLUMNS,

  {[Product].[All Products].[Classic Cars], [Product].[All Products].[Motorcycles], [Product].[All Products].[Planes], [Product].[All Products].[Ships], [Product].[All Products].[Trains], [Product].[All Products].[Trucks and Buses], [Product].[All Products].[Vintage Cars]} ON ROWS

from [SteelWheelsSales]

8. 每年销售排名前 10 的产品

select

{[Measures].[Sales]} on 0,

generate([Time].[Years].Members,TopCount([Time].[Years].CurrentMember*[Product].[Product].Members,10,[Measures].[Sales]))  on 1

from [SteelWheelsSales]

 

 

 

SELECT

NON EMPTY

{

 

    {     {[Measures].[Sales]}

}

}  ON COLUMNS,

 

NON EMPTY

{

     {ToggleDrillState({{[Time].[All Years].[2003]}*{[Product].[All Products].[Classic Cars].[Classic Metal Creations].[1952 Alpine Renault 1300],[Product].[All Products].[Classic Cars].[Unimax Art Galleries].[1992 Ferrari 360 Spider red]},

     {[Time].[All Years].[2004]}*{[Product].[All Products].[Classic Cars].[Second Gear Diecast].[2001 Ferrari Enzo],[Product].[All Products].[Classic Cars].[Unimax Art Galleries].[1992 Ferrari 360 Spider red]},

     {[Time].[All Years].[2005]}*{[Product].[All Products].[Classic Cars].[Unimax Art Galleries].[1992 Ferrari 360 Spider red],[Product].[All Products].[Motorcycles].[Red Start Diecast].[2003 Harley-Davidson Eagle Drag Bike]}}, {[Time].[All Years].[2003]})}

 

} ON ROWS

FROM [SteelWheelsSales]

 

SELECT

NON EMPTY

{

 

    {     {[Measures].[Sales]}

}

}  ON COLUMNS ,

 

NON EMPTY

{

     union ( union ({ToggleDrillState({[Time].[All Years].[2003]}, {[Time].[All Years].[2003]})}

  * {[Product].[All Products].[Classic Cars].[Unimax Art Galleries].[1992 Ferrari 360 Spider red],

  [Product].[All Products].[Classic Cars].[Classic Metal Creations].[1952 Alpine Renault 1300]},

  {[Time].[All Years].[2004]}*{[Product].[All Products].[Classic Cars].[Second Gear Diecast].[2001 Ferrari Enzo],

  [Product].[All Products].[Classic Cars].[Unimax Art Galleries].[1992 Ferrari 360 Spider red]}),

  {[Time].[All Years].[2005]}*{[Product].[All Products].[Classic Cars].[Unimax Art Galleries].[1992 Ferrari 360 Spider red],[Product].[All Products].[Motorcycles].[Red Start Diecast].[2003 Harley-Davidson Eagle Drag Bike]})

} ON ROWS

FROM [SteelWheelsSales]

 

9. 每年销售排名后 10 的产品

sselect

{[Measures].[Sales]} on 0,

generate([Time].[Years].Members,BottomCount([Time].[Years].CurrentMember*[Product].[Product].Members,10,[Measures].[Sales]))  on 1

from [SteelWheelsSales]

10.2004 年既买过 Classic Cars 又买过 Motorcycles 的客户

select {[Product].[All Products].[Classic Cars], [Product].[All Products].[Motorcycles], [Product].[All Products].[Planes], [Product].[All Products].[Ships], [Product].[All Products].[Trains], [Product].[All Products].[Trucks and Buses], [Product].[All Products].[Vintage Cars]} ON COLUMNS,

Generate([Customers].[Customer].Members,

IIF(([Product].[All Products].[Classic Cars],[Measures].[Quantity])>0 and ([Product].[All Products].[Motorcycles],[Measures].[Quantity])>0 ,

{[Customers].[Customer].CurrentMember},{})) ON ROWS

from [SteelWheelsSales]

where [Time].[Years].[2004]

10. 各个国家销售产品的综合查询

select {[Measures].[Quantity],[Measures].[Sales]} ON COLUMNS,

  {[Markets].[Country].Members} ON ROWS

from [SteelWheelsSales]

 

SELECT

 

{[Measures].[Quantity],[Measures].[Sales]}  ON COLUMNS ,

{[Markets].[All Markets].[#null].[Germany],[Markets].[All Markets].[EMEA].[Sweden]} ON ROWS

FROM [SteelWheelsSales]

11.2004 年增长分析

with

  member [Measures].[Sales Increase Percent] as

'IIF(([Measures].[Sales],[Time].CurrentMember.PrevMember)>0,(([Measures].[Sales] - ([Measures].[Sales],[Time].CurrentMember.PrevMember)) / ([Measures].[Sales],[Time].CurrentMember.PrevMember)),null)',

Format_string = '0.00%'

member [Measures].[Quantity Increase Percent] as

'IIF(([Measures].[Quantity],[Time].CurrentMember.PrevMember)>0,(([Measures].[Quantity] - ([Measures].[Quantity],[Time].CurrentMember.PrevMember)) / ([Measures].[Quantity],[Time].CurrentMember.PrevMember)),null)',

Format_string = '0.00%'

member [Measures].[Last Sales] as

'([Measures].[Sales],[Time].CurrentMember.PrevMember)'

member [Measures].[Last Quantity] as

'([Measures].[Quantity],[Time].CurrentMember.PrevMember)'

select

{[Measures].[Sales],[Measures].[Last Sales],[Measures].[Sales Increase Percent],[Measures].[Quantity],[Measures].[Last Quantity],[Measures].[Quantity Increase Percent] }

ON COLUMNS,

Generate({[Time].[Months].Members},{[Time].[Years].[2004]})

ON ROWS

from [SteelWheelsSales]

 

 

SELECT

  { [Measures].[Quantity],[Measures].[Sales]} ON COLUMNS,

  { Crossjoin({[Time].[Years].members}, {[Markets].[All Markets].[NA].[USA]})} ON ROWS

FROM [SteelWheelsSales]