<SQL Server 2000 OLAP服务设计与应用>书中的源代码

7-1 {US, China, Japan} Geography.Members=在地理维中的所有成员 Nest ({Jan, Feb},{China, US})={(Jan, China),(Jan, US),(Feb, China),(Feb, US)} Geography.cities=在地理维中的所有城市 China.Children=中国的所有省市 7-2 Shanghai.Parent = China Ancestor(海淀区,Country)=China May.FirstSibling(Quarters)=April May.FirstSibling(Years)=January 7-3 Sum（{US，China}，Sales.Value）：返回美国及中国的销售量。 Count（Descendents（China,Cities））：返回中国所有城市的数量。 Average，Median，Min，Max，Variance等…… 7-4 {Jan,Feb,Q3,Q4}代表1月、2月、第3季度、第4季度 [1998].Children={[1998].[Q1], [1998].[Q2], [1998].[Q3], [1998].[Q4]} [Year].members={[1997],[1998]} Q1：Q4={Q1,Q2,Q3,Q4} Crossjoin((Crossjoin(USA.Children,Q1:Q3)),Product)=…… {{{CA,Q1},Drink},{{CA,Q1},Food},{{CA,Q1},NonConsumable},..., {{CA,Q2},Drink},{{CA,Q2},Food},...,{{WA,Q1},Drink},{{WA,Q1},Food},……} 7-5 select[Product].Children on rows，[Years].members on Columns from [Sales] Where[line item total] 7-6 select[Product].Children on rows，[Years].members on Columns from[Sales] Where（[line item total]，[USA]） 7-7 Where CROSSJOIN{Sales},{[1997],[1998]}= Where {（Sales，[1997]），（Sales，[1998]）} 7-8 WITH <single_formula_specification>[<single_formula_specification>...] SELECT <axis_specification> [, <axis_specification>...] FROM <cube_specification> WHERE <slicer_specification> 其中： <single_formula_specification> ::= <member_specification> | <set_specification> | <cache_specification> <member_specification> ::= MEMBER <parent_of_member >.<member_name> AS '<value_expression>' <set_specification> ::= SET <set_name> AS '<set>' <cache_specification> ::= CACHE AS '(<set> [,<set>])' 7-9 WITH MEMBER All_Countries.美国和日本AS 'SUM（{美国，日本}） SELECT CROSSJOIN（{Smith，Jones}， {美国北部.CHILDREN，美国南部，日本，美国和日本}）ON COLUMNS， {一季度.CHILDREN，二季度，三季度，四季度.CHILDREN} ON ROWS FROM SalesCube WHERE（Sales，[1997]，Products.All） 7-10 WITH MEMBER All_Countries.美国和日本AS 'SUM（{美国，日本}）' SET Expand_Qtrs_1_4 AS '{一季度.CHILDREN，二季度，三季度，四季度.CHILDREN}' SELECT CROSSJOIN（{Smith, Jones}, {美国北部.CHILDREN，美国南部，日本，美国和日本}）ON COLUMNS, Expand_Qtrs_1_4 ON ROWS FROM SalesCube WHERE（Sales，[1997]，Products.All） 7-11 Dim amdCat As New ADOMD.Catalog Dim amdCube As ADOMD.CubeDef Dim amdProp As Object amdCat.ActiveConnection="Provider=msolap; Data Source=local;Initial Catalog=FoodMart;" amdCat.VubeDefs.Refresh For Each amdCube In amdCat.CubeDefs Print amdCube.Name Next 7-12 Dim amdCat As New ADOMD.Catalog Dim amdCset As New ADOMD.Cellset Dim XPos As ADOMD.Position Dim YPos As ADOMD.Position Dim mbr As ADOMD.Member l 指定连接及查询字串 amdCat.ActiveConnection = "Provider=msolap;" _ + "Data Source=local;Initial Catalog=FoodMart;" amdCset.Source = "SELECT Gender.members on columns, Measures.members on rows FROM sales" l 连接到OLAP服务器及执行MDX查询语句 Set amdCset.ActiveConnection = amdCat.ActiveConnection amdCset.Open l 将X轴的member打印在窗体上，X轴的Index是0，所以是Axes(0) For Each XPos In amdCset.Axes(0).Positions For Each mbr In XPos.Members Print mbr.Name Next Next 将Y轴的member打印在窗体上，Ｙ轴的Index是1，所以是Axes(1) For Each YPos In amdCset.Axes(1).Positions For Each mbr In YPos.Members Print mbr.Name Next Next 7-13 CREATE CUBE <cube name> <open param> DIMENSION <dimension name> [TYPE TIME], <hierarchy def> [<hierarchy def>...][{, DIMENSION <dimension name> [TYPE TIME], <hierarchy def> [<hierarchy def>...]}...] , MEASURE <measure name> <measure function def> [<measure format def>][<measure type def>][{, MEASURE <measure name> <measure function def> [<measure format def>] [<measure type def>] }...][,COMMAND <expression>][,COMMAND <expression>...] <close parem> <dimension name> ::= dimension name <hierarchy def> ::= [HIERARCHY <hierarchy name>,] <level def> [,<level def>...] <level def> ::= LEVEL <level name> [TYPE <level type>] [<level format def>] [<level options def>] <level type> ::= ALL | YEAR | QUARTER | MONTH | WEEK | DAY | DAYOFWEEK | DATE | HOUR | MINUTE | SECOND <level format def> ::= FORMAT_NAME <expression> [FORMAT_KEY <expression>] <level options def> ::= OPTIONS (<option_list>) <option_list> :: = <option> [,<option_list>] <option> ::= UNIQUE | SORTBYNAME | SORTBYKEY <measure function def> ::= FUNCTION <function name> <measure format def> ::= FORMAT <expression> <function name> ::= SUM | MIN | MAX | COUNT <measure type def> ::= TYPE <supported OLEDB numeric types> <supported OLEDB numeric types> :: = DBTYPE_I1 | DBTYPE_I2 | DBTYPE_I4 | DBTYPE_I8 | DBTYPE_UI1 | DBTYPE_UI2 | DBTYPE_UI4 | DBTYPE_UI8 | DBTYPE_R4 | DBTYPE_R8 | DBTYPE_CY | DBTYPE_DECIMAL | DBTYPE_NUMERIC | DBTYPE_DATE 7-14 Create CUBE LocalSales ( DIMENSION Time Type TIME, LEVEL Year TYPE YEAR, LEVEL Quarter TYPE QUARTER, LEVEL Month Type Month, DIMENSION Product, LEVEL [All Products] type All, LEVEL [Product Name], MEASURE [Quantity] Function SUM) 7-15 INSERT INTO <target-clause> [<options-clause>] [<bind-clause>] <source-clause> <target-clause> ::= <cube-name> ( <target-element-list> ) <target-element-list> ::= <target-element>[, <target-element-list>] <target-element> ::= [<dim-name>.[<hierarchy-name>.]]<level-name> | <time-dim-name> | [Measures.]<measure-name> | SKIPONECOLUMN <level-name> ::= <simple-level-name> | <simple-level-time>.NAME | <simple-level-time>.KEY <time-dim-name> ::= <dim-name-type-time> | <dim-name-type-time>.NAME | <dim-name-type-time>.KEY <options-clause> ::= OPTIONS <options-list> <options-list> ::= <option>[, <options-list>] <option> ::= <defer-options> | < analysis-options> <defer-options> ::= DEFER_DATA | ATTEMPT_DEFER <analysis-options> ::= PASSTHROUGH | ATTEMPT_ANALYSIS <bind-clause> ::= BIND (<bind-list>) <bind-list> ::= <simple-column-name>[,<simple-column-name>] <simple-column-name> ::= <identifier> <source-clause> ::= SELECT <columns-list> FROM <tables-list> [ WHERE <where-clause> ] | DIRECTLYFROMCACHEDROWSET <hex-number> <columns-list> ::= <column-expression> [, < columns-list> ] <column-expression> ::= <column-expression-name> <column-expression-name> ::= <column-name> [AS <alias-name>] | <alias name> <column-name> <column-name> ::= <table-name>.<column-name> | <column-function> | <ODBC scalar function> | <braced-expression> <column function> ::= <identifier>(. . .) <ODBC scalar function> ::= {FN<column-function>} <braced-expression> ::= (. . .) <tables list> ::= <table-expression> [, <table-list>] <table-expression> ::= <table-name> [ [AS] <table-alias>] <table-alias> ::= <identifier> <table-name> ::= <identifier> <where clause> ::= <where-condition> [AND <where-clause>] <where condition> ::= <join-constraint> | <application constraint> <join-constraint> ::= <column-name> = <column-name> | <open-parem><column-name> = <column-name><close-parem> <application-constraint> ::= (. . .) | NOT (. . .) | (. . .) OR (. . .) <identifier> ::= <letter>{<letter>|<digit>|<underline>|<dollar> |<sharp>}. . 7-16 CREATE <optional-scope><create-member-subset> [<create-member-subset>...] <create-member-subset> ::= MEMBER <cube-name>.<fully-qualified-member-name> AS '<expression>' [,<property-definition-list>] <property-definition-list> ::= <property-definition> | <property-definition>, <property-definition-list> <property-definition> ::= <property-identifier> = <property-value> <property-identifier> ::= <identifier> <property-value> ::= <string> | <number> <optional-scope> ::= <empty> | SESSION 示例：（FOODMART