Reading SAS OLAP Cubes

Goal

You want your application to read SAS OLAP cubes.

This recipe applies to the OLAP provider. This recipe applies only to ADO MD, which is an extension to ADO that enables you to read multidimensional schema, query cubes, and retrieve the results. Sample code is included.

Note: This recipe requires that you reference these type libraries in your Visual Basic project: the Microsoft ActiveX Data Objects Library and the Microsoft ActiveX Data Objects (Multidimensional) Library.

Implementation

Opening an ADO MD Cellset Object

The following Visual Basic code shows you how to open an ADO MD Cellset object. You first specify an MDX query and then execute it on a SAS OLAP Server.

' obConnection is an open Connection object.
Dim obCellset As ADOMD.Cellset

Set obCellset.ActiveConnection = obConnection

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

Displaying the Results of the MDX Query

After the Cellset object is open, you can display the results of the MDX query. The following Visual Basic code shows how this task is done. The 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