This documentation is archived and is not being maintained.

Programming OLAP Databases from Microsoft Access Using DSO

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
 

Frank C. Rice
Microsoft Corporation

April 2002

Applies to:
    Microsoft® Access 2002
    Microsoft SQL Server™ 2000 Analysis Services

Summary: Create and manipulate OLAP databases and cubes on an Analysis server using the Decision Support Objects (DSO) library from a Microsoft Access database. (33 printed pages)

Download ODC_DSODemo.exe.


Contents

Introduction
Why Use DSO?
The Access Sample Database
Creating the Connection
The frmDSODemo Form
The MDStore Interface
Getting Information About the Analysis Server
Creating a Database and a Cube
Removing the Database and Cube
Adding and Removing a Data Source
The frmBuildCube Form
Creating a Dimension
Creating a Level
Removing a Dimension and Level
Conclusion

Introduction

Microsoft® SQL Server™ 2000 Analysis Services is designed to help users make sense of the large amounts of business-generated data in order to spot trends, opportunities, and answer questions vital to moving the business forward. Much of this work is done through online analytical processing (OLAP) cubes based on multidimensional databases. Developers and database administrators need to be able to create and manipulate the objects that make up these cubes. This can be done in the Analysis Manager user interface or programmatically through the Decision Support Objects (DSO) library. This article will demonstrate using DSO from a Microsoft Access database to create and manipulate OLAP databases and cubes on an Analysis server.

Microsoft SQL Server 2000 Analysis Services is becoming the application of choice for turning raw data into information that businesses can use to discover trends and opportunities in order to make timely decisions, also known as business intelligence. By using Analysis Services for data management and analysis, organizations can quickly adapt to changing conditions and derive a competitive advantage.

However, Analysis Services is both a product and a platform for building powerful online analytical processing (OLAP) applications. And while Analysis Services is tightly linked to relational databases, it is in fact a multidimensional database information system with all of the necessary OLAP structures such as cubes, dimensions, measures, and levels. Definitions of these and other OLAP-related terms are available at Getting Started with SQL Server Books Online.

Creating and manipulating these structures is an important task for administrators and developers charged with developing and maintaining these systems. One way of doing that is through the Analysis Manager user interface provided with Analysis Services. The other method is by using the Decision Support Objects (DSO) object model.

In this article, we will explore creating and manipulating the various OLAP related objects available on an Analysis server. We will do this by using the DSO object model from within Microsoft Visual Basic® for Applications (VBA) code in a sample Microsoft Access database that you can download. For more information on the objects, collections, interfaces, and events within DSO, see the article Decision Support Objects Programmer's Reference.

Why Use DSO?

DSO is the object model used to programmatically create and manipulate OLAP objects in Analysis Services just as the Analysis Manager is used to create and manipulate these objects through a user interface. Using DSO can give you more control over these operations. By using DSO, you could package a set of Analysis Service specific tasks into an executable unit for use at a later date. For example, you could build a packaged script that creates an OLAP partition from a set of grocery store transactions as an automated task at the end of each week. Using DSO allows you to hide the complexities of creating databases, assigning data sources, creating cubes, and so on from a less technical user. In this case, DSO could be used to execute just a subset of the operations available through the Analysis Manager.

In your exploration of DSO and Analysis Services, you may also encounter Microsoft ActiveX® Data Objects Multidimensional (ADOMD). ADOMD is a set of extensions to the ADO application programming interface (API) that is used to query multidimensional data sources. And just as Transact-SQL (T-SQL) is used for querying relational data sources through ADO, ADOMD uses Multidimensional Expressions (MDX) to query multidimensional data sources through ADOMD. You can also get more information about ADOMD and MDX by starting with the article Manipulate and Query OLAP Data Using ADOMD and Multidimensional Expressions.

The Sample Access Database

The sample Microsoft Access 2002 database, included as a download with this article, demonstrates using DSO to program OLAP databases, cubes, and so forth in Analysis Services. This sample demonstrates the code to connect to a local Analysis Services server, create and remove databases, cubes, and dimensions as well as other operations.

Note   To program against the Analysis server and to use the procedures in the sample Access database, you need to have SQL Server 2000 Analysis Services installed, either on your local machine or a server, and set a reference (References dialog box, Tools menu) to the Microsoft Decision Support Objects (msmddo80.dll) library.

The sample database consists of three tables, three forms, and a standard code module. The form frmConnect and the code module modDeclServerObj contain the declarations, controls, and procedures that enable you to connect to the Analysis server. The other two forms (frmDSODemo and frmDSOBuildCube) contain controls and procedures, which are used to demonstrate using DSO to manipulate objects on an Analysis server. The controls and procedures are spread across two forms in order to avoid making one form too unwieldy. The forms and the controls they contain are linked to the tables so that any selections made in the controls are retained after the forms are closed.

Creating the Connection

The form frmConnect and the module modDeclServerObj contain the declarations, controls, and procedures used to create a connection to the Analysis server. The code module contains a single declaration for the DSO Server object as:

Public dsoServer As DSO.Server

The declaration is made in a standard module and declared as a Public variable so that the Server object is available to all of the form class modules in the database.

