用于处理 SSAS 立方体的 ASP.NET 页面

Igor Krupitsky

3.00/5 (2投票s)

2017年4月27日

CPOL

1分钟阅读

12948

242

一个 ASP.NET 页面，允许您处理 SSAS（SQL Server Analysis Services）立方体数据库。

下载源代码 - 738.8 KB

引言

SQL Server Analysis Services 通常每天处理一次，但有时，您希望让您的用户能够按需刷新数据库。此页面允许您执行此操作。

如果在处理过程中出现任何问题，页面将向您显示错误文本。

安装

下载 Cube.zip
解压缩并复制到 C:\inetpub\wwwroot\Cube
在记事本中打开 Cube.aspx.vb。将第 4 行和第 5 行更改为您的服务器和 SSAS 数据库。
在记事本中打开 Cube.aspx。将第 14 行更改为处理您的 SSAS 数据库所需的时间估计。
将浏览器指向 http://YourServerName/Cube/Cube.aspx。

背景

此页面使用以下第三方组件

ADOMD.NET (Microsoft.AnalysisServices.AdomdClient.dll)
AMO (Analysis Services Management Objects) (Microsoft.AnalysisServices.DLL)
jQuery
Bootstrap

Using the Code

Cube.aspx 将使用 jQuery 的 $.get 异步处理立方体。

 <%@ Page Language="vb" CodeFile="Cube.aspx.vb" Inherits="Cube" %>
<!DOCTYPE html>
<html>
<head runat="server">
    <title>Cubes</title>

    <script src="Content/jquery-1.10.2.min.js"></script>
    <link href="Content/bootstrap.min.css" rel="stylesheet" />

    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta charset="utf-8"> 
    <script>
    var start = null;
    var iTimeToProcess = 60*4; //4 mins

    function Process() {
            $("#idResult").show();
            $(".progress").show();

        if (start==null){
            start = new Date;

            setInterval(function() {
                var iSec = (new Date - start) / 1000;
                var iPct = parseInt(100 * iSec / iTimeToProcess) + "%";
                   $('.progress-bar').width(iPct).text(iPct);
            }, 1000);
        }

            $.get("?process=1",{ "_": $.now() }, function (data) {
                $(".progress").hide();

                if (data == "1") {
                    location = "?refresh=1";
                }else{
                    $("#txtResult").show().val(data);
                }
            })
        }
    </script>    
</head>
<body>
    <form id="form1" runat="server">
    <div class="container">
       <H1>Cubes for <%=sServer%> - <%=sDatabase%></H1>

        <table class="table table-striped table-hover">
               <tr>
                   <th>Cube</th>
                   <th>Last Processed</th>
               </tr>
            <%GetCubeList()%>
        </table>

        <div class="form-group">
            <button class="btn btn-default" onclick="Process(); return false;">Process Cubes</button>
        </div>

    <div id="idResult" class="form-group" style="display: none;">
        <textarea id="txtResult" class="form-control" rows="8" style="display: none;"></textarea>
        
        <div class="progress">
            <div class="progress-bar" role="progressbar" aria-valuenow="70" 
             aria-valuemin="0" aria-valuemax="100" style="width:0%"></div>
        </div>
    </div>       

    </div>

    </form>
</body>
</html>

Cube.aspx.vb 代码后台页面将列出立方体及其上次处理时间。第 27 行 "oDatabase.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)" 执行实际的数据库处理工作。

Public Class Cube
    Inherits System.Web.UI.Page

    Public sServer As String = "Server1"
    Public sDatabase As String = "Db1"

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Request.QueryString("process") = "1" Then
        Page.Server.ScriptTimeout = 60*20 ' 20 mins

            Try
                ProcessSsas()
                Response.Write("1")
            Catch ex As Exception
                Response.Write(ex.Message)
            End Try
            Response.End()
        End If

    End Sub

    Private Sub ProcessSsas()
        Dim oServer As New Microsoft.AnalysisServices.Server()
        oServer.Connect(GetConnectionString())
        Dim oDatabase As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabase)
        oDatabase.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)
    End Sub

    Private Function GetConnectionString() As String
        Return "Provider=MSOLAP.6;Initial Catalog=" & sDatabase & ";Data Source=" & sServer
    End Function

    Public Sub GetCubeList()
        Dim cn As New Microsoft.AnalysisServices.AdomdClient.AdomdConnection(GetConnectionString())
        cn.Open()

        If cn.Cubes.Count = 0 Then
            Response.Write("<tr><td colspan=2>No Cubes</td></tr>" & vbCrLf)
        End If

        For i = 0 To cn.Cubes.Count - 1
            If cn.Cubes(i).Name.Substring(0, 1) <> "$" Then
                Response.Write("<tr><td>")
                Response.Write(cn.Cubes(i).Name)
                Response.Write("</td><td>")
                Response.Write(cn.Cubes(i).LastProcessed)
                Response.Write("</td></tr>" & vbCrLf)
            End If
        Next

        cn.Close()
    End Sub

