Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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 ClassTypeclsCube contains a SubClassTypesbclsVirtual 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:
- Declare the cube as type MDStore.
- Instantiate the Cube object by using the New keyword.
- Add the object to the MDStores collection.
- 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.