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

Posted: 10/16/2006

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.

The prerequisites you may need can be found at: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

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).