ASP.NET Page to Process SSAS Cubes

Igor Krupitsky

Rate me:

3.00/5 (2 votes)

27 Apr 2017CPOL1 min read

12.1K

179

ASP.NET page that lets you process an SSAS (SQL Server Analysis Services) cube database.

Download source - 738.8 KB

Introduction

SQL Server Analysis Services is usually processed daily, but sometimes, you want to give your users the ability to refresh the database on demand. This page lets you do that.

If there are any problems during the processing, the page will show you the error text.

Installation

Download Cube.zip
Unzip and copy to C:\inetpub\wwwroot\Cube
Open Cube.aspx.vb in Notepad. Change lines 4 and 5 to your server and SSAS database.
Open Cube.aspx in Notepad. Change Line 14 to the time estimate it takes to process your SSAS database.
Point your browser to http://YourServerName/Cube/Cube.aspx.

Background

This page uses the following third party components:

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

Using the Code

The Cube.aspx will let you process the cube asynchronously by using jQuery's $.get.

ASP.NET

 <%@ 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>

The Cube.aspx.vb code behind page will list cubes and their last processed time. Line 27 "oDatabase.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)" does the actual work of processing the database.

VB.NET

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

Other Things to Try

Instead of using Database.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull), we can try to run XMLA script using oServer.Execute(). This would give us greater flexibility like the ability to ignore KeyNotFound errors.

VB.NET

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

History

27^th April, 2017: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Igor Krupitsky

Web Developer

United States

Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida. He also has following professional certifications: MCSD, MCDBA, MCAD.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.