ADO NET Programming-Understanding the Data Source

Understanding the Data Source

This section identifies the major low-level parts of Analysis Services and then focuses on its architecture for the purpose of our example. Analysis Services has two parts:

The OLAP Server, which stores the data
The PivotTable Service, which is a set of tools that allows data manipulation from the client

For our example, we are going to take a look at the sample FoodMart 2000 database that comes with Analysis Services. The general relationship of the client-server data manipulation architecture of Analysis Services is illustrated on the following page.

Figure 9-4: Analysis Services architecture

The Relational Database

The relational database for the FoodMart 2000 sample is implemented as a Microsoft Access application. It is located under the Samples folder, which is installed inside the Analysis Services installation directory. This path depends on where you originally specified the installation directory of Analysis Services. There are two files inside this directory that we are going to look at:

FoodMart 2000.mdb: This is the actual relational database from which the OLAP database is created.
FoodMart 2000.cab: This is an archive of the OLAP database provided by the installation process. We will restore this database after our discussion of the relational structure of the FoodMart 2000.mdb.

The FoodMart 2000 database keeps track of sales activities for a fictional company called FoodMart. Critical information about customers, products, employees, warehouses, and purchases are kept inside the database. It is not our goal to examine the exact design of the database, so I will not go into a detailed discussion of the database structure. Figure 9-5 provides a quick summary of the relational structure of the FoodMart database.

Figure 9-5: The relational structure of the FoodMart database

The OLAP Database

The OLAP Database Server stores data in a multidimensional format. The data is grouped into cubes that the database designer specifies.

Populating the OLAP Database

In general, an OLAP database is a warehouse database. It follows that the data must be available from a different source in order for it to be populated. In Figure 9-4, the OLAP database is using data from four separate databases: Accounts, Sales, Human Resources, and CRM. Data from these four databases is combined and stored in the OLAP database for future analysis. The data source from which to obtain data for an OLAP database does not necessarily have to be a relational data source. It can be any form of data that you want to analyze. This includes text files, Windows Active Directory, and Exchange Server, just to name a few.

How is the Data Stored?

The data in an OLAP database is stored inside cubes. A cube is simply a conceptual way of expressing a multidimensional storage model. OLAP cubes have three storage architectures: MOLAP, ROLAP, and HOLAP. For a more in-depth overview of multidimensional storage architecture, see Professional SQL Server 2000 Data Warehousing with Analysis Services.

PivotTable Service

The PivotTable Service is the client tool used to access, retrieve, and manipulate multidimensional data from the OLAP Server.

Note

You cannot update an OLAP data source because it is simply an Analysis Server and nothing more. It is not designed to provide OLTP support.

The PivotTable Service has a very loose architecture that you must understand before attempting to program for Analysis Services. The PivotTable Service architecture has two main components to start with: the OLEDB Provider for OLAP and ADO MD. We will take an in-depth look at both of these technologies as we proceed through this chapter.

OLEDB Provider for OLAP

OLEDB Provider for OLAP is one of the thousands of OLEDB providers available on the market today. Microsoft developed the OLEDB Provider for OLAP MSOLAP driver to be used specifically with any OLAP database, although it is optimized for Analysis Services. OLEDB Provider for OLAP is to Analysis Services what the SQLOLEDB driver is to SQL Server. To manipulate OLAP data, developers can use ADO MD, implement their own consumers for OLEDB for OLAP, or use the provider’s API in their code. Expect to see more extensive .NET-enabled tools out with the next version of SQL Server Analysis Services.

Multidimensional Expressions (MDX)

MDX is the data manipulation language (DML) used for OLAP data sources. Its syntax is very close to that of its predecessor, SQL. The only difference between the two is that the latter can only manipulate the relational data source, while MDX is used in a multidimensional scenario. Therefore, MDX is simply an optimized version of SQL. OLEDB providers, such as SQLOLEDB, cannot process MDX queries. However, MSOLAP can process SQL queries, and it is possible to query OLAP data sources with SQL.

This section does not cover the entire MDX specification as released by Microsoft. We will walk through some simple syntax in our examples and explain the semantics of the language. Unless you have a good knowledge of cubic data storage, it is impossible to fully understand the MDX syntax. For a full discussion of the topic, see Professional SQL Server 2000 Data Warehousing with Analysis Services.

ActiveX Data Object Multidimensional (ADO MD)

