The NEXT thing to try!!

Icon

Random rants and tips about NETCF, WinCE, Emacs, Powershell etc.

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
  5. 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:

asresult

About these ads

Filed under: OLAP

3 Responses

  1. […] Check it out! While looking through the blogosphere we stumbled on an interesting post today.Here’s a quick excerptUsing 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 ie the error while attempting an invalid MDX can … […]

  2. Idetrorce says:

    very interesting, but I don’t agree with you
    Idetrorce

  3. Sam Kane says:

    Here are this and some other articles on Displaying OLAP Data: http://ssas-wiki.com/w/Articles#Displaying_OLAP_Data

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: