Accessing OLAP using ASP.NET - Creating a .NET Web Application to Access OLAP
(Page 4 of 4 )
This sample web application uses the existing demonstration database named “Foodmart 2000”, which gets automatically installed by SQL Server 2000 Analysis Services (please refer to the previous topic).
This sample application was tested using the following software setup:
- Microsoft Windows 2000 Server
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Analysis Services
- Microsoft Visual Studio.NET 2003 Enterprise Architect
The .NET Framework doesn't natively support data access for multidimensional (i.e., OLAP) data sources. So, OLAP developers have to use existing COM-based ADO MD (Multi Dimensional) for application development. Programming with ADO MD is much like programming with ADO. Like ADO, ADO MD has two primary ways of retrieving information: You can retrieve metadata from the schema rowsets, or you can execute queries.
To start programming with ADO MD from Visual Studio .NET, import the ADO MD type library into your .NET project. Do this the same way as adding a reference in Visual Basic (VB) 6.0. In Visual Studio .NET, open the Project menu and select Add Reference. In the resulting dialog box, click the COM tab and scroll down to select Microsoft ActiveX Data Objects (Multi-dimensional) 2.x Library (as shown in the following figure). After selecting the type library, click Select, and then click OK. You've added the type definitions from the library to your project.
The entire source code is as follows:
Imports ADODB
Imports ADOMD
Imports System.Data.OleDb
Public Class DispInfo
Inherits System.Web.UI.Page
.
.
.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Build the MDX statement(Query)
Dim szMDX As String
szMDX = szMDX & "SELECT "
szMDX = szMDX & "{[Measures].members} ON COLUMNS,"
szMDX = szMDX & _
"NON EMPTY [Store].[Store City].members ON ROWS"
szMDX = szMDX & " FROM Sales"
' Connect to the OLAP server
Dim cn As New ADODB.Connection
cn.Open("provider=msolap;data source=localhost")
cn.DefaultDatabase = "Foodmart 2000"
' Create a cellset
Dim cs As New ADOMD.Cellset
'cs = Server.CreateObject("ADOMD.Cellset")
cs.ActiveConnection = cn
cs.Open(szMDX)
'display the cellset
Me.DataGrid1.DataSource = New DataView(getDataTable(cs))
Me.DataGrid1.DataBind()
'clear the resources
cs.Close()
cn.Close()
End Sub
Private Function getDataTable(ByRef cs As Cellset) As DataTable
'design the datatable
Dim dt As New DataTable
Dim dc As DataColumn
Dim dr As DataRow
'add the columns
dt.Columns.Add(New DataColumn("Description")) 'first column
'get the other columns from axis
Dim p As Position
Dim name As String
Dim m As Member
For Each p In cs.Axes(0).Positions
dc = New DataColumn
name = ""
For Each m In p.Members
name = name + m.Caption + " "
Next
dc.ColumnName = name
dt.Columns.Add(dc)
Next
'add each row, row label first, then data cells
Dim y As Integer
Dim py As Position
y = 0
For Each py In cs.Axes(1).Positions
dr = dt.NewRow 'create new row
' Do the row label
name = ""
For Each m In py.Members
name = name + m.Caption + "<BR>"
Next
dr(0) = name 'first cell in the row
' Data cells
Dim x As Integer
For x = 0 To cs.Axes(0).Positions.Count - 1
dr(x + 1) = cs(x, y).FormattedValue 'other cells in the row
Next
dt.Rows.Add(dr) 'add the row
y = y + 1
Next
Return dt
End Function
End Class
The above example is just a sample, demonstrated to access OLAP information through ADOMD and converting it to the .NET, understandable when using the COM interoperability. The same can be further enhanced with all the necessary ingredients to attain much more efficient presentation of information, including charts.
Any suggestions or questions are welcome to my mail at jag_chat@yahoo.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