The form frmConnect contains the Connection String box and a Connect to the Analysis Server button. The form is set as the startup form (on the Tools menu, click Startup, and then enter the form name in the Display Form/Page box) and is displayed when the database is opened.

After the user enters a connection string in the Connection String box and clicks the Connect to the Analysis Server button, the following procedure is executed:

Private Sub cmdConnectToServer_Click()
    'Purpose: Connects to the Analysis server.
    '
    Dim strServerName As String

    On Error GoTo cmdConnectToServer_Click_Err

    ' Get the server name.
    Me![txtConnectString].SetFocus
    strServerName = Me![txtConnectString].Text

    ' Indicate progress to the user.
    DoCmd.Hourglass True

    ' Connect to the server.
    Set dsoServer = New DSO.Server
    dsoServer.Connect strServerName

    ' Hide this form and open the frmDSODemo form.
    Me.Visible = False
    DoCmd.OpenForm "frmDSODemo"

cmdConnectToServer_Click_End:
    DoCmd.Hourglass False
    Exit Sub

cmdConnectToServer_Click_Err:
    Select Case Err.Number
        Case mderrConnectError
            MsgBox "A connection to the Analysis server could " & _
                "not be established. Please retry later. "
        Case Else
            MsgBox "Error " & Err.Number & ": " & _
                Err.Description, , "cmdConnectToServer"
    End Select
    Resume cmdConnectToServer_Click_End
End Sub

In the procedure, a connection to the Server object is created, the frmConnect form is hidden, and the frmDSODemo form is opened so that the user can begin to work with the objects in DSO. The DSO Server object takes the name of the computer on which the Analysis server is running. You can type LocalHost in the Connection String box on the frmConnect form to specify the Analysis server running on the same computer as your DSO application.

The frmDSODemo Form

The form frmDSODemo contains the controls and procedures used to query and display basic information about the Analysis server and the objects it contains. The form also demonstrates creating and deleting databases and cubes. Let's examine the various procedures included in the form.

In the Declarations section of our form module, we declare the DSO objects for the database, cube and data sources used in these procedures. Variables declared here are available to each of the procedures in this form.

...
Dim dsoDb As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dsoDS As DSO.DataSource
...

Looking at these declarations, notice that both the database and cube are declared as a DSO.MDStore object. These objects are part of the MDStore interface. There is also an MDStores collection for many of the objects that you can create with DSO. The next section discusses the MDStores collection and MDStore interface in a little more detail.

The MDStore Interface

Some of the objects used within DSO implement a single interface called MDStore (short for multidimensional store) which represents objects that contain multidimensional data. DSO interfaces are conceptually similar to the Component Object Model (COM) interfaces in that they provide a way for objects to expose similar functionality. However, they are different in that they are not implemented as ActiveX controls.

So why implement an interface for these objects? One example, consider that there are regular cubes, virtual cubes, and linked cubes. The DSO designers could have created a separate object for each of these types of cubes with nearly identical properties. Instead, they choose to create different objects that share a common interface, the MDStore interface. In our previous example, an object of ClassType clsCube contains a SubClassType sbclsVirtual and another SubClassType sbclsLinked for virtual and linked cubes, respectively. The objects that implement the MDStore interface include databases, cubes, partitions, and aggregations. To determine which type of object is being used, the ClassType property of the MDStore interface can be used to return a constant indicating the particular object. The returned constants are:

  • clsDatabase
  • clsCube
  • clsPartition
  • clsAggregation

The MDStore interface provides the collections, methods, and properties to manipulate these objects, their contained objects, and data. And while these objects can be explicitly declared as DSO.Database, DSO.Cube, and so forth, the MDStore interface provides additional functionality (methods and properties) not available when using the objects internal interface. The MDStore interface is used to manipulate these objects while the relationships between objects are maintained through hierarchical linkages using the MDStores collections of the objects and the Server object. For example, the MDStores collection of a server object contains database objects. Database objects contain Cube objects. Cubes contain partitions, and partitions contain aggregations. Together, the MDStore interface and the MDStores collections establish and maintain the hierarchy that defines the structure of OLAP data.

To further illustrate this relationship, you would follow these steps to create a cube in code:

  1. Declare the cube as type MDStore.
  2. Instantiate the Cube object by using the New keyword.
  3. Add the object to the MDStores collection.
  4. Manipulate the object with methods and properties of the MDStore interface.

There are actually six interfaces in Analysis Services:

  • Command—Exposes functionality for defining and managing MDX statements executed on the Analysis server.
  • Dimension—Defines the properties, methods, and collections that you can use to manipulate different types of dimensions: database dimensions, cube dimensions, partition dimensions, and aggregation dimensions.
  • Level—Defines objects that specify the dimension hierarchy.
  • MDStore—Objects that implement the MDStore interface are those that contain dimensions: databases, cubes, partitions, and aggregations.
  • Measure—Objects that implement the Measure interface describe the values stored in cubes, partitions, and aggregations.
  • Role—Objects that implement the Role interface contain access permissions on databases, cubes, and data mining models.

A thorough discussion of these interfaces is beyond the scope of this article. For more information on the interfaces and collections within DSO, see the article Decision Support Objects Programmer's Reference.

