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
.
<%@ 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) + "%";
$(
}, 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.
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
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.
Private Sub ProcessSsas()
Dim oServer As New Microsoft.AnalysisServices.Server()
oServer.Connect(GetConnectionString())
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
- 27th April, 2017: Initial version