In this lesson, you will be introduced to the object model for OLAP programming. Just as OLE DB for OLAP is an extension to OLE DB that provides multidimensional capabilities to OLE DB, ActiveX Data Objects (multidimensional), or ADO MD, is an extension to ADO. This lesson covers the objects that you will use to connect to an OLAP Server and retrieve metadata about a cube on the server. You will learn how to write code for performing these operations.
After this lesson, you will be able to:
- Analyze existing cubes by connecting to multidimensional data sources and accessing cube definitions
Estimated lesson time: 60 minutes
Microsoft has created OLE DB for OLAP and ADO MD to be the standard interfaces to multidimensional data. These interfaces extend the OLE DB and ADO interfaces that are widely used to access other kinds of data.
ADO MD enables your client applications to access PivotTable Service through OLE DB. ADO MD supports key features for building client/server and Web-based applications.
ADO MD has the following characteristics:
NOTE
SQL Server Books Online does not include documentation of the ADO MD object model. The ADO MD object model is documented in the ADO MD Programmer s Reference, which is included in the ADO version 2.x documentation. This documentation should be located in C:\Program Files\Common Files\System\ado\ado2xx.CHM on computers that have Microsoft Data Access Components installed.
OLE DB for OLAP extends OLE DB to include objects specific to multidimensional data. Systems programmers must write C or C++ code that directly uses the set of COM interfaces in OLE DB for OLAP. Vendors can use these standard interfaces to create data providers or services that work with all other components and tools that use the OLE DB for OLAP standard.
NOTE
SQL Server Books Online does not include documentation for OLE DB for OLAP programming. OLE DB for OLAP programming is documented in the Microsoft OLE DB documentation in Data Access Services in the Platform SDK in the Microsoft Developer Network Library. You can find a copy of this documentation on the Companion CD-ROM.
This section discusses how to programmatically analyze existing cubes using ADO MD objects. These objects allow you to connect to multidimensional data sources and retrieve cube data and metadata.
The ADO MD object model is organized hierarchically to allow access to both cube definitions and data. This object model is composed of the major organizing components shown in Figure 10.12.
Figure 10.12 The high-level objects of the ADO MD object model
Catalog Object
ADO MD uses the Catalog object to establish a connection to the multidimensional data store. The Catalog object represents the connection to an OLAP database on an OLAP Server, and it contains the CubeDefs collection, which represents all the cubes in a database.
CubeDef Object
The CubeDef object represents a particular cube. A CubeDef object contains all the metadata about a cube, including its dimensions, name, and description. The CubeDef object also contains the Cellset object.
Cellset Object
The Cellset object represents a multidimensional result set from a multidimensional query. Values in the cellset are retrieved from individual cells in the cellset. A cellset is an array-like structure that makes it possible to directly access any cell in the multidimensional result set. You will learn how to use the Cellset object in Chapter 12, "MDX Statements and ADO MD Objects."
You connect to a multidimensional data source using the Catalog object. The Catalog object uses an OLE DB connection to connect to the multidimensional data source. You use the Catalog object ActiveConnection property to reference an existing Connection object or to define a connection string that OLE DB uses to create a connection to the data source.
The Catalog object also has a Name property and a CubeDefs collection. The CubeDefs collection contains one CubeDef object for each cube in the connected data source.
Defining the Connection String
To define the connection string, you use the parameters in the following table:
Parameter | Description |
---|---|
Provider | Indicates the OLE DB data provider; for OLAP Services databases, the provider is always MSOLAP |
Data source | The name of the server running OLAP Services |
Initial catalog | The name of the OLAP database to use |
Location | The name of a local cube file (.cub) created by the PivotTable Service |
To connect to a database on an OLAP server, specify the Provider, Data Source, and Initial Catalog paramenters. To connect to a local cube file, specify the Provider and Location parameters.
Example
This example shows how to connect to an OLAP database named Foodmart on an OLAP Server named OLAPSERVER by using a Catalog object and a script written in the Visual Basic Scripting Edition (VBScript) language.
connstr = "Provider=MSOLAP;Data Source=OLAPSERVER;" & _ "Initial Catalog=Foodmart" Set Cat = CreateObject("ADOMD.Catalog") Cat.ActiveConnection = connstr |
After you have connected to a multidimensional data source, you use the CubeDef object to access cube definition properties.
Using CubeDef Object Properties
A CubeDef object contains all the information about a cube stored in its properties. The name and description of the CubeDef object are stored in the Name and Description properties. All other properties of a CubeDef object are dynamic properties.
The CubeDef.Properties collection is a dynamic collection that is defined by the provider; it contains information about the cube. You can access the value of a dynamic property only through the CubeDef.Properties collection. The properties that are included are determined by the writer of the provider, but the properties in the following table should always be available:
Properties | Description |
---|---|
CATALOG_NAME | The name of the catalog to which this cube belongs |
SCHEMA_NAME | The name of the schema to which this cube belongs |
CUBE_NAME | The name of the cube |
CUBE_TYPE | The type of the cube |
CUBE_GUID | Cube globally unique identifier (GUID) |
CREATED_ON | Date and time of cube creation |
LAST_SCHEMA_UPDATE | Date and time of last schema update |
SCHEMA_UPDATED_BY | User ID of the person doing the last schema update |
LAST_DATA_UPDATE | Date and time of last data update |
DATA_UPDATED_BY | User ID of the person doing the last data update |
DESCRIPTION | A meaningful description of the cube |
Example
This example shows how to use Visual Basic code to connect to a catalog named Foodmart on an OLAP Server named OLAPSERVER and list the name and properties of each cube contained in the catalog. If you want to run this example, you can find the code for the example in C:\SQLDW\Exercise\ Ch10\Examples.TXT.
Dim Cat As ADOMD.Catalog Dim MyCube As ADOMD.CubeDef Dim Prop As Object Set Cat = CreateObject("ADOMD.Catalog") Cat.ActiveConnection = "Provider=MSOLAP;Data Source=OLAPSERVER;" & _ "Initial Catalog=Foodmart;" Cat.CubeDefs.Refresh For Each MyCube In Cat.CubeDefs Debug.Print MyCube.Name For Each Prop In MyCube.Properties Debug.Print Prop.Name, Prop.Value Next Next |
To find out how a cube is constructed, you can use the ADO MD objects that are contained in a CubeDef object, which are shown in Figure 10.13.
Figure 10.13 The cube definition objects in the ADO MD object model
Dimensions Object
This object represents a dimension used to construct the cube. The Dimensions collection of the CubeDef object contains a list of all the dimensions in the cube. You can use the Properties collection to find out information about the dimension.
Hierarchy Object
The Dimension object has a Hierarchies collection that contains a list of all the hierarchies defined on that dimension for the cube. Each hierarchy is represented by a Hierarchy object in the Hierarchies collection. The Hierarchy object has properties that you can use to work with a hierarchy and the levels that it contains.
Level Object
Each Hierarchy object contains a Levels collection, which is a list of Level objects. In addition to the Name property, the Level object has a Depth property that indicates its position in the hierarchy. The Members collection contains a list of the members that comprise the current level.
NOTE
You cannot use these objects to create or modify cubes.
Example
This example reads through the metadata for the Sales cube in the Foodmart database. Because the Dimensions, Hierarchies, and Levels collections and objects are defined in a hierarchy, this example uses three sets of nested loops to read through the cube definition and print the information. If you want to run this example, you can find the code for the example in C:\SQLDW\Exercise\Ch10\Examples.TXT.
Dim Cat As ADOMD.Catalog Dim Cub As ADOMD.CubeDef Dim Dimn As ADOMD.Dimension Dim Hier As ADOMD.Hierarchy Dim Lvl As ADOMD.Level Dim Row As Integer Dim Col As Integer '************************************************** ' Connect to the database. '************************************************** Set Cat = CreateObject("ADOMD.Catalog") Cat.ActiveConnection = "Provider=MSOLAP;Data Source=OLAPSERVER;" & _ "Initial Catalog=Foodmart" '**************************************************** 'Read the definition for the Sales Cube. '**************************************************** Set Cub = Cat.CubeDefs("Sales") Debug.Print "Cube Name: " & Cub.Name Debug.Print "List of Dimensions" '**************************************** ' Read Each Dimension in the Cube Object '**************************************** For Each Dimn In Cub.Dimensions Debug.Print "Dimension Name: " & Dimn.Name Debug.Print "Description: " & Dimn.Description Debug.Print "List of Hierarchies" '********************************************* ' Read Each Hierarchy in the Dimension Object '********************************************* For Each Hier In Dimn.Hierarchies Debug.Print "Hierarchy Name: " & Hier.Name Debug.Print "Description: " & Hier.Description Debug.Print "List of Levels" '**************************************** ' Read Each Level in the Hierarchy Object '**************************************** For Each Lvl In Hier.Levels Debug.Print "Level Name: " & Lvl.Name Debug.Print "Caption: " & Lvl.Caption Debug.Print "Depth: " & Lvl.Depth Next '***Level***' Next '***Hierarchy***' Next '***Dimension***' |
In this exercise and the following exercise, you will use ADO MD and Visual Basic for Applications (VBA) in Excel 2000 to connect to the Northwind_DSS OLAP database and read the cube definition of the Sales cube. Following these exercises are two exercises in which you perform the same operations by using Visual Basic Scripting Edition (VBScript) running in Active Server Pages on Internet Information Server. You can do both sets of exercises or choose only one of the two. The C:\SQLDW\Exercise\Ch10\ADO10_Ans.XLS file is a completed answer file for this exercise.
Now, you will declare variables and objects used to connect to the OLAP Server.
Dim cat As ADOMD.Catalog Const connstr = "Provider=MSOLAP;" & _ "Data Source=LOCALHOST;Initial Catalog=Northwind_DSS" |
This code contains error checking to determine whether the connection was made. It uses the connection string that you previously defined in the (General) section of the module. The complete Connect() procedure is as follows:
Public Sub Connect() Set Cat = CreateObject("ADOMD.Catalog") Cat.ActiveConnection = connstr If Not Cat Is Nothing Then Msgbox "Connection Made." Else Msgbox "Connection Error." End If End Sub |
Position your cursor inside the Connect Sub procedure and click Run on the toolbar to run the code. If you have written the code correctly, you will see a message box that says Connection Made.
In this exercise, you will use ADO MD to read the cube definition for the Sales cube stored in the OLAP database and display the results in an Excel spreadsheet. The C:\SQLDW\Exercise\Ch10\ADO10_Ans.XLS file is a completed answer file for this exercise.
Now, you will declare ADO MD objects to hold metadata for the Sales cube.
In Code Module 1, locate the comment labeled ***Second Edit***. In the space provided, type the following code to declare variables for the CubeDef, Dimension, Hierarchy, and Level objects:
Dim Cub As ADOMD.CubeDef Dim Dimn As ADOMD.Dimension Dim Hier As ADOMD.Hierarchy Dim Lvl As ADOMD.Level |
Locate the comment labeled ***Third Edit*** in Module 1. In the space provided, type the following code to call the procedure that connects to the Northwind_DSS database:
Connect |
Now, you will read the properties of all CubeDefs, Dimensions, Hierarchies, and Levels collections in the Sales cube and place the results in an Excel spreadsheet. Because these objects represent a hierarchy, you will use a set of three nested loops to iterate through this information.
Set Cub = cat.CubeDefs("Sales") row = row + 1 Cells(row, col) = "Cube Name: " & Cub.Name row = row + 1 Cells(row, col) = "Description: " & Cub.Description row = row + 1 Cells(row, col) = "Dimensions" |
'**************************************** ' Read Each Dimension in the Cube Object '**************************************** For Each Dimn In Cub.Dimensions row = row + 1 Cells(row, col + 1) = "Dimension Name: " & Dimn.Name row = row + 1 Cells(row, col + 1) = "Description: " & Dimn.Description row = row + 1 Cells(row, col + 1) = "Unique Name: " & Dimn.UniqueName row = row + 1 Cells(row, col + 1) = "Hierarchies" Next Dimn |
'********************************************* ' Read Each Hierarchy in the Dimension Object '********************************************* For Each Hier In dimn.Hierarchies row = row + 1 Cells(row, col + 2) = "Hierarchy Name: " & Hier.Name row = row + 1 Cells(row, col + 2) = "Description: " & Hier.Description row = row + 1 Cells(row, col + 2) = "Unique Name: " & Hier.UniqueName row = row + 1 Cells(row, col + 2) = "Levels" Next Hier |
'**************************************** ' Read Each Level in the Hierarchy Object '**************************************** For Each Lvl In Hier.Levels Cells(row, col + 3) = "Level Name: " & Lvl.Name row = row + 1 Cells(row, col + 3) = "Description: " & Lvl.Description row = row + 1 Cells(row, col + 3) = "Unique Name: " & Lvl.UniqueName row = row + 1 Cells(row, col + 3) = "Caption: " & Lvl.Caption row = row + 1 Cells(row, col + 3) = "Depth: " & Lvl.Depth Next Lvl |
Locate the comment labeled ***Fifth Edit*** in Module 1. In the space provided, type the following code to clear the objects from memory when they are no longer in use:
Set Lvl = Nothing Set Hier = Nothing Set Dimn = Nothing Set Cub = Nothing Set Cat = Nothing |
In this exercise and in the following exercise, you will use ADO MD and Visual Basic Scripting Edition (VBScript) in a Web page, to connect to the Northwind_DSS OLAP database and read the cube definition of the Sales cube. In the previous two exercises, you performed the same operations using Visual Basic for Applications (VBA) running in Excel 2000. You can do both sets of exercises or choose only one of the two. The C:\SQLDW\Exercise\Ch10\CubeDef_Ans.ASP file is a completed answer file for this exercise.
Now, you will declare variables and objects used to connect to the OLAP Server.
Dim Cat Const connstr = _ "Provider=MSOLAP;Data Source=LOCALHOST;Initial Catalog=Northwind_DSS" |
This code uses the connection string defined earlier to make a connection to the OLAP Server.
Public Sub Connect() Set Cat = Server.CreateObject("ADOMD.Catalog") Cat.ActiveConnection = connstr End Sub |
In this exercise, you will use ADO MD to read the cube definition for the Sales cube stored in the OLAP database and display the results in a Web page. The C:\SQLDW\Exercise\Ch10\CubeDef_Ans.ASP file is a completed answer file for this exercise.
Now, you will declare ADO MD objects to hold metadata for the Sales cube.
Locate the comment labeled ***Third Edit***. In the space provided, type the following code to declare variables for the CubeDef, Dimension, Hierarchy, and Level objects:
Dim Cub Dim Dimn Dim Hier Dim Lvl |
Locate the comment labeled ***Fourth Edit***. In the space provided, type the following code to call the procedure that connects to the Northwind_DSS OLAP database.
Connect |
Now, you will read the properties of all CubeDefs, Dimensions, Hierarchies, and Levels collections in the Sales cube and display the results in a Web page. Because these objects represent a hierarchy, you will use a set of three nested loops to iterate through this information.
Set cub = cat.CubeDefs("Sales") Response.Write "<h3>Cube Name: " & Cub.Name & "</h3>" Response.Write "Description: <b>" & Cub.Description & "</b><br><hr>" |
Locate the comment labeled ***Sixth Edit***. In the space provided, type the following code to clear the objects from memory when they are no longer in use:
Set Lvl = Nothing Set Hier = Nothing Set dimn = Nothing Set cub = Nothing Set cat = Nothing |
Users can analyze data in a cube, even when they are not connected to an OLAP Server. PivotTable Service can create a local cube file from any tabular data provider. Tabular providers include relational databases for which you have an OLE DB provider or an ODBC driver. OLAP Services is a tabular and multidimensional provider. PivotTable Service cannot create cubes on an OLAP Server.
The PivotTable Service enables client applications to persist data cubes in local files. The default extension for these files is .CUB. Local data cubes are stored in structured, compound document files that are the local equivalent of OLAP Services objects. More than one local data cube can be stored in a .CUB file.
In Chapter 12, "MDX Statements and ADO MD Objects," you will learn how to create a local cube using the MDX CREATE CUBE statement and the ADO MD objects.
The ADO MD object model gives programmers access to OLAP cubes on an OLAP Server and in a local cube file. You use familiar OLE DB connection strings to connect to an OLAP Server. The OLAP database is represented by the Catalog object, and cubes are represented by CubeDef objects in the CubeDefs collection in a Catalog.