End Class

其他尝试

我们可以尝试使用 oServer.Execute() 运行 XMLA 脚本，而不是使用 Database.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)。这将为我们提供更大的灵活性，例如忽略 KeyNotFound 错误的能力。

Private Sub ProcessSsas()
    Dim oServer As New Microsoft.AnalysisServices.Server()
    oServer.Connect(GetConnectionString())
    ' Dim oDatabase As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabase)
    'oDatabase.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)
    
    Dim s As String = "<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" & _
    "   <ErrorConfiguration xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" _
    xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" _
    xmlns:ddl2=""http://schemas.microsoft.com/analysisservices/2003/engine/2"" _
    xmlns:ddl2_2=""http://schemas.microsoft.com/analysisservices/2003/engine/2/2"" _
    xmlns:ddl100_100=""http://schemas.microsoft.com/analysisservices/2008/engine/100/100"" _
    xmlns:ddl200=""http://schemas.microsoft.com/analysisservices/2010/engine/200"" _
    xmlns:ddl200_200=""http://schemas.microsoft.com/analysisservices/2010/engine/200/200"" _
    xmlns:ddl300=""http://schemas.microsoft.com/analysisservices/2011/engine/300"" _
    xmlns:ddl300_300=""http://schemas.microsoft.com/analysisservices/2011/engine/300/300"" _
    xmlns:ddl400=""http://schemas.microsoft.com/analysisservices/2012/engine/400"" _
    xmlns:ddl400_400=""http://schemas.microsoft.com/analysisservices/2012/engine/400/400"">" & _
    "       <KeyErrorLimit>-1</KeyErrorLimit>" & _
    "       <KeyNotFound>IgnoreError</KeyNotFound>" & _
    "   </ErrorConfiguration>" & _
    "   <Parallel>" & _
    "       <Process xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" _
    xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" _
    xmlns:ddl2=""http://schemas.microsoft.com/analysisservices/2003/engine/2"" _
    xmlns:ddl2_2=""http://schemas.microsoft.com/analysisservices/2003/engine/2/2"" _
    xmlns:ddl100_100=""http://schemas.microsoft.com/analysisservices/2008/engine/100/100"" _
    xmlns:ddl200=""http://schemas.microsoft.com/analysisservices/2010/engine/200"" _
    xmlns:ddl200_200=""http://schemas.microsoft.com/analysisservices/2010/engine/200/200"" _
    xmlns:ddl300=""http://schemas.microsoft.com/analysisservices/2011/engine/300"" _
    xmlns:ddl300_300=""http://schemas.microsoft.com/analysisservices/2011/engine/300/300"" _
    xmlns:ddl400=""http://schemas.microsoft.com/analysisservices/2012/engine/400"" _
    xmlns:ddl400_400=""http://schemas.microsoft.com/analysisservices/2012/engine/400/400"">" & _
    "        <Object>" & _
    "           <DatabaseID>" & sDatabase & "</DatabaseID>" & _
    "        </Object>" & _
    "        <Type>ProcessFull</Type>" & _
    "       </Process>" & _
    "   </Parallel>" & _
    "</Batch>"
    
    Dim sMsg As String = ""
    Dim oResult As Microsoft.AnalysisServices.XmlaResultCollection = oServer.Execute(s)
    For i As Integer = 0 To oResult.Count - 1
        For j As Integer = 0 To oResult(i).Messages.Count - 1
    If sMsg <> "" Then sMsg += "</br>"
            sMsg += oResult(i).Messages(j).Description
        Next
    Next
    
    Response.Write(sMsg)
End Sub

历史

2017年4月27日：初始版本