For those who are reading about it for the first time, ADO MD may seem to be something new. In fact, it is not, at least if you are accustomed to ADO. ADO MD is an elegant COM component library that is simply an extended version of ADO (ActiveX Data Object). While Microsoft implemented ADO as a consumer of OLEDB, ADO MD is a consumer to OLEDB Provider for OLAP. It is virtually impossible to retrieve multidimensional data using ADO. The objects in ADO MD have been optimized to do this job efficiently and seamlessly.

Figure 9-6 provides you with a nice view of the ADO MD object model. In the next few sections, you will be reading and learning more about it. There is even an example of how to use it in your code to retrieve and manipulate OLAP data on the client application.

Figure 9-6: The ADO MD object model

As I explain the concepts of ADO MD, I will also illustrate how to use it through the implementation of a small Windows Form application. The code is very easy and straightforward to follow. It is provided on the companion CD.

Visiting the Design of the Database

Through the ADO MD objects, it is possible for you to obtain every single detail of knowledge you require about the structure of an OLAP data source. It is possible to take a look at all the objects in the data source. For this small section, our focus is on Part A of Figure 9-6. We will discuss the route from the Catalog object to the Level object.

When operating on an OLAP database, the Catalog object represents the entire database. It has three properties: Name, ActiveConnection, and CubeDefs. The name of the Catalog object in our example would be FoodMart 2000. The properties and their purposes are illustrated in Table 9-4:

Table 9-4: Properties of the Catalog object
Property	Purpose
Name	Specifies the name of the OLAP database. This property is read-only.
ActiveConnection	This defines the connection string used to connect to the database. This property is read and write. Upon assigning a valid string value to it, the Catalog object automatically connects to the data source.
CubeDefs	This property is a collection of all the Cube objects referencing all the cubes that are available in the database referenced by the Catalog object.

A Cube object is a reference to one OLAP cube in the database. It is not possible to access a cube directly using the Cube object. You must have a valid Catalog object with a CubeDefs collection containing all the available cubes. The properties of the Cube object are discussed in Table 9-5.

Table 9-5: Properties of the Cube object
Property	Purpose
Name	Specifies the name of the OLAP cube. This property is read-only.
Dimensions	This is a collection of all the dimensions available for the particular cube.

The FoodMart 2000 database contains six cubes:

Budget
HR
Sales
Trained Cube
Warehouse
Warehouse and Sales

The Dimension object is one dimension of an OLAP cube. It can be instantiated using one item in the Dimensions collection of a Cube object. The properties of the Dimension object are discussed in Table 9-6.

Table 9-6: Properties of the Dimension object
Property	Purpose
Name	Specifies the name of the cube dimension. This property is read-only.
Hierarchies	This is a collection of all the hierarchies available for the particular dimension.

A Hierarchy object stands for a valid hierarchy of a dimension. It is initialized using an item from the Hierarchies collection property of a Dimension object. The properties of the Hierarchy object are discussed in Table 9-7.

Table 9-7: Properties of the Hierarchy object
Property	Purpose
UniqueName	Specifies the name of the cube dimension. This property is read-only.
Levels	This is a collection of all the levels available for the particular Hierarchy. It is important to note that while inside a database, levels have different positions and can be children of another level; this is not the case inside the Levels collection. Level objects maintain their child-parent state through one of their own properties.

In this example, you take a look at the Windows application that retrieves an OLAP database design.

This is the code for the form:

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim objCatalog As Object

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()
        'This call is required by the Windows Form
        'Designer.
        InitializeComponent()
        'Add any initialization after the 
        'InitializeComponent() call
    End Sub

    'Form overrides dispose to clean up the component
    'list.
    Protected Overloads Overrides _
    Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As _
    System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the
    'Windows Form Designer
    'It can be modified using the Windows Form Designer.
    'Do not modify it using the code editor.
    Friend WithEvents Label1 As _
    System.Windows.Forms.Label

    Friend WithEvents lblCatalogName As _
    System.Windows.Forms.Label

    Friend WithEvents lstCubes As _
    System.Windows.Forms.ListBox

    Friend WithEvents Label2 As _
    System.Windows.Forms.Label

    Friend WithEvents lstDimensions As _
    System.Windows.Forms.ListBox

    Friend WithEvents lstHierarchies As _
    System.Windows.Forms.ListBox

    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.Label1 = New System.Windows.Forms.Label()
        Me.lblCatalogName = New
          System.Windows.Forms.Label()
        Me.lstCubes = New System.Windows.Forms.ListBox()
        Me.Label2 = New System.Windows.Forms.Label()
        Me.lstDimensions = New
          System.Windows.Forms.ListBox()
        Me.lstHierarchies = New
          System.Windows.Forms.ListBox()
        Me.SuspendLayout()
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(8,
          16)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(80, 16)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "Catalog Name:"
        '
        'lblCatalogName
        '
        Me.lblCatalogName.Location = New _
        System.Drawing.Point(88, 16)

        Me.lblCatalogName.Name = "lblCatalogName"
        Me.lblCatalogName.Size = New
          System.Drawing.Size(272, 23)
        Me.lblCatalogName.TabIndex = 1
        '
        'lstCubes
        '
        Me.lstCubes.Location = New System.Drawing.Point(8,
          56)
        Me.lstCubes.Name = "lstCubes"
        Me.lstCubes.Size = New System.Drawing.Size(120,
          134)
        Me.lstCubes.TabIndex = 2
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(8,
          40)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(100, 16)
        Me.Label2.TabIndex = 3
        Me.Label2.Text = "Cubes:"
        '
        'lstDimensions
        '
        Me.lstDimensions.Location = New _
        System.Drawing.Point(136, 56)

        Me.lstDimensions.Name = "lstDimensions"
        Me.lstDimensions.Size = New
          System.Drawing.Size(120, 134)
        Me.lstDimensions.TabIndex = 4
        '
        'lstHierarchies
        '
        Me.lstHierarchies.Location = New _
        System.Drawing.Point(264, 56)
        Me.lstHierarchies.Name = "lstHierarchies"
        Me.lstHierarchies.Size = New
          System.Drawing.Size(120, 134)
        Me.lstHierarchies.TabIndex = 5
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5,
          13)
        Me.ClientSize = New System.Drawing.Size(392, 195)

        Me.Controls.AddRange(New
          System.Windows.Forms.Control() _
        {Me.lstHierarchies, Me.lstDimensions, Me.Label2, _
        Me.lstCubes, Me.lblCatalogName, Me.Label1})

        Me.FormBorderStyle = _
        System.Windows.Forms.FormBorderStyle.FixedTool-
          Window

        Me.Name = "Form1"
        Me.Text = "Example 1"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Form1_Load(ByVal sender As System.
      Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
        ReadCatalog()
    End Sub

    Public Function Connect()
        Dim strConn As String

        strConn = "Data Source= LOCALHOST; Provider=
          MSOLAP;" _
                & "Database=FoodMart 2000;UserId=;"  
                & "Password=;"
        'create the COM object
        objCatalog = CreateObject("ADOMD.Catalog")
        'Connect to the database
        objCatalog.ActiveConnection = strConn
    End Function

    Public Function ReadCatalog()
        Connect()
        lblCatalogName.Text = objCatalog.name
        GetCubes()
    End Function

    Public Function GetCubes()
        Dim Cube As Object
        For Each Cube In objCatalog.CubeDefs
            lstCubes.Items.Add(Cube.name)
        Next
    End Function

    Public Function GetDimensions()
        Dim Dimension As Object
        lstDimensions.Items.Clear()

        For Each Dimension In _
        objCatalog.CubeDefs(lstCubes.SelectedItem).
            dimensions
            lstDimensions.Items.Add(Dimension.name)
        Next
    End Function

    Public Function GetHierarchies()
        Dim Hierarchy As Object
        lstHierarchies.Items.Clear()
        For Each Hierarchy In _
        objCatalog.CubeDefs(lstCubes.SelectedItem).
          Dimensions( _
        lstDimensions.SelectedItem).Hierarchies
            lstHierarchies.Items.Add(Hierarchy.UniqueName)
        Next
    End Function

    Private Sub lstCubes_SelectedIndexChanged
      (ByVal sender As  
    System.Object, ByVal e As System.EventArgs) Handles _
    lstCubes.SelectedIndexChanged
        GetDimensions()

    End Sub

    Private Sub lstDimensions_SelectedIndexChanged _
    (ByVal sender As System.Object, ByVal e As _
     System.EventArgs) Handles _
    lstDimensions.SelectedIndexChanged

        GetHierarchies()
    End Sub
End Class

Figure 9-7 shows the Visual Studio .NET Designer with Form1 in design view:

Figure 9-7: The Visual Studio .NET Designer

Figure 9-8 shows the output of the sample code and the Form1 object:

Figure 9-8: A view of the OLAP database containing the output of the Form1 object

You can select a particular cube from the Cubes list box to view a list of its dimensions in the Dimensions list box. Each dimension may have one or more hierarchies listed in the Hierarchies list box.