OLAP Using ASP.NET - All The Code

(Page 7 of 7 )

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 + "
"
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 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.

Comments On: OLAP Using ASP.NET

>>> Be the FIRST to comment on this article!