Fetch cube data using .Net code

When I went through MSDN SSAS forum, I found that, there are many professionals who want to know "how to fetch cube data using .net code". Following is one of the similar thread from MSDN forum;
SSAS MSDN Forum thread . So I thought lets discuss this topic here on my blog.

For fetching cube data using .Net code, you will need the reference of Microsoft.AnalysisServices.AdomdClient.dll. If you don't have this dll then you can download that from Microsoft download center here Microsoft download . Open the link and download msi file for "Microsoft SQL Server 2008 R2 ADOMD.NET" as per your server requirement (i.e. X86, X64 or IA64).
After downloading, run the msi file. After successful run go to the location "C:\Program Files\Microsoft.NET\ADOMD.NET\100\" and check either dll named "Microsoft.AnalysisServices.AdomdClient.dll" is present there or not.


Create .net project and add the reference of "Microsoft.AnalysisServices.AdomdClient.dll" under reference node. 

After addition of dll reference, you can import the AdomdClient namespace and use different classes and objects of the same namespace.

After all above steps, you can write following code which will fetch cube data and fill that in a data table.



        Dim objConnection As New AdomdConnection("Data Source=localhost;Initial Catalog=Adventure       Works DW 2008R2")
        Dim objCommand As New AdomdCommand()
        Dim objDatatable As New DataTable
        Dim strCommand As String


        strCommand = "SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,"
        strCommand = strCommand & " [Product].[Category].[Category] ON ROWS "
        strCommand = strCommand & " FROM [Adventure Works]"


        objConnection.Open()
        objCommand.Connection = objConnection
        objCommand.CommandText = strCommand
        Dim objDataAdapter As New AdomdDataAdapter(objCommand)
        objDataAdapter.Fill(objDatatable)


        objConnection.Close()