Accessing OLAP Server Data with ADO.NET

On-Line Analytical Processing (OLAP) is a service that represents raw data in a variety of views of information that's easily understandable by the users. OLAP provides consistent and fast access to the data, which can be represented in personalized views in the real world without considering the size and complexity of the database. For example, OLAP can represent data in the form of three-dimensional cubes and cells. The server involved in this processing is called an OLAP server. The OLAP server is a high-capacity, multiuser data manipulation engine specifically designed to work on multidimensional data structures. The ActiveX Data Objects Multi-Dimensional Library (ADOMD) provides access to OLAP server data. OLAP services extract, summarize, organize, and store data warehouses in multidimensional structures, also known as OLAP server cubes.

To test this OLAP sample, you'll use the FoodMart 2000 database that comes with Microsoft SQL Server Analysis Server. Before testing this sample, you must have SQL Server 2000 Analysis Server running. If you don't have SQL Server 2000 Analysis Server running, you can install it from Microsoft SQL Server CD by selecting SQL Server 2000 Components Install Analysis Services. This option installs SQL Server 2000 Analysis Server on your machine.

Note

Installing Analysis Services may not install the FoodMart 2000 database. You may need to restore the database from the C:\Program Files\Microsoft Analysis Services\Samples\ foodmart 2000.cab file. You can restore a database by using Analysis Manager Meta Data Restore option.

ADOMD functionality is defined in the msadomd.dll library. If this library is not listed in your COM components list, you can use the Browse button on the Add Reference dialog box to browse for it. The default path for this library is C:\Program Files\Common Files\System\ADO (see Figure 20-6).

Figure 20-6: Browsing the msadomd.dll library

After adding a reference to the msadomd.dll, the Add Reference dialog box looks like Figure 20-7.

Figure 20-7: Adding a reference to msadomd.dll library

Now click the OK button to add the reference. This action adds the ADOMD namespace to the project (see Figure 20-8).

Figure 20-8: ADOMD namespace listed in the project namespaces

Now you can use the Imports directive to include the ADOMD namespace in your project and use ADOMD classes.

To test the source code, create a Windows application and add two ListBox controls, two Button controls, a TextBox control, and a Label controls and set their properties (see Figure 20-9).

Figure 20-9: Windows form to test ADOMD

Next, add references of ADOMD and ADODB in the application as follows:

 Imports ADODB Imports ADOMD 

Also, add the following variables in the beginning of the form class:

 Private strConn As String Private dbConn As Connection Private dtCatalog As Catalog Private cubes As CubeDefs 

After that, create Connection and Catalog objects on the form load and use CubeDefs of Catalog to get all the cubes. As you can see from Listing 20-3, you then add all cubes to the ListBox and the number of cubes to the TextBox.

Listing 20-3: Getting All Available Cubes from the FoodMart 2000 Database

 Private Sub Form1_Load(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles MyBase.Load     ' Construct connection string     strConn = "Provider=msolap; Data Source = MCB;" & _     "Initial Catalog = FoodMart 2000; User ID =sa; Pwd="     ' Create and open a connection     dbConn = New Connection()     dbConn.Open(strConn, "", "", _       CType(ConnectModeEnum.adModeUnknown, Integer))     ' Create a Catalog object and set it's active connection     ' as connection     dtCatalog = New Catalog()     dtCatalog.ActiveConnection = CType(dbConn, Object)     ' Get all cubes     cubes = dtCatalog.CubeDefs     ' Set text box text as total number of cubes     TextBox1.Text = cubes.Count.ToString()     Dim cube As CubeDef     For Each cube In cubes       Dim str As String = ""       ListBox1.Items.Add(cube.Name.ToString())       str = "Cube Name :" + cube.Name.ToString() + ", "       str += "Description :" + cube.Description.ToString() + ", "       str += "Dimensions :" + cube.Dimensions.Count.ToString()     Next     ListBox1.SetSelected(0, True)   End Sub 

If you run the application, the output looks like Figure 20-10.

Figure 20-10: All available cubes in the FoodMart 2000 database

The Get Dimensions button gets the dimensions of the selected cube in the left box in Figure 20-10. Listing 20-4 returns the dimensions of a cube and adds it to the right box of Figure 20-10.

Listing 20-4: Getting All Dimensions of a Cube

 Private Sub Button1_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles Button1.Click     ' Get the selected cube     Dim cube As CubeDef = cubes(ListBox1.SelectedItem.ToString())     ' Get all the dimensions of the selecte cube     Dim i As Integer     For i = 0 To cube.Dimensions.Count - 1 Step i + 1       Dim dimen As Dimension = cube.Dimensions(i)       ListBox2.Items.Add(dimen.Name.ToString())     Next     ListBox2.SetSelected(0, True) End Sub 

The output of the Get Dimensions button fills the right box with the dimensions (see Figure 20-11).

Figure 20-11: Getting dimensions of a cube

The Get Dimension Members button returns the properties of a dimension such as name, hierarchies, UniqueName, and Properties (see Listing 20-5).

Listing 20-5: Getting Dimension Members

 Private Sub Button2_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles Button2.Click     ' Get the selected cube     Dim cube As CubeDef = cubes(ListBox1.SelectedItem.ToString())     ' Get the selected Dimension     Dim dimen As Dimension = cube.Dimensions(ListBox2.SelectedItem.ToString())     MessageBox.Show("Dimension Properties :: Name=" + dimen.Name.ToString() & _        ", Description=" + dimen.Description.ToString() + ", Hierarchies=" & _        dimen.UniqueName.ToString())   End Sub 

The output of Listing 20-5 looks like Figure 20-12.

Figure 20-12: Viewing dimension properties