Accessing Microsoft Analysis services using ASP

Oh!! I wasn’t aware that ADOMD makes it so easy. Using ADOMD class Library, I have created a small asp script which can make a connection with the Microsoft Analysis services and run a MDX query. The APIs also allows catching an error i.e the error while attempting an invalid MDX can also be displayed.

To start with we need to prepare Analysis services instance for connection. What this means is the instance should allow access to IUSR_MACHINENAME. This can be achieved by adding a role in the catalog you wish to connect to. Secondly, you should know the connection string which should be used for connection to the instance. For a local instance, most probably it will be

“Data Source=localhost;Provider=MSOLAP;”

Once this is done we are ready to connect to the AS instance.

1. Declare the connection properties i.e the connection string and the default database

      1: connString = "Data Source=localhost;Provider=MSOLAP;"

      2: defaultDB = "Analysis Services Tutorial"

2. Connect with the Microsoft Analysis services instance, using the connection properties used above

      1: 'Create a connection to the Adventure Works OLAP Database

      2:  Set cnn = Server.CreateObject("ADODB.Connection")

      3:  cnn.Open  connString

      4:  

      5:  If Err.Number <> 0 Then

      6:     Response.Write Err.Description

      7:  else    

      8:     cnn.DefaultDatabase = defaultDB

      9:  

     10: 'Access the Adventure Works cube catalog

     11:  Set cat = Server.CreateObject("ADOMD.Catalog")

     12:  Set cat.ActiveConnection = cnn

3. After step 2, we have established a connection with the Analysis services instance, and we can now use the cnn connection object to query the server

      1: Set cel = Server.CreateObject("ADOMD.Cellset")

      2: cel.Source = strMDX

      3: cel.ActiveConnection = cnn

      4: cel.Open

4. cel.open above has triggered an MDX query (i.e. strMDX here), the result of which can be read from cel (CellSet Object). I wrote a small routine to show the returned set in an HTML table

Set colAxis = cel.Axes(0)

Set rowAxis = cel.Axes(1)

Set firstColPos = colAxis.Positions(0)

colDepth = colAxis.Positions(0).Members.Count

if rowAxis Then

   Set firstRowPos = rowAxis.Positions(0)

   rowDepth = firstRowPos.Members.Count

else 

    rowDepth = 0

end If

cellIndex = 0

Response.Write "<TABLE>"

    Response.Write "<TR>"

    For iCount = 1 To rowDepth

            Response.Write "<TD rowSpan="& colDepth &">  </TD>"

    Next

    For Each pos In colAxis.Positions

     Response.Write "<TD class=headerStyle><b>"

     Set m = pos.Members(0)

     Response.Write pos.Members(0).Caption & "</b></TD>"

    Next

    Response.Write "</TR>"

    For cCount = 2 To colDepth

        Response.Write "<TR>"

        For Each pos In colAxis.Positions

         Response.Write "<TD class=headerStyle><b>"

         Response.Write pos.Members(cCount-1).Caption & "</b></TD>"

        Next

        Response.Write "</TR>"

    Next

   if rowAxis Then

    For Each pos In rowAxis.Positions

        Response.Write "<TR>"

        For iCount = 1 To rowDepth

            Response.Write "<TD class=headerStyle><b>" & pos.Members(iCount-1).Caption & "</b></TD>"

        Next

        For iCount = 1 To cel.Axes(0).Positions.Count

             if cel(cellIndex) Then

                 Response.write "<TD class=data>" & cel(cellIndex).FormattedValue & "</TD>"

             else

                 Response.write "<TD class=data>NaN</TD>"

            end If

             cellIndex = cellIndex + 1

        Next

        Response.Write "</TR>"

        Next

  else

     For iCount = 1 To cel.Axes(0).Positions.Count

         if cel(cellIndex) Then

             Response.write "<TD class=data>" & cel(cellIndex).FormattedValue & "</TD>"

         else

              Response.write "<TD class=data>NaN</TD>"

         end If

         cellIndex = cellIndex + 1

      Next

  end If

Response.Write "</TABLE>"

The full source for the OLAP Application can be downloaded from here.

Here output which you get when querying for

Select ( [Order Date].[CalendarYear].members) ON COLUMNS, ([Customer].[English Education].Members) ON ROWS From [Adventure Works DW] Where [Measures].[Sales Amount]

Output:

Filed under: OLAP