07.txt

仍然是SQL语言学习版 可以致用的一些文章

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 Cube）
CREATE MEMBER
[WAREHOUSE].[MEASURES].[WAREHOUSEPOFIT] 
AS ‘[MEASURES].[WAREHOUSE SALES] - 
	[MEASURES].[WAREHOUSE COST]

7-17

<%
  Option Explicit
  Dim cn 
  Dim status 
  Dim ConnectionString
  Dim location
  Dim dsn
  Dim CreateCubeStr
  Dim InsertInto
  Dim SourceDSN 

  On Error Resume Next
  Set cn = CreateObject("ADODB.Connection")
  ' **********************************************************************
  '  设置连接字串及创建本地多维数据集的连接语句
  ' **********************************************************************
  ' 将Local Cube创建在C:\ 路径下
  location = "LOCATION=C:\LocalSalesCube.cub"

  ' 连接到OLAP Server的字串，其中data source是OLAP Server的名称，INITIAL   
  '  CATALOG是数据库的名称
  SourceDSN = "Provider=MSOLAP;data source=local;INITIAL " _
  & "CATALOG=NW_Mart"

  dsn = "SOURCE_DSN=""" & SourceDSN & """"

  ' 定义创建本地多维数据集的语句　　
  CreateCubeStr = "CREATECUBE=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);"

  ' 定义INSERT INTO本地多维数据集的语句
  InsertInto = "INSERTINTO=INSERT INTO LocalSales (" _
  & "Time.Year, Time.Quarter, Time.Month, Product.[Product Name], " _
  & "Measures.Quantity) " _
  & "SELECT 销售分析.[时间_年季月日:年], 销售分析.[时间_年季月日:季度],销售分析. [时间_年季月日:月], "  _      
  & "销售分析.[产品_类别区分:产品名称], " _
  & "销售分析.[measures:销售金额] From 销售分析"

  ' **********************************************************************
  '  连接到OLAP Server执行创建OLAP Server及将数据写入本地多维数据集中
  ' **********************************************************************
  ConnectionString = location & ";" & dsn & ";" & CreateCubeStr & ";" & InsertInto
  ' Provider必需指定为MSOLAP
  cn.Provider = "MSOLAP"
  cn.ConnectionString = ConnectionString
  cn.Open ConnectionString
  If Err.Number = 0 Then