Getting Information About the Analysis Server

Looking at the next procedures, the first two functions simply return the database and cube names entered by the user in their respective text boxes.

Function GetDbName() As String
    ' Purpose: Returns the database name.
    '
    Dim strDbName As String

    Me![txtDbName].SetFocus
    strDbName = Me![txtDbName].Text
    If strDbName = "" Then
        MsgBox "The Database Name control cannot be empty. Enter a name
          and try again."
    Else
        GetDbName = strDbName
    End If
End Function

Function GetCubeName() As String
    ' Purpose: Returns the cube name.
    '
    Dim strCubeName As String

    Me![txtCubeName].SetFocus
    strCubeName = Me![txtCubeName].Text
    If strCubeName = "" Then
        MsgBox "The Cube Name control cannot be empty. Enter a name and
          try again."
    Else
        GetCubeName = strCubeName
    End If
End Function

Private Sub cmdBasicInformation_Click()
    ' Purpose: This procedure displays information about the server,
    ' databases, and cubes.
    '
    Dim dsoEdition As OlapEditions

    On Error GoTo cmdBasicInformation_Click_Err

    ' Indicate progress to the user.
    DoCmd.Hourglass True

    ' Clear the list box and populate it with information
    ' about the Server, Database, and Cube objects.
    With lstInfo
        .RowSource = ""

        ' The server object was created in the form frmConnect
        .AddItem "Server: " & dsoServer.Name
        .AddItem "Version: " & dsoServer.Version

        ' Determine the edition of Analysis Services server installed.
        Select Case dsoEdition
            Case olapEditionUnlimited
                .AddItem "Edition:  " & "Enterprise Edition"
            Case olapEditionNoPartitions
                .AddItem "Edition: " & "Standard Edition"
            Case Else
                .AddItem "Edition: " & " (Unknown Edition)"
        End Select

        For Each dsoDb In dsoServer.MDStores
            .AddItem "Database: " & dsoDb.Name & " (" & _
                dsoDb.Description & ")"
            For Each dsoDS In dsoDb.DataSources
                .AddItem "Data Source: " & dsoDS.Name
            Next dsoDS
            For Each dsoCube In dsoDb.MDStores
                .AddItem "Cube: " & dsoCube.Name
            Next dsoCube
        Next dsoDb
    End With

cmdBasicInformation_Click_End:
    DoCmd.Hourglass False
    Exit Sub

cmdBasicInformation_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdBasicInformation_Click"
    Resume cmdBasicInformation_Click_End
End Sub

The next procedure provides information about the local server, databases, and cubes and is executed from the OnClick event of the View server information as you make changes button. After declaring a variable for the OlapEditions object, which will hold the edition of SQL Server 2000 we are using, we use a With. . .End With statement to simplify referencing the properties of the lstInfo list. We then clear the contents of the list and populate it with pertinent information about the server and databases.

...
With lstInfo
    .RowSource = ""
    ' The server object was created in the form frmConnect.
    .AddItem "Server: " & dsoServer.Name
    .AddItem "Version: " & dsoServer.Version
...

Next, we examine the Edition property of the OlapEditions object to determine which edition of the Analysis server we are using and use a Select Case statement to add that information to the list.

...
Select Case dsoEdition
    Case olapEditionUnlimited
        .AddItem "Edition:  " & "Enterprise Edition"
    Case olapEditionNoPartitions
        .AddItem "Edition: " & "Standard Edition"
    Case Else
        .AddItem "Edition: " & " (Unknown Edition)"
End Select
...

And finally, we iterate through the MDStores collection of the Server object to identify the databases contained on the server and add that information and a description of the database, if any, to the list. We do the same for the MDStores collection of each Database object to determine the cubes it contains.

...
For Each dsoDb In dsoServer.MDStores
    .AddItem "Database: " & dsoDb.Name & " (" & _
        dsoDb.Description & ")"
    For Each dsoDS In dsoDb.DataSources
        .AddItem "Data Source: " & dsoDS.Name
    Next dsoDS
    For Each dsoCube In dsoDb.MDStores
        .AddItem "Cube: " & dsoCube.Name
    Next dsoCube
Next dsoDb
...

Creating a Database and a Cube

The next procedure creates a new database on the server and then set some of the Database object's properties:

Private Sub cmdCreateDb_Click()
    ' Purpose: This procedure creates a database and then sets the
    ' Description property.
    '
    Dim strDbDescription As String
    Dim strDbName As String

    On Error GoTo cmdCreateDb_Click_Err
      
    strDbName = GetDbName

    ' If the user doesn't enter a database name, exit.
    If strDbName = "" Then
        GoTo cmdCreateDb_Click_End
    End If
    ' Check to see if the database already exists. If not, create a 
      reference
    ' to it and set its Description property.
    If dsoServer.MDStores.Find(strDbName) Then
        MsgBox "Database " & strDbName & " already exists."
    Else
        Set dsoDb = dsoServer.MDStores.AddNew(strDbName)
        Me![txtDbDescription].SetFocus
        strDbDescription = Me![txtDbDescription].Text
        dsoDb.Description = strDbDescription

        dsoDb.Update
        MsgBox "Database " & dsoDb.Name & " was created successfully."
    End If
    
cmdCreateDb_Click_End:
    Exit Sub

cmdCreateDb_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdCreateDb_Click"
    Resume cmdCreateDb_Click_End
End Sub

After getting the name of the database entered by the user and making sure it isn't blank, we check to see if that database already exists by using the Find method. If it doesn't exist, we create the new database by using the AddNew method of the MDStores collection of the Server object.

If dsoServer.MDStores.Find(strDbName) Then
    MsgBox "Database " & strDbName & " already exists."
Else
    Set dsoDb = dsoServer.MDStores.AddNew(strDbName)
    Me![txtDbDescription].SetFocus
    strDbDescription = Me![txtDbDescription].Text
    dsoDb.Description = strDbDescription

    dsoDb.Update
    MsgBox "Database " & dsoDb.Name & " was created successfully."
End If
...

Next, we populate the Description property of the database with the text from the Database Description box. We then use the Update method of the Database object to persist the changes and inform the user of the addition.

The next procedure is similar to the previous one except that it creates a cube in the database:

Private Sub cmdCreateCube_Click()
    ' Purpose: This procedure creates a new cube.
    '
    Dim strDbName As String
    Dim strCubeName As String

    On Error GoTo cmdCreateCube_Click_Err

    strDbName = GetDbName

    ' If the user doesn't enter a database name, exit.
    If strDbName = "" Then
        GoTo cmdCreateCube_Click_End
    End If
    ' If the database exists, create a reference to it.
    If dsoServer.MDStores.Find(strDbName) Then
        Set dsoDb = dsoServer.MDStores(strDbName)
    Else
        MsgBox "Database " & strDbName & " not found."
        GoTo cmdCreateCube_Click_End
    End If

    strCubeName = GetCubeName

    If strCubeName = "" Then
        GoTo cmdCreateCube_Click_End
    End If
    ' Check to see if the cube already exists. If not, create a
      reference
    ' to it.
    If dsoDb.MDStores.Find(strCubeName) Then
        MsgBox "Cube " & strCubeName & " already exists for the " & _
            dsoDb.Name & " database."
    Else
        Set dsoCube = dsoDb.MDStores.AddNew(strCubeName)
        dsoCube.Update

        MsgBox "Cube " & dsoCube.Name & " was created for the " & _
            dsoDb.Name & " database."
    End If

cmdCreateCube_Click_End:
    Exit Sub

cmdCreateCube_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdCreateCube_Click"
    Resume cmdCreateCube_Click_End
End Sub

In this procedure, we do our checks for the database name and the cube name, create a reference to the database, and then use the AddNew method to add a new cube.

Removing the Database and Cube

The next two procedures are similar to each other in structure. The first is used to delete a database from the Server object. The second is used to delete a cube from the Database object.

Private Sub cmdDeleteDb_Click()
    ' Purpose: This procedure removes a database from the server.
    '
    Dim strDbName As String
    Dim Response As Integer
    ' Return value if user selects Yes to delete confirmation.
    Const Yes = 6

    On Error GoTo cmdDeleteDb_Click_Err

    strDbName = GetDbName

    ' If the user doesn't enter a database name, exit.
    If strDbName = "" Then
        GoTo cmdDeleteDb_Click_End
    End If
    ' If the database exists, prompt the user and then delete it.
    If dsoServer.MDStores.Find(strDbName) Then
        Response = MsgBox("Delete the database " & strDbName & "?", 
          vbYesNo)
        If Response = Yes Then
            dsoServer.MDStores.Remove (strDbName)
            MsgBox "Database " & strDbName & " was removed 
              successfully."
        Else
            GoTo cmdDeleteDb_Click_End
        End If
    Else
        MsgBox "Database " & strDbName & " not found."
    End If

cmdDeleteDb_Click_End:
    Exit Sub

cmdDeleteDb_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdDeleteDb"            
    Resume cmdDeleteDb_Click_End
End Sub

Private Sub cmdDeleteCube_Click()
    ' Purpose: This procedure removes a cube from the database.
    '
    Dim strDbName As String
    Dim strCubeName As String
    Dim Response As Integer
    ' Return value if user selects Yes to delete confirmation.
    Const Yes = 6

    On Error GoTo cmdDeleteCube_Click_Err

    strDbName = GetDbName

    ' If the user doesn't enter a database name, exit.
    If strDbName = "" Then
        GoTo cmdDeleteCube_Click_End
    End If
    ' If the database exists, create a reference to it.
    If dsoServer.MDStores.Find(strDbName) Then
        Set dsoDb = dsoServer.MDStores(strDbName)
    Else
        MsgBox "Database " & strDbName & " not found."
        GoTo cmdDeleteCube_Click_End
    End If

    strCubeName = GetCubeName

    If strCubeName = "" Then
        GoTo cmdDeleteCube_Click_End
    End If
    ' If the cube exists, prompt the user then delete it.
    If dsoDb.MDStores.Find(strCubeName) Then
        Response = MsgBox("Delete " & strCubeName & " from the " & 
          strDbName & _
            " database?", vbYesNo)
        If Response = Yes Then
            dsoDb.MDStores.Remove strCubeName
            MsgBox "Cube " & strCubeName & " was removed from the " & _
                dsoDb.Name & " database."
        Else
            GoTo cmdDeleteCube_Click_End
        End If
    Else
        MsgBox "Cube " & strCubeName & " not found."
    End If

cmdDeleteCube_Click_End:
    Exit Sub

cmdDeleteCube_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdDeleteCube_Click"
    Resume cmdDeleteCube_Click_End
End Sub

In both procedures, we do checks for an empty database or cube name and then check to make sure that the particular object (database or cube) exists. After confirming that the user wants to delete the object, we use the Remove method of their respective parent objects to delete either the database or cube.

Adding and Removing a Data Source

The next procedure is used to connect a data source to a database.

Private Sub cmdConnect_Click()
    ' Purpose: This procedure connects a data source to a database.
    '
    Dim strConnectString As String
    Dim strDbName As String
    Dim strDataSrc As String

    On Error GoTo cmdConnect_Click_Err
    
    ' Get the name of the data source.
    Me![txtDataSrc].SetFocus
    strDataSrc = Me![txtDataSrc].Text

    ' Initialize the ConnectionString property for the data source.
    strConnectString = "Provider=MSDASQL.1;Persist Security 
      Info=False;Data Source=" & _
        strDbName & ";Connect Timeout=15"

    strDbName = GetDbName

    ' If the user doesn't enter a database name, exit.
    If strDbName = "" Then
        GoTo cmdConnect_Click_End
    End If
    ' If the database exists, create a reference to it.
    If dsoServer.MDStores.Find(strDbName) Then
        Set dsoDb = dsoServer.MDStores(strDbName)
        ' Check to see if the data source already exists for this 
          database. If not, create
        ' a reference and connect to it.
        If dsoDb.DataSources.Find(strDataSrc) Then
            MsgBox "Data source " & strDataSrc & " is already connected
              to the " & _
                dsoDb.Name & " database."
        Else
            Set dsoDataSrc = dsoDb.DataSources.AddNew(strDataSrc)
            dsoDataSrc.ConnectionString = strConnectString

            dsoDataSrc.Update
            MsgBox "Data source " & dsoDataSrc.Name & " was added to 
              the " & _
                dsoDb.Name & " database."
        End If
    Else
        MsgBox "Database " & strDbName & " not found."
    End If
      
cmdConnect_Click_End:
    Exit Sub

cmdConnect_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdConnect_Click"
    Resume cmdConnect_Click_End
End Sub

A database can contain one or more data sources while a cube can be connected to one (and only one) data source. This procedure uses the ConnectString property to set the database connection parameters:

...
strConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data
  Source=" & _
    strDbName & ";Connect Timeout=15"
...

Next, after we check to see if the database and data source exist, we add the data source to the Database object and then set its ConnectionString property. We update the DataSources collection of the database and inform the user.

...
Set dsoDataSrc = dsoDb.DataSources.AddNew(strDataSrc)
    dsoDataSrc.ConnectionString = strConnectString

    dsoDataSrc.Update
    MsgBox "Data source " & dsoDataSrc.Name & " was added to the " & _
        dsoDb.Name & " database."
...

We also include a procedure, which can be used to remove a data source from a database.

Private Sub cmdDeleteDS_Click()
    ' Purpose: This procedure disconnect a data source from a database.
    '
    Dim strDbName As String
    Dim strDataSrc As String
    Dim Response As Integer
    ' Return value if user selects Yes to delete confirmation.
    Const Yes = 6

    On Error GoTo cmdDeleteDS_Click_Err

    ' Get the name of the data source.
    Me![txtDataSrc].SetFocus
    strDataSrc = Me![txtDataSrc].Text

    strDbName = GetDbName
    ' If the user doesn't enter a database name, exit.
    If strDbName = "" Then
        GoTo cmdDeleteDS_Click_End
    End If
    ' If the database exists, check to see if the data source exists
    ' and then delete it.
    If dsoServer.MDStores.Find(strDbName) Then
        Set dsoDb = dsoServer.MDStores(strDbName)
        If dsoDb.DataSources.Find(strDataSrc) Then
            Response = MsgBox("Remove " & strDataSrc & " from the " &
              strDbName & _
                " database?", vbYesNo)
            If Response = Yes Then
                dsoDb.DataSources.Remove (strDataSrc)
                MsgBox "Data source " & strDataSrc & " was removed from
                  the " & _
                    dsoDb.Name & " database."
            Else
                GoTo cmdDeleteDS_Click_End
            End If
        Else
            MsgBox "Data source " & strDataSrc & " not found."
        End If
    Else
        MsgBox "Database " & strDbName & " not found."
    End If

cmdDeleteDS_Click_End:
    Exit Sub

cmdDeleteDS_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdDeleteDS_Click"

    Resume cmdDeleteDS_Click_End
End Sub

This procedure is similar to the previous one with the exception that after prompting the user, we use the Remove method of the Database object to remove the data source.

The frmBuildCube Form

The second form (frmBuildCube) contains controls and procedures, which demonstrate linking a cube to a data source, and creating and removing dimensions and levels.

The first procedure, Form_Current, is used to populate the Database Name list on the form. It executes from the form's OnCurrent event.

Note   The OnCurrent event occurs when the focus moves to a record, making it the current record, or when the form is refreshed or requeried.
Private Sub Form_Current()
    ' Purpose: This procedure populates the Database Name
    ' combo boxes.
    '
    On Error GoTo Form_Current_Err

    ' Iterate through the MDStores collection on the server to
    ' populate the Database Name combo box control.
    For Each dsoDb In dsoServer.MDStores
        cboDbName.AddItem dsoDb.Name & ";"
    Next dsoDb

Form_Current_End:
    Exit Sub

Form_Current_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "Form_Current"
    Resume Form_Current_End
End Sub

Private Sub cboDbName_AfterUpdate()
    ' Purpose: This procedure populates the Cube Name combo
    ' box control.
    '
    On Error GoTo cboDbName_AfterUpdate_Err

    ' Indicate progress to the user.
    DoCmd.Hourglass True

    strDbName = GetDbName
    Set dsoDb = dsoServer.MDStores(strDbName)

    Me![cboCubeName].RowSource = ""

    ' Iterate through the MDStores collection of the database to
    ' populate the Cube Name combo box control.
    For Each dsoCube In dsoDb.MDStores
        Me![cboCubeName].AddItem dsoCube.Name & ";"
    Next dsoCube

    ' Display the first available item in combo box control.
    Me![cboCubeName].DefaultValue = Me![cboCubeName].ItemData(0)

cboDbName_AfterUpdate_End:
    DoCmd.Hourglass False
    Exit Sub

cboDbName_AfterUpdate_Err:
    Select Case Err.Number
        Case 13
            MsgBox "This database has no cubes defined. Please " & _
                " select another database."
            Resume cboDbName_AfterUpdate_End
        Case Else
            MsgBox "Error " & Err.Number & ": " & _
                Err.Description, , "cboDbName_AfterUpdate"
            Resume cboDbName_AfterUpdate_End
    End Select
End Sub

The cboDbName_AfterUpdate procedure is executed from the AfterUpdate event of the Database Name list and is used to populate the Cube Name list control.

Likewise, we add variable declarations scoped to the form to represent the various objects and separate functions for retrieving the database, cube, dimension, and level names entered by the user.

Dim dsoDb As DSO.Database
Dim dsoCube As DSO.Cube
Dim dsoDS As DSO.DataSource
Dim dsoDim As DSO.Dimension
Dim dsoLevel As DSO.Level

Function GetDbName() As String
    ' Purpose: Returns the database name.
    '
    Dim strDbName As String

    Me![cboDbName].SetFocus
    strDbName = Me![cboDbName].Text
    GetDbName = strDbName
End Function

Function GetCubeName() As String
    ' Purpose: Returns the cube name.
    '
    Dim strCubeName As String

    Me![cboCubeName].SetFocus
    strCubeName = Me![cboCubeName].Text
    GetCubeName = strCubeName
End Function

Function GetDimName() As String
    ' Purpose: Returns the dimension name.
    '
    Dim strDimName As String

    Me![txtDimName].SetFocus
    strDimName = Me![txtDimName].Text

    If strDimName = "" Then
        MsgBox "The Dimension Name control cannot be empty."
        GetDimName = strDimName
    Else
        GetDimName = strDimName
    End If
End Function

Function GetLevelName() As String
    ' Purpose: Returns the level name.
    '
    Dim strLevelName As String

    Me![txtLevelName].SetFocus
    strLevelName = Me![txtLevelName].Text

    If strLevelName = "" Then
        MsgBox "The Level Name control cannot be empty."
        GetLevelName = strLevelName
    Else
        GetLevelName = strLevelName
    End If
End Function

The next procedure is similar to the cmdBasicInformation_Click procedure described in the previous form. It populates a list box control with information about the cube to include the server, database, cube, data source, dimension, and level names.

Private Sub cmdGetCubeInfo_Click()
    ' Purpose: This procedure displays information about the cube
    ' to the list box.
    '
    Dim strDbName As String

    On Error GoTo cmdGetCubeInfo_Click_Err

    ' Indicate progress to the user.
    DoCmd.Hourglass True

    ' Clear the list box and populate it with information
    ' about the Server, Database, Cube, Dimension, and
    ' Level objects.
    With lstCubeInfo
        .RowSource = ""

        .AddItem "Server " & dsoServer.Name
        strDbName = GetDbName
        .AddItem "Database: " & strDbName

        Set dsoDb = dsoServer.MDStores(strDbName)

        For Each dsoDS In dsoDb.DataSources
            .AddItem "Data Source: " & dsoDS.Name
        Next dsoDS
        For Each dsoCube In dsoDb.Cubes
            .AddItem "Cube: " & dsoCube.Name
            For Each dsoDim In dsoDb.Dimensions
                .AddItem "Dimension: " & dsoDim.Name
                For Each dsoLevel In dsoDim.Levels
                    .AddItem "Level: " & dsoLevel.Name
                Next dsoLevel
            Next dsoDim
        Next dsoCube
    End With

cmdGetCubeInfo_Click_End:
    DoCmd.Hourglass False
    Exit Sub

cmdGetCubeInfo_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdGetCubeInfo_Click"
    Resume cmdGetCubeInfo_Click_End
End Sub

Creating a Dimension

The next procedure is used to create a dimension in the user-specified cube and set the FromClause and JoinClause properties of the new dimension.

Private Sub cmdCreateDim_Click()
    ' Purpose: This procedure creates a new dimension and sets
    ' its FromClause and JoinClause properties.
    '
    Dim strServerName As String
    Dim strDbName As String
    Dim strDimName As String
    Dim strFromClause As String
    Dim strJoinClause As String

    On Error GoTo cmdCreateDim_Click_Err

    strDbName = GetDbName
    Set dsoDb = dsoServer.MDStores(strDbName)

    strDimName = GetDimName

    ' If the user doesn't enter a dimension name, exit.
    If strDimName = "" Then
        GoTo cmdCreateDim_Click_End
    End If

    Me![txtFromClause].SetFocus
    strFromClause = Me![txtFromClause].Text
    Me![txtJoinClause].SetFocus
    strJoinClause = Me![txtJoinClause].Text

    ' Check to see if the dimension already exists and if not,
    ' create it and then set the properties.
    If dsoDb.Dimensions.Find(strDimName) Then
        MsgBox "Dimension " & strDimName & " already exists for the " &_
            dsoDb.Name & " database."
    Else
        Set dsoDim = dsoDb.Dimensions.AddNew(strDimName)
        ' The FromClause is the related table where the levels get
        ' their data.
        dsoDim.FromClause = strFromClause
        ' The JoinClause (used in snowflake schemas) joins any related
        ' dimension tables.
        dsoDim.JoinClause = strJoinClause

        dsoDim.Update
        MsgBox "Dimension " & dsoDim.Name & " was added to the " & _
            dsoDb.Name & " database."
    End If

cmdCreateDim_Click_End:
    Exit Sub

cmdCreateDim_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdCreateDim_Click"
    Resume cmdCreateDim_Click_End
End Sub

After getting the database and dimension names and checking to see that the dimension doesn't already exist, we use AddNew method of the Dimensions collection of the Database object to create the new dimension.

...
If dsoDb.Dimensions.Find(strDimName) Then
    MsgBox "Dimension " & strDimName & " already exists for the " & _
        dsoDb.Name & " database."
Else
    Set dsoDim = dsoDb.Dimensions.AddNew(strDimName)
    ' The FromClause is the related table where the levels get
    ' their data.
    dsoDim.FromClause = strFromClause
    ' The JoinClause (used in snowflake schemas) joins any related
    ' dimension tables.
    dsoDim.JoinClause = strJoinClause

    dsoDim.Update
    MsgBox "Dimension " & dsoDim.Name & " was added to the " & _
        dsoDb.Name & " database."
End If
...

The AddNew method, in this case, produces a regular dimension (as opposed to a virtual or parent-child dimension type) with the name specified in the strDimName string. More information about the different types of dimensions can be found at Getting Started with SQL Server Books Online.

Next, we set the FromClause and JoinClause properties of the dimension. The FromClause property specifies the tables or tables (a comma-separated list) that provide the data to the dimension. The JoinClause property is used for a dimension stored in a snowflake schema and specifies how the tables are linked, by equating primary and foreign keys. For more information about OLAP schemas and building cubes, dimensions, and levels, see Building and Processing Cubes.

Creating a Level

The next procedure creates a new level on the dimension and sets several of the level's properties.

Private Sub cmdCreateLevel_Click()
    ' Purpose: This procedure creates a level in the dimension and
    ' sets the MemberKeyColumn, ColumnSize, ColumnType, and
    ' EstimatedSize properties.
    '
    Dim strServerName As String
    Dim strDbName As String
    Dim strDimName As String
    Dim strLevelName As String
    Dim strColDataType As String
    Const intColumnSize = 255
    Const intColDataType = 130
    Const intDistinctMembers = 1

    On Error GoTo cmdCreateLevel_Click_Err

    ' Check to see if the Dimension object exists and if so,
    ' the check to see if the Level object exists. If not,
    ' create the Level object and set some of its properties.
    strDbName = GetDbName
    Set dsoDb = dsoServer.MDStores(strDbName)

    strDimName = GetDimName
    ' If the user doesn't enter a dimension name, exit.
    If strDimName = "" Then
        GoTo cmdCreateLevel_Click_End
    End If

    ' If the Dimension exists, create a reference to it.
    If dsoDb.Dimensions.Find(strDimName) Then
        Set dsoDim = dsoDb.Dimensions.Item(strDimName)
    Else
        MsgBox "Dimension " & strDimName & " not found."
    End If

    strLevelName = GetLevelName
    ' If the user doesn't enter a level name, exit.
    If strLevelName = "" Then
        GoTo cmdCreateLevel_Click_End
    End If

    ' Check to see if the level already exists and if not,
    ' create it and then set the properties.
    If dsoDim.Levels.Find(strLevelName) Then
        MsgBox "Level " & strLevelName & " already exists for the " & _
            dsoDim.Name & " dimension."
    Else
        Set dsoLevel = dsoDim.Levels.AddNew(strLevelName)
        With dsoLevel
            .MemberKeyColumn = "" & strDimName & "." & strLevelName &
              ""
            .ColumnSize = intColumnSize
            .ColumnType = intColDataType
            .EstimatedSize = intDistinctMembers

            dsoDim.Update
            MsgBox "Level " & .Name & " was added to the " & _
                dsoDim.Name & " dimension."
        End With
    End If

cmdCreateLevel_Click_End:
    Exit Sub

cmdCreateLevel_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdCreateLevel_Click"
    Resume cmdCreateLevel_Click_End
End Sub

The MemberKeyColumn property contains the column from which the member's key value (primary key) is taken from the source database. The ColumnSize property indicates the size of the column in bytes, and the ColumnType property, its type (an enumerated type derived from ADODB.DataTypeEnum).

Note   If the cube or dimension aggregations are going to be stored in a relational database (in other words, the dimension or cube is a relational OLAP (ROLAP) store) these two columns are used by Analysis Services to create a table structure that will hold the aggregations on the remote database.

The EstimatedSize property provides a rough estimation of the number of members within the current level. This property is used by the Partition Analyzer in Analysis Services when aggregations are designed.

These properties are representative of just some of the various properties that can be set for levels.

Removing a Dimension and Level

The next two procedures are used to remove a dimension and level, respectively.

Private Sub cmdRemoveDim_Click()
    ' Purpose: This procedure removes a Dimension object from
    ' the database.
    '
    Dim strServerName As String
    Dim strDbName As String
    Dim strDimName As String
    Dim Response As Integer
    ' Return value if user selects Yes to delete confirmation.
    Const Yes = 6

    On Error GoTo cmdRemoveDim_Click_Err

    strDbName = GetDbName
    Set dsoDb = dsoServer.MDStores(strDbName)

    strDimName = GetDimName
    ' If the user doesn't enter a dimension name, exit.
    If strDimName = "" Then
        GoTo cmdRemoveDim_Click_End
    End If

    ' If the dimension exists, prompt the user and then delete it.
    If dsoDb.Dimensions.Find(strDimName) Then
        Response = MsgBox("Remove " & strDimName & " from the " &
          strDbName & _
            " database?", vbYesNo)
        If Response = Yes Then
            dsoDb.Dimensions.Remove (strDimName)

            MsgBox "Dimension " & strDimName & " was removed from " & _
                dsoDb.Name & " database."
        Else
            GoTo cmdRemoveDim_Click_End
        End If
    Else
        MsgBox "Dimension " & strDimName & " not found."
    End If

cmdRemoveDim_Click_End:
    Exit Sub

cmdRemoveDim_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdRemoveDim_Click"
    Resume cmdRemoveDim_Click_End
End Sub

Private Sub cmdRemoveLevel_Click()
    ' Purpose: This procedure removes a level from the dimension.
    '
    Dim strServerName As String
    Dim strDbName As String
    Dim strDimName As String
    Dim strLevelName As String
    Dim Response As Integer
    ' Return value if user selects Yes to delete confirmation.
    Const Yes = 6

    On Error GoTo cmdRemoveLevel_Click_Err

    strDbName = GetDbName
    Set dsoDb = dsoServer.MDStores(strDbName)

    strDimName = GetDimName

    ' Check to see if the dimension exists.
    If dsoDb.Dimensions.Find(strDimName) Then
        Set dsoDim = dsoDb.Dimensions.Item(strDimName)
    Else
        MsgBox "Dimension " & strDimName & " not found."
        GoTo cmdRemoveLevel_Click_End
    End If

    strLevelName = GetLevelName

    ' If the level exists, prompt the user and then delete it.
    With dsoDim
        If .Levels.Find(strLevelName) Then
            Response = MsgBox("Remove " & strLevelName & " from the " &
              .Name & _
                " dimension?", vbYesNo)
            If Response = Yes Then
                .Levels.Remove (strLevelName)

                .Update
                MsgBox "Level " & strLevelName & " was removed from the
                  " & _
                    .Name & " dimension."
            Else
                GoTo cmdRemoveLevel_Click_End
            End If
        Else
            MsgBox "Level " & strLevelName & " not found."
        End If
    End With

cmdRemoveLevel_Click_End:
    Exit Sub

cmdRemoveLevel_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdRemoveLevel_Click"
     Resume cmdRemoveLevel_Click_End
End Sub

The procedures are similar in that they both use the Remove method of their respective parent objects.

That concludes our exploration of some of the objects, methods, properties, collections, and interfaces you can program with DSO. Since DSO can be used to do most things that you can do in the Analysis Manager, creating a few databases, cubes, dimensions, and levels in the Analysis Manager is also a good way to better understand the relationship between these objects. I would also encourage you to explore using ADOMD and MDX to query against Analysis Services and work with cellsets and multidimensional data.

Conclusion

In this article, we explored ways of creating and manipulating the various OLAP related objects available on an Analysis Services server. We looked at creating and removing databases, cubes, dimensions and levels. We also looked at some of the interfaces and objects available in the DSO object model. Exploring the details of using DSO with Analysis Services will increase and improve your understanding of how these objects work and how they are interrelated to form an OLAP solution.

Show: