MDX, ADOMD, VB.NET & SQL Server Analysis Services

0 comments Microsoft, SharePoint, Sql Server 2008

Jan 06


YouTube Converter
Recently, we’ve jumped into the world of multi-dimensional OLAP.  We’re currently using SQL Server Analysis Services which has been great to create the data cubes and Microsoft Sharepoint with Business Scorecard Manager to render the cubes.  The database and cube work wonderfully.  We have had issues with Scorecard Manager 2005 though (namingly, it doesn’t render well to Firefox and since one of our clients is Indiana University who supports Firefox, this becomes an issue for us).
Our solution to this problem is that we’re going to create our own scorecards until a service pack comes out for BSM or the next version fixes the rendering issues.  The cube is really the critical technology for us.  We can render our Scorecard one of two ways, via an ASP.NET website or since we’re using Sharepoint via a custom webpart.  The ASP.NET site would be much easier however the custom Sharepoint webpart is attractive because it leverages the Sharepoint framework’s ability to allow content managers (e.g., not me) to customize the look and feel (as well as any other property you allow them to control).  Our first hurdle is how to read data from the OLAP Cube using .NET.  This is the example code I ran from a VB.NET form.  I put a text box as textbox1 on the main form and this code in the load event.  It runs an MDX query then builds a string it puts in a text box.  The string contains the row name, the year in this case and the value of the column.
You will need to add a reference in your project to Microsoft.AnalysisServices.AdomdClient and then add an Import for it at the top of your code (or fill out the full references).

‘*******************************************************************************

‘ Local Declarations
‘*******************************************************************************
Dim Buf As String = “”
Dim Mdx As String = “”

‘*******************************************************************************

‘ Declare and Setup the Connection

‘*******************************************************************************

Dim Connection_String As String = “PROVIDER=MSOLAP.2;DATA SOURCE=<Your Server Here>;INITIAL CATALOG=<Your Cube Here”

Dim Conn As New Microsoft.AnalysisServices.AdomdClient.AdomdConnection(Connection_String)
Try
Conn.Open()
Catch ex As Exception
MsgBox(ex.Message)
Conn = Nothing
Exit Sub
End Try
‘*******************************************************************************

‘ Create the MDX query, the command and data reader objects and parse the information

‘*******************************************************************************

Mdx = “Select [Measures].[Gift Amount] On Columns, “ & _

“{ LastPeriods(4, [Credit Date].[Fiscal Hierarchy].[Fiscal Year].[2007]) } On Rows “ & _
” From [Dollars-Donors]“
Dim Command As New AdomdCommand(Mdx, Conn)
Dim Dr As AdomdDataReader = Command.ExecuteReader()
While Dr.Read()
Buf = Buf & “Gift Amount for “ & Dr(“[Credit Date].[Fiscal Hierarchy].[Fiscal Year].[MEMBER_CAPTION]“) & “: “ & _
FormatNumber(Dr(“[Measures].[Gift Amount]“).ToString, 2, TriState.False, TriState.True, TriState.True) & vbCrLf
Buf = Buf & “———————————————————-” & vbCrLf
End While
TextBox1.Text = Buf

‘*******************************************************************************

‘ Cleanup

‘*******************************************************************************

Conn.Close()

Conn = Nothing