Lesson 4: Introduction to Programming with ADO MD Objects

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

Client Interfaces for OLAP

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.

Using ADO MD

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:

  • Extends ADO to include data access interfaces optimized for multidimensional data applications. It is part of ADO version 2.0 and later.
  • Is object-based, high-level, and language-independent.
  • Allows Visual Basic and other COM-automation programming languages to use the ADO MD objects.

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.

Using OLE DB for OLAP

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.

Analyzing Existing Cubes

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.

Navigating the ADO MD Object Model

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.

click to view at full size

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."

Connecting to a Multidimensional Data Source

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

Accessing Cube Definition Properties

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

Accessing Cube Definition Objects

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.

click to view at full size

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***'

Exercise 1: Connecting to an OLAP Database Using VBA in Excel 2000.

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.

  • To use the ADO MD object library
    1. Open the C:\SQLDW\Exercise\Ch10\ADO10_Start.XLS Excel file. You may be prompted to enable or disable macros. You need to enable macros because you will be writing VBA code in the Excel workbook.
    2. On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor.
    3. On the Tools menu in Microsoft Visual Basic, click References.
    4. In the References list, check the Microsoft ActiveX Data Objects (multi-dimensional) 1.0 Library check box.
    5. Click OK.

  • To add a code module and declare objects and variables
  • Now, you will declare variables and objects used to connect to the OLAP Server.

    1. In the Project Explorer tree in the Visual Basic Editor, expand the Modules folder, and then double-click Module 1.
    2. On the Edit menu, click Find.
    3. Locate the comment labeled ***First Edit***. In the space provided, type the following code to declare the variables and objects that you will use to make the connection:
    4. Dim cat As ADOMD.Catalog Const connstr = "Provider=MSOLAP;" & _ "Data Source=LOCALHOST;Initial Catalog=Northwind_DSS"

  • To create a connection
    1. On the Insert menu, click Procedure. Create a Public Sub procedure named Connect. Click OK when you have filled in the fields in the Add Procedure dialog box.
    2. Add code to the Connect() procedure as shown below.
    3. 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

    4. Save the workbook.

  • To test the Connect procedure
  • 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.

    Exercise 2: Reading a Cube Definition with ADO MD Using VBA in Excel 2000.

    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.

  • To declare cube objects and variables
  • 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

  • To Connect to the OLAP Server
  • 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

  • To read a cube definition
  • 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.

    1. Locate the comment labeled ***Fourth Edit*** in Module 1. In the space provided, type the following code to access the cube definition for the Sales cube. This code also retrieves the Name and Description properties of the Sales cube.
    2. 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"

    3. Locate the comment labeled ***First Review*** in Module 1. Examine the code. The code reads the Name, Description, and UniqueName properties of each dimension in the Dimensions collection.
    4. '**************************************** ' 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

    5. Locate the comment labeled ***Second Review*** in Module 1. Examine the code. This code reads the Name, Description, and UniqueName properties of each hierarchy in the Hierarchies collection.
    6. '********************************************* ' 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

    7. Locate the comment labeled ***Third Review*** in Module 1. Examine the code. You will read the Name, Description, and UniqueName properties of each level in the Levels collection.
    8. '**************************************** ' 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

  • To de-allocate object variables
  • 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

  • To review and execute the procedure
    1. Review the completed procedure and save the workbook.
    2. Position your cursor inside the ReadCubeDef Sub procedure and run the code.
    3. Switch to the Excel spreadsheet to see the results.

    Exercise 3: Connecting to an OLAP Database Using VBScript in a Web Page

    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.

  • To add a code module and declare objects and variables
  • Now, you will declare variables and objects used to connect to the OLAP Server.

    1. Use Notepad to open the C:\SQLDW\Exercise\Ch10\CubeDef.ASP file. This file contains a partially complete Active Server Page.
    2. Locate the comment labeled ***First Edit***. In the space provided, type the following code to declare the variables and objects that you will use to make the connection:
    3. Dim Cat Const connstr = _ "Provider=MSOLAP;Data Source=LOCALHOST;Initial     Catalog=Northwind_DSS"

  • To create a connection
    1. Locate the comment labeled ***Second Edit***.
    2. Create a public Sub procedure called Connect.
    3. Add the following code to the Connect() procedure.
    4. 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

    5. Save the file but keep Notepad open.

    Exercise 4: Reading a Cube Definition with ADO MD Using VBScript in a Web Page

    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.

  • To declare cube objects and variables
  • 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

  • To Connect to the OLAP Server
  • 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

  • To read a cube definition
  • 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.

    1. Locate the comment labeled ***Fifth Edit***. In the space provided, type the following code to access the cube definition for the Sales cube. This code also retrieves the Name and Description properties of the Sales cube.
    2. Set cub = cat.CubeDefs("Sales") Response.Write "<h3>Cube Name: " & Cub.Name & "</h3>" Response.Write "Description: <b>" & Cub.Description & "</b><br><hr>"

    3. Locate the comment labeled ***First View*** and examine the code. This code reads the Name, Description, and UniqueName properties of each dimension in the Dimensions collection.
    4. Locate the comment labeled ***Second View*** and examine the code. This code reads the Name, Description, and UniqueName properties of each hierarchy in the Hierarchies collection.
    5. Locate the comment labeled ***Third View*** and examine the code. This code reads the Name, Description, UniqueName, Caption, and Depth properties of each level in the Levels collection.

  • To de-allocate object variables
  • 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

  • To review and execute the procedure
    1. Review the completed procedure and save the file.
    2. Right-click the C:\SQLDW\Exercise\Ch10 folder in Windows NT Explorer and select Sharing on the pop-up menu.
    3. Select the Web Sharing tab, and then select Share this folder. The Edit Alias dialog box will be displayed. If the Edit Alias dialog box is not displayed, click the Add button to open it.
    4. In the Edit Alias dialog box, set the Alias to ADOMD10 and ensure that the Read and Scripts check boxes are checked. Click the OK button twice to close the Edit Alias and the Ch10 properties dialog boxes.
    5. To view the Web page, open Microsoft Internet Explorer and navigate to http://localhost/ADOMD10/CubeDef.ASP.

    Local Cubes

    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.

    Creating Local Cube Files

    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.

    Lesson Summary

    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.



    Microsoft Corporation - Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
    Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
    ISBN: 0735606706
    EAN: 2147483647
    Year: 1999
    Pages: 114

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net