C H A P T E R 10 MDX MDX MDX SQL

Size: px

Start display at page:

Download "C H A P T E R 10 MDX MDX MDX SQL"

琢怡钱
4 years ago
Views:

1 C H A P T E R 10 MDX MDX MDX SQL

2 /20 ABC 80% 20 80% ABC Pareto Adventure Works Clothing Subcategory with Set [OrderSet] as Order( [Product].[Product Categories].[Clothing].Children, [Measures].[Sales Amount], DESC ) Member [Measures].[ ] as Rank( [Product].[Product Categories].CurrentMember, OrderSet ) Member [Measures].[ ] as Sum(Head([OrderSet],[ ]),[Sales Amount]) Member [Measures].[ ] as [Sales Amount]/sum(OrderSet,[Sales Amount]), Format_String = "Percent" Member [Measures].[ ] as 366

3 10.1 Sum(Head([OrderSet],[ ]),[ ]), Format_String = "Percent" select {[Measures].[Sales Amount], [Measures].[ ], [Measures].[ ], [Measures].[ ], [Measures].[ ]} on 0, [OrderSet] on 1 from [Adventure Works] Clothing Subcategory Clothing Subcategory SQL Server 2005 MDX 367

10 368 Y 2 3 10-2 80/20 Subcategory 80% 80/20 70/30 60/40 0~80

4 Y /20 Subcategory 80% 80/20 70/30 60/40 0~80 80~95 95~100 ABC Z A B C 10-3 ABC Z Z Z 5 1~

5 Z Adventure Works Sales Amount with Set [TimeSet] as DESCENDANTS( [Date].[Calendar].[CY 2003], [Date].[Calendar].[Month]) Member [Measures].[ ] as Sum( {[TimeSet].Item(0): [Date].[Calendar].CurrentMember}, [Sales Amount]) Member [Measures].[ ] as Sum( LastPeriods( Count(TimeSet), [Date].[Calendar].CurrentMember), [Sales Amount]) 10.2 select {[Measures].[Sales Amount], [Measures].[ ], [Measures].[ ]} on 0, TimeSet on 1 from [Adventure Works] 10-4 SQL Server 2005 MDX 369

6 Z Z 10-5 Salas Amount Sales Amount Z Z Z 3 Z

7 Z 10-1 Z OLAP Domain Know How SQL Server 2005 MDX

Category Category Fiscal Year 10 372 1 WITH Member [Measures].[2004] as ([Date].[Fiscal Year].[FY 2004],[Sales Amount]) Member [Measures].[2005 ] as null Member [Measures].

8 Category Category Fiscal Year WITH Member [Measures].[2004] as ([Date].[Fiscal Year].[FY 2004],[Sales Amount]) Member [Measures].[2005 ] as null Member [Measures].[ ] as null Member [Measures].[ ] as null Select {[2004],[2005 ],[ ],[ ]} on 0, [Product].[Category].Members on 1 from [Adventure Works] FY 2004 Sales Amount

9 WITH Member [Measures].[2004] as ([Date].[Fiscal Year].[FY 2004],[Sales Amount]) Set [YearSet] as {[Date].[Fiscal Year].[FY 2002], [Date].[Fiscal Year].[FY 2003], [Date].[Fiscal Year].[FY 2004]} Member [Measures].[ID] as [Date].[Fiscal Year].CurrentMember.Properties("ID") Member [Measures].[2005 ] as LinRegPoint( 5, YearSet, [Sales Amount], [Measures].[ID]), Format_String = "Currency" Member [Measures].[ ] as null Member [Measures].[ ] as null Select {[2004],[2005 ],[ ],[ ]} on 0, [Product].[Category].Members on 1 L i n R e g P o i n t 8. 4 LinRegPoint (x,y) [Sales A m o u n t] y I D x F Y 2002 FY 2003 FY 2004 ID LinRegPoint x FY 2005 y 10-7 SQL Server 2005 MDX

10 374 3 10-7 2005 65939721.26 Member [Measures].[ ] as [Measures].

10 Member [Measures].[ ] as [Measures].[2004]/ CoalesceEmpty( ([Product].[Category].CurrentMember.Parent, [Measures].[2004]),[Measures].[2004]), Format_String = "Percent" Category CoalesceEmpty 0 4 Member [Measures].[ ] as ([Product].[Category].[All Products],[2005 ]) * [ ], Format_String = "Currency" * Category Category

11 Excel Set [TopCountry] as TopCount( [Customer].[Country].[Country].Members, 3, [Measures].[Internet Sales Amount]) 2 5 Set [TopProd] as Generate( {TopCountry}, TopCount( {[Customer].[Country].CurrentMember * [Product].[Product].[Product].Members}, 5, [Measures].[Internet Sales Amount])) Generate 5 SQL Server 2005 MDX 375

12 Member [Measures].[Rank1] as Rank( ([Customer].[Country].CurrentMember, [Product].[Product].CurrentMember), Order( [TopProd], [Internet Sales Amount], BDESC)) 15 2 TopProd TopProd Rank Tuple TopProd BDESC 4 Member [Measures].[Rank2] as Rank( [Product].[Product].CurrentMember, Order( [Product].[Product].[Product].Members, ([Customer].[Country].[All Customers], [Internet Sales Amount]),DESC)) Order [Customer].[Cou ntry].[all Customers] [Customer].[Country]. CurrentMember Internet Sales Amount 10-9

13 WITH Select {[Internet Sales Amount],[Rank1],[Rank2]} on 0, {TopProd} on 1 from [Adventure Works] SQL Server 2005 MDX 377

天津天狮学院关于修订2014级本科培养方案的指导意见

天津天狮学院关于修订2014级本科培养方案的指导意见目录天津天狮院关于修订 2014 级本科培养方案的指导意见...1 金融类专业...9 金融专业培养方案...9 保险专业培养方案...14 人力资源管理专业培养方案...19 劳动与社会保障专业培养方案...24 工商管理类专业...29 市场营销专业