Reading Multidimensional Data with ADO MD

In this recipe, you learn how to use the SAS OLAP Data Provider to read multidimensional data from a SAS OLAP server.

Applies to:	SAS OLAP Data Provider
Implement using:	ADO MD

Overview

ADO MD is an extension to the ADO programming interface that enables users to read multidimensional schema, query cubes, and retrieve the results. ADO MD accesses data through a multidimensional data provider such as the SAS OLAP Data Provider. This recipe provides sample code for the following steps:

Opening an ADO Connection object to establish a connection to the SAS OLAP Server
Opening an ADO MD Cellset object and executing a multidimensional expression (MDX) query on the server.
Reading the results of the MDX query from the server.

Details

1. Establishing the Connection

Before you can write code for the connection, you must add the following library references to your Visual Basic project (see Minimum System Requirements for the SAS Data Providers for supported versions):

Microsoft ActiveX Data Objects Library
Microsoft ActiveX Data Objects (Multidimensional) Library

The following code fragment shows how to open a connection to the SAS OLAP Server.

Note: See Connecting to a Remote SAS OLAP Server for more information about making connections. For more information about how to open a Connection object, see Opening an ADO Connection Object.

' Constants from OLEDB.h
const DBPROPVAL_DST_TDP = 1
const DBPROPVAL_DST_MDP = 2

Dim obConnection As ADODB.Connection
Dim obCellset As ADOMD.Cellset

Set obConnection = New ADODB.Connection
Set obCellset = New ADOMD.Cellset

obConnection.Provider = "sas.OLAPProvider.9.1"
' Connect to a local SAS OLAP Server
obConnection.Properties("Data Source") = "_LOCAL_"
obConnection.Open

Set obCellset.ActiveConnection = obConnection

2. Specifying the MDX Query

The following code shows how to specify an MDX query and execute it on the SAS OLAP Server by opening an ADO MD Cellset object.

obCellset.Source = "SELECT" & _
                   "  {[dealers].[dealer].members} ON COLUMNS," & _
                   "  {[cars].members} ON ROWS" & _
                   "  FROM MDDBCARS" & _
                   "  WHERE ([measures].[SALES_SUM])"
obCellset.Open

3. Reading Multidimensional Data

After the Cellset object is open, you can display the results of the MDX query using code similar to the following. This sample code prints the data to the Visual Basic Immediate window by calling Debug.Print.

' Print the cellset axis structure and count the number of cells
Dim cCells As Long
Dim obAxis As ADOMD.Axis
Dim obPosition As ADOMD.Position
Dim obMember As ADOMD.Member

cCells = 1
For Each obAxis In obCellset.Axes
    Debug.Print obAxis.Name & ", cells on axis: " & obAxis.Positions.Count
    cCells = cCells * obAxis.Positions.Count
    For Each obPosition In obAxis.Positions
        For Each obMember In obPosition.Members
            Debug.Print vbTab & obMember.Name & ": " & obMember.Caption
        Next
    Next
Next

' Print the slicer axis structure
Debug.Print vbNewLine & "Filter/Slicer Axis:"
For Each obPosition In obCellset.FilterAxis.Positions
    For Each obMember In obPosition.Members
        Debug.Print vbTab & obMember.Name & ": " & obMember.Caption
    Next
Next
Debug.Print ""

If cCells = 1 Then
    Debug.Print "1 cell returned." & vbNewLine
Else
    Debug.Print cCells & " cells returned." & vbNewLine
End If

' Print the cell values by ordinal
Dim i As Long, j As Long
Dim obCell As ADOMD.Cell
Dim strPositions As String

For i = 0 To cCells - 1
    Set obCell = obCellset(i)
    strPositions = "Cell " & i & " "

    ' Print the position array for this cell
    ' (Example: Cell 0 would be Cell(0,0,0) in a 3-d array)
    strPositions = strPositions & "("
    For j = 0 To obCell.Positions.Count - 1
        If j > 0 Then strPositions = strPositions & ", "
        strPositions = strPositions & obCell.Positions(j).Ordinal
    Next
    strPositions = strPositions & "): "

    ' Retrieve the cell value and check for NULL
    If IsNull(obCell.value) Then
        strPositions = strPositions & "-null-"
    Else
        strPositions = strPositions & obCell.value
    End If
    Debug.Print strPositions
Next

' Clean up and exit
obCellset.Close
Set obCellset = Nothing
Set obCell = Nothing
Set obAxis = Nothing
Set obPosition = Nothing
Set obMember = Nothing