This article is about how to use CubeMaker in ASP

This article describes web-based interface for interactive OLAP reports building with Contour CubeMaker tool. It can be useful to create analytical services for remote clients and company staff.

The data is provided in microcubes – compact data stores created from arbitrary sources. Contour Microcube (a .cube file) is a local multidimensional database that contains:

  •  data obtained from relational database (s);
  •  metadata that describes physical microcube structure;
  •  Metadata that describes all the detail of report layout and appearance.

So, a microcube stores both data and user interface layout. Contour Microcube contains data in compressed form, which allows to use it for transmission of considerable amounts of information through the Web. The compression rate of microcube data is about 40 times.
 

Microcubes are created with a command-line utility named Contour CubeMaker. It builds microcubes, ruled by previously written scripts and allows scheduling, which is useful to maintain cube data actual. Contour CubeMaker itself is built on ContourCube software component.

ContourCube is high-performance OLAP component released by the British company Contour Components Ltd, and designed for fast building of laptop, server and web-based applications of Business Intelligence (BI), and for extending analytical capabilities of ERP systems. Users of applications powered by ContourCube can execute interactive queries to relational databases, carry out OLAP manipulations and statistical analysis, chart and create presentation quality reports.
 

Software and programming language

 

To create microcubes from a web page MS Internet Information Services (IIS) and Contour CubeMaker are required. All program code is written in Visual Basic Scripting language using ASP (Active Server Pages) technology. Initial IIS setup includes specifying server home directory, allowing scripts executing and setting up default document.

Virtual Directory setup
Figure 1. Virtual Directory setup

Default Document setup
Figure 2. Default Document setup
 

Algorithm

 

The working algorithm is following: when a user visits the page, he is asked to choose a predefined script (e.g. sales by regions or sales by good categories) and report transfer mode: show in HTML page body, or send by email. In this case, user have to specify his email address and choose whether he wants to receive the report in message body, or as an attachment (.cube file).

A sample algorithm
Figure 3. A sample algorithm
 

Detailed algorithm description

 

Global constants are defined in the heading section of ASP code:

'These string constants specify base paths for all files required by this demo
'Root of the ASP server
BaseURL = "http://Admin:8000/ASP"

'Local ASP root path
BaseDir = "w:\www\ASP\"

'Relative URL to store result cubes
CubePath = "/"

'Local path where microcube scripts are stored
SettingsPath = "w:\www\ASP\"

'Path and filename of the HTML responce template
ObjTemplate = "w:\www\ASP\obj.tpl"

'Location of the CubeMaker program
CubeMaker = "W:\ISL\CubeMaker\CubeMaker.exe "

'Mail delivery settings
MailServer = "Exchange"
SMTPUser = "Domain\tester"
SMTPPassword = "tester"

'Time to wait before sending generated cube as mail attachment.
Required to wait while CubeMaker creates the file
PostDelay = 5

Then, unique values of master fields are selected from database table and copied into combo boxes on the main page.
An instance of ADODB.Connection is required to connect to the database. In this sample, a DBASE IV table is used as a data source:

Dim Conn: Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & BaseDir &
  ";Extended Properties=""DBASE IV;"";"
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT DISTINCT Region FROM Sales"
rs.Open sql, Conn, 3, 3

In cycle through the result set, the Region combo box is populated with field values:

Response.Write "var region='"
Do Until rs.EOF
  Response.Write "<option value=""" & rs.Fields("region").Value &
    """>" & rs.Fields("region").Value
  rs.MoveNext
Loop
Response.Write "';" & vbCrLf
rs.Close

Same way, the Category combo is filled with data. Then, in function of whether the form is submit (i.e. user has made the choice), the microcube creation begins, or initial form is shown:

<% If request("submit") <> "" Then
‘Microcube building with respect to user-entered parameters
%>
<% Else %>
<!--HTML of the form asking user to specify build parameters(Fig. 4) -->
<% End If %>

Main form appearance
Figure 4. Main form appearance


Let’s examine in detail the main section of the algorithm. In the beginning, Contour CubeMaker must be launched with parameters entered by user. This is done by creating an object of class Wscript.Shell and calling its Run method:

Set server_shell = Server.CreateObject("wscript.shell")
server_shell.Run "%comspec% /c " & CubeMaker & "-l " & SettingsPath &
  "CubeMaker.log" & " -q '""" & request("cat") & """' " & SettingsPath &
  request("type") & ".xcube"

Note:  Be extremely careful when executing shell commands from a web server is insecure. It can lay in cause of vulnerabilities in the system, and there is no warranty that the launched application terminates before server timeout. This sample is created for demonstration purposes only and may not be used in industry.
Composing cube file name and its URL from user parameters:

CubeName = request("type") & ".cube"
CubeURL = BaseURL & CubePath & CubeName
CubeFile = BaseDir & CubeName

Because the same page template is used when showing the report on a web page and sending it in message body, it is read into Body variable, and appended with cube URL and command line used to launch CubeMaker (for test purposes).

Set objFSys = Server.CreateObject("Scripting.FileSystemObject")
Set objFile = objFSys.OpenTextFile(ObjTemplate, 1)
Body = replace(objFile.readall(), "{CubeURL}", CubeURL)
Body = replace(Body, "{CmdLine}", "%comspec% /c " & CubeMaker & "-l " &
  SettingsPath & "CubeMaker.log" & " -q '""" & request("cat") & """' " &
  SettingsPath & request("type") & ".xcube")
objFile.Close

Determining what delivery mode is chosen. If show on web page, it’s just posted “as is”:

If request("transport") = "http" Then
  response.write("<h4 align=""center"">" & Subj & "</h4>")
  response.write(body)

Web page with created report
Figure 5. Web page with created report


If user has chosen to send report by email, the procedure is more complicated. First of all, a CDO.Configuration object must be created and set up with SMTP parameters. (SMTP, Simple Mail Transfer Protocol used to send email messages):

Else
Set Conf = CreateObject("CDO.Configuration")
With Conf.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = MailServer
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = SMTPUser
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = SMTPPassword
.Update
End With

Then, the message itself is created:

Set Msg = CreateObject("CDO.Message")
Set Msg.Configuration = Conf
Msg.Subject = Subj
Msg.To = request("email")
Msg.From = "Contour_CubeMaker_ASP_Demo"

In function of what type of message (plain or with attachment) user has chosen, different emails are built:

If request("attach") = "1" Then
StartWait = Timer
While Timer - StartWait < PostDelay
  'Waiting while CubeMaker builds the cube...
Wend
Msg.AddAttachment CubeFile
Msg.TextBody = "Congratulations! If you read this message, Contour
CubeMaker ASP Demo has succesfully generated this content."& vbCrLf
Msg.TextBody = Msg.TextBody & Subj & ". See report in attachment" & vbCrLf
Msg.TextBody = Msg.TextBody & "Command line string used to build this report is:" & vbCrLf
Msg.TextBody = Msg.TextBody & "%comspec% /c " & CubeMaker & "-l " &
  SettingsPath & "CubeMaker.log" & " -q '""" & request("cat") & """' " &
  SettingsPath & request("type") & ".xcube"
Else
Set Bp = Msg.GetInterface("IBodyPart")
Bp.ContentMediaType = "text/html"
Set Stm = Bp.GetDecodedContentStream
Stm.WriteText Body
Stm.Flush
End If

Finally, the message is sent:

Msg.Send

Email message containing active ContourCube in HTML body
Figure 6 . Email message containing active ContourCube in HTML body
 

Conclusion

 

Contour CubeMaker is a flexible microcube building tool that allows obtaining various microcube files, using different configuration scripts and templates on user’s request or even on the fly. A web server powered by Contour CubeMaker provides full-featured OLAP analysis capability from everywhere in the world without need of any specific applications, except a web browser or an internet mail client.

As shown in the example, developing such solutions is not difficult, whereas it can add a big value to any analytical system powered with this technology. Among lots of other examples, a subscription service that periodically emails microcubes to subscribed users, and many other easy-to-implement services adding Business Intelligence to your applications.

See also