07.txt

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

      Response.Write "本地多维数据集创建完成!"
  Else
     Response.Write "Error Number: " & Err.Number & "<br>" & "Description: " _

      & err.description
  end if
  cn.Close
  Set cn = nothing
  %>

7-18

Option Explicit
Dim amdcat As ADOMD.Catalog
Dim amdcst As ADOMD.Cellset
' 数据源连接到本地 (Local)，数据库为NW_Mart
Const connstr = "Provider=MSOLAP;Data Source=local;Initial " _
 & "Catalog=NW_Mart"
l	创建一个子程序连接到Analysis Server
Public Sub Connect2OLAP()
    Set amdcat = CreateObject("ADOMD.Catalog")
        amdcat.ActiveConnection = connstr
            If Not amdcat Is Nothing Then
                MsgBox "连接成功!"
            Else
                MsgBox "连接失败!"
            End If
End Sub
l	读取多维数据集的定义，在此我们创建一个子程序
Public Sub GetCubeDef()
   On Error Resume Next
   '*********************************
   ' 清除当前工作表
   '*********************************
    Range("A1:Z400").Select
    Selection.Clear
    Dim cub As ADOMD.CubeDef
    Dim dimn As ADOMD.Dimension
    Dim hier As ADOMD.Hierarchy
    Dim lvl As ADOMD.Level
    Dim row As Integer
    Dim col As Integer
    row = 2
    col = 1
    '**********************************************************
    ' 调用Connect2OLAP子程序连接到 OLAP Database
    '**********************************************************
    Connect2OLAP
    '**********************************************************
    ' 取得销售分析多维数据集的定义

    '**********************************************************
    Set cub = amdcat.CubeDefs("销售分析")
    row = row + 1
    Cells(row, col) = "Cube Name: " & cub.Name
    row = row + 1
    Cells(row, col) = "Description: " & cub.Description
    row = row + 1
    Cells(row, col) = "Dimensions"
    '**********************************************************
    ' 取得各多维数据集中的各维
    '**********************************************************
    For Each dimn In cub.Dimensions
        row = row + 1
        Cells(row, col + 1) = "Dimension Name: " & dimn.Name
        row = row + 1
        Cells(row, col + 1) = "Description: " & dimn.Description
        row = row + 1
        Cells(row, col + 1) = "Unique Name: " & dimn.UniqueName
        row = row + 1
        Cells(row, col + 1) = "Hierarchies"
        row = row + 1
        '**********************************************************
        ' 取得维中各分层结构
        '**********************************************************
        For Each hier In dimn.Hierarchies
            row = row + 1
            Cells(row, col + 2) = "Hierarchy Name: " & hier.Name
            row = row + 1
            Cells(row, col + 2) = "Description: " & hier.Description
            row = row + 1
            Cells(row, col + 2) = "Unique Name: " & hier.UniqueName
            row = row + 1
            Cells(row, col + 2) = "Levels"
            row = row + 1
            '**********************************************************
            ' 取得分层结构中各层次
            '**********************************************************
           For Each lvl In hier.Levels
                row = row + 1
                Cells(row, col + 3) = "Level Name: " & lvl.Name
                row = row + 1
                Cells(row, col + 3) = "Description: " & lvl.Description

                row = row + 1
                Cells(row, col + 3) = "Unique Name: " & lvl.UniqueName
                row = row + 1
                Cells(row, col + 3) = "Caption: " & lvl.Caption
                row = row + 1
                Cells(row, col + 3) = "Depth: " & lvl.Depth
                row = row + 1
            Next lvl
        Next hier
    Next dimn
    '***********************************************
    ' 清除所有对象声明
    '***********************************************
    Set lvl = Nothing
    Set hier = Nothing
    Set dimn = Nothing
    Set cub = Nothing
    Set amdcat = Nothing
End Sub

7-19

Public Sub GetCubeData()
    Dim Y_axis As ADOMD.Axis
    Dim X_axis As ADOMD.Axis
    Dim i As Integer
    Dim j As Integer
    Dim amdcst As ADOMD.Cellset
    Const connstr1 = "Provider=MSOLAP;Data Source=local;Initial " _
 & "Catalog=FoodMart"
    '*********************************
    ' 清除工作表
    '*********************************
    Range("A1:Z400").Select
    Selection.Clear
    '****************************************
    '创建cellset对象
    '****************************************

    Set amdcst = CreateObject("ADOMD.Cellset")
    '****************************************
    '连接到OLAP Server
    '****************************************
    amdcst.ActiveConnection = connstr1
  '****************************************
    '设置要执行的MDX语句
    '****************************************
    amdcst.Source = "With Member [Measures].[Total Sales] As" _
    + "'([Measures].[Store Sales])',Format ='$#.00'" _
    + "Select {[Measures].[Total Sales]} On Columns, [Product].[Food].Children On Rows From Sales Where ([Customers].[USA].[CA],[1997])"

    amdcst.Open
    '****************************************
    '设置每一轴的Axes Object
    '****************************************
    Set X_axis = amdcst.Axes(0)
    Set Y_axis = amdcst.Axes(1)
    '***********************************************
    '将各坐标轴上member对象中的数据读出来显示在工作
    '表上
    '***********************************************
    For i = 0 To X_axis.Positions.Count - 1
        Cells(1, i + 2) = X_axis.Positions(i).Members(0).Caption
        For j = 0 To Y_axis.Positions.Count - 1
       '*****************************************************
       '读取cell position中heirarchy level member
       '*****************************************************
             Cells(j + 3, 1) = Y_axis.Positions(j).Members(0).Caption
       '**********************************************
       '取得轴交点的Cell值
       '**********************************************
           Cells(j + 3, i + 2) = amdcst(i, j).FormattedValue
       Next j
    Next i
   '****************************************
    '清除所有对象声明
    '****************************************
    amdcst.Close
    Set amdcst = Nothing
    Set Y_axis = Nothing

    Set X_axis = Nothing
End Sub

7-20

<%@ Language=VBScript %>

<%

    Option Explicit
    Dim connstr
    connstr = "Provider=MSOLAP;Data Source=local;Initial Catalog=FoodMart"
    Dim cst
    
    Dim Y_axis
    Dim X_axis
    Dim i
    Dim j
   
    '****************************************
    '创建Cellset
    '****************************************
    Set cst = CreateObject("ADOMD.Cellset")
    
    '****************************************
    '连接OLAP服务器
    '****************************************
    cst.ActiveConnection = connstr
    
    If err.number <> 0 then

		Response.Write err.description
	end if
    
    '****************************************
    '创建查询字串
    '****************************************
    cst.Source = "With Member [Measures].[Total Sales] As" _
       + "'([Measures].[Store Sales])',Format ='$#.00'" _
       + "Select {[Measures].[Total Sales]} On Columns, [Product].[Food].Children On Rows From Sales Where ([Customers].[USA].[CA],[1997])"
    
    cst.Open

    If err.number <> 0 then
		Response.Write err.description
	end if
    
    '****************************************
    '获取Axis
    '****************************************
    Set X_axis = cst.Axes(0)
    Set Y_axis = cst.Axes(1)
        
    '***********************************************
    '输出到网页
    '***********************************************
    Response.Write("<BR><p>问题<BR>1997年美国加州各类食品销售额是多少?</p><BR><BR>")
    Response.Write("<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>")

    For i = 0 To X_axis.Positions.Count - 1
        Response.Write "<td BGCOLOR=#800000> <FONT COLOR=WHITE>" & "</font></td>"
        Response.Write "<td BGCOLOR=#800000> <FONT COLOR=WHITE>" & X_axis.Positions(i).Members(0).Caption & "</font></td>"
        
        For j = 0 To Y_axis.Positions.Count - 1
	    Response.Write "<tr>"
             Response.Write "<td BGCOLOR=f7efde>" & Y_axis.Positions(j).Members(0).Caption & "</td>"
            
             Response.Write "<td BGCOLOR=f7efde>" & cst(i, j).FormattedValue & "</td>"
            
	     Response.Write "</tr>"

        Next
    Next
    
    Response.Write "</table>"
    '****************************************
    '清除所有对象声明
    '****************************************
    cst.Close
    Set cst = Nothing
    Set Y_axis = Nothing
    Set X_axis = Nothing
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>

<P>&nbsp;</P>

</BODY>
</HTML>