This documentation is archived and is not being maintained.

MdhInterfacesLib Library VBA Code Sample Reference for Microsoft Data Analyzer

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.
 

Paul Cornell
Microsoft Corporation

April 2002

Applies to:
    Microsoft® Data Analyzer

Summary: Provides VBA code samples for many of the objects and members in the Microsoft Data Analyzer MdhInterfacesLib library. (13 printed pages)

Contents

Introduction
Object Model Map
Code Samples

Introduction

Microsoft® Data Analyzer ships with a compiled HTML Help (CHM) file titled Microsoft Data Analyzer API Help with the default file path of C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5\MSDA35om.chm. This CHM file contains a few snippets of Microsoft Visual Basic® code and Microsoft Visual Basic Scripting Edition code; individual API topics contain C++ code stubs but no Visual Basic or VBScript code samples. This article contains Visual Basic for Applications (VBA) code samples for some of the API topics.

For complete descriptions of each of the API's objects, collections, members, and enumerations, consult the Microsoft Data Analyzer API Help file (MSDA35om.chm) included with Data Analyzer.

For purposes of these code samples, this reference assumes an instance of the Microsoft Data Analyzer ActiveX® control has been embedded on a VBA UserForm and references have been set to the:

  • Max3 ActiveX 3.0 Type Library (using the default path C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5\Max3ActiveX.dll)
  • Max3API library (using the default path C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5\Max3API.dll)
  • MdhInterfacesLib library (using the default path C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5\MdhInterfacesLib.tlb)

Only code for the MdhInterfacesLib library (MdhInterfacesLib.tlb) will be presented in this reference.

The MDHHeirarchy property in the Max3API library returns an IMdhHeirarchy object in the MdhInterfacesLib library. The MDHMember property in the Max3API library returns an IMdhMember object in the MdhInterfacesLib library. The MetaData property in the Max3API library returns an IMdhManager object in the MdhInterfacesLib library. Because none of these three properties are contained in the MdhInterfacesLib library, they are not included in this reference. For more information on these properties, see the Microsoft Data Analyzer API help file (MSDA35om.chm). For example, to gain entry into the MdhInterfacesLib library from the Max3API library using VBA, you can use code similar to the following:

Private Sub Max3Ax1_Initialized()

    Dim daApp As Max3API.Application
    Dim objCMdhInterfaces As MdhInterfacesLib.CMdhInterfaces
    Dim intCube As Integer
    Dim strResults As String

    Set daApp = Max3Ax1.Application

    daApp.ActiveView.Connect _
        "location=MyOLAPServerName;provider=msolap"

    Set objCMdhInterfaces = daApp.ActiveView.MetaData

    strResults = "Cubes:" & vbCrLf

    For intCube = 0 To objCMdhInterfaces.Cubes.Count - 1
        strResults = strResults & _
            objCMdhInterfaces.Cubes.Item(intCube).Name & vbCrLf
    Next intCube

    MsgBox strResults

End Sub

Object Model Map

The following figure provides a graphical representation of the relationships among the objects in the MdhInterfacesLib library.

Click here to see larger image

Figure 1. MdhInterfacesLib library object model map (click picture to see larger image)

Code Samples

CMdhInterfaces Object: ActiveCatalog Property, Catalogs Property, ConnectionDescription Property
IMdhCatalog Object: Name Property
IMdhCatalogs Object: Count Property, Item Property

The following code sample provides high-level information about the catalogs on a particular OLAP server.

Private Sub Max3Ax1_Initialized()

    Dim daApp As Max3API.Application
    Dim objCMdhInterfaces As MdhInterfacesLib.CMdhInterfaces
    Dim intCatalog As Integer
    Dim strResults As String

    Set daApp = Max3Ax1.Application

    daApp.ActiveView.Connect _
        "location= MyOLAPServerName;provider=msolap"

    Set objCMdhInterfaces = daApp.ActiveView.MetaData

    strResults = "Connection Description: " & _
        objCMdhInterfaces.ConnectionDescription & vbCrLf
    strResults = strResults & "Catalogs:" & vbCrLf

    For intCatalog = 0 To objCMdhInterfaces.Catalogs.Count - 1
        strResults = strResults & " " & _
            objCMdhInterfaces.Catalogs.Item(intCatalog).Name & vbCrLf
    Next intCatalog

    strResults = strResults & "Active Catalog: " & _
        objCMdhInterfaces.ActiveCatalog.Name

    MsgBox strResults

End Sub

CMdhInterfaces Object: Cubes Property
IMdhCube Object: Dimensions Property, LastUpdate Property, Name Property, Type Property
IMdhCubes Object: Count Property, Item Property
IMdhDimension Object: Name Property
IMdhDimensions Object: Count Property, Item Property

The following code sample iterates through the cubes and dimensions on a particular OLAP server.

Private Sub Max3Ax1_Initialized()

    Dim daApp As Max3API.Application
    Dim objCMdhInterfaces As MdhInterfacesLib.CMdhInterfaces
    Dim objIMdhCube As MdhInterfacesLib.IMdhCube
    Dim objIMdhDimension As MdhInterfacesLib.IMdhDimension
    Dim intCube As Integer
    Dim intDimension As Integer
    Dim strResults As String

    Set daApp = Max3Ax1.Application

    daApp.ActiveView.Connect _
        "location= MyOLAPServerName;provider=msolap"

    Set objCMdhInterfaces = daApp.ActiveView.MetaData

    strResults = "Cubes / Dimensions: " & vbCrLf

    For intCube = 0 To objCMdhInterfaces.Cubes.Count - 1

        Set objIMdhCube = objCMdhInterfaces.Cubes.Item(intCube)

        strResults = strResults & _
            "Cube: " & objIMdhCube.Name & vbCrLf & _
            " Last Update: " & _
            objIMdhCube.LastUpdate & vbCrLf & _
            " Type: " & objIMdhCube.Type & vbCrLf

            For intDimension = 0 To objIMdhCube.Dimensions.Count - 1

                Set objIMdhDimension = _
                    objIMdhCube.Dimensions.Item(intDimension)

                strResults = strResults & _
                    "  Dimension: " & objIMdhDimension.Name & _
                    vbCrLf

            Next intDimension

    Next intCube

    Debug.Print strResults

End Sub

IMdhCube Object: MeasuresDimension Property
IMdhMeasure Object: AggregatorType Property, Caption Property, DataType Property, Dimension Property, NumericPrecision Property, NumericScale Property, Type Property, UniqueName Property
IMdhMeasures Object: Count Property, Item Property
IMdhMeasuresDimension Object: Measures Property

The following code sample iterates through the cubes and measures on a particular OLAP server.

Private Sub Max3Ax1_Initialized()

    Dim daApp As Max3API.Application
    Dim objCMdhInterfaces As MdhInterfacesLib.CMdhInterfaces
    Dim objIMdhCube As MdhInterfacesLib.IMdhCube
    Dim objIMdhMeasure As MdhInterfacesLib.IMdhMeasure
    Dim intCube As Integer
    Dim intMeasure As Integer
    Dim strResults As String

    Set daApp = Max3Ax1.Application

    daApp.ActiveView.Connect _
        "location=MyOLAPServerName;provider=msolap"

    Set objCMdhInterfaces = daApp.ActiveView.MetaData

    strResults = "Cubes / Measures: " & vbCrLf

    For intCube = 0 To objCMdhInterfaces.Cubes.Count - 1

        Set objIMdhCube = objCMdhInterfaces.Cubes.Item(intCube)

        strResults = strResults & _
            "Cube: " & objIMdhCube.Name & vbCrLf

        strResults = strResults & " Measures:" & vbCrLf

        For intMeasure = 0 To _
            objIMdhCube.MeasuresDimension.Measures.Count - 1

            Set objIMdhMeasure = _
                objIMdhCube.MeasuresDimension.Measures.Item(intMeasure)

            strResults = strResults & _
                "  Unique Name: " & _
                objIMdhMeasure.UniqueName & vbCrLf & _
                "   Aggregator Type: " & _
                objIMdhMeasure.AggregatorType & vbCrLf & _
                "   Caption: " & _
                objIMdhMeasure.Caption & vbCrLf & _
                "   Data Type: " & _
                objIMdhMeasure.DataType & vbCrLf & _
                "   Dimension Name: " & _
                objIMdhMeasure.Dimension.Name & vbCrLf & _
                "   Numeric Precision: " & _
                objIMdhMeasure.NumericPrecision & vbCrLf & _
                "   Numeric Scale: " & _
                ojIMdhMeasure.NumericScale & vbCrLf & _
                "   Type: " & _
                objIMdhMeasure.Type & vbCrLf

        Next intMeasure

    Next intCube

    Debug.Print strResults

End Sub

IMdhDimension Object: Caption Property, Hierarchies Property, IsMeasuresDimension Property, UniqueName Property
IMdhHierarchies Object: Count Property, Item Property
IMdhHierarchy Object: Caption Property, Cardinality Property, DefaultMember Property, Dimension Property, UniqueName Property

The following code sample iterates through the cubes, hierarchies, and members on a particular OLAP server.

Private Sub Max3Ax1_Initialized()

    Dim daApp As Max3API.Application
    Dim objCMdhInterfaces As MdhInterfacesLib.CMdhInterfaces
    Dim objIMdhCube As MdhInterfacesLib.IMdhCube
    Dim objIMdhDimension As MdhInterfacesLib.IMdhDimension
    Dim objIMdhHierarchy As MdhInterfacesLib.IMdhHierarchy
    Dim intCube As Integer
    Dim intDimension As Integer
    Dim intHierarchy As Integer
    Dim strResults As String

    Set daApp = Max3Ax1.Application

    daApp.ActiveView.Connect _
        "location= MyOLAPServerName;provider=msolap"

    Set objCMdhInterfaces = daApp.ActiveView.MetaData

    For intCube = 0 To objCMdhInterfaces.Cubes.Count - 1

        Set objIMdhCube = objCMdhInterfaces.Cubes.Item(intCube)

            strResults = strResults & "Cube: " & objIMdhCube.Name & 
              vbCrLf

            For intDimension = 0 To objIMdhCube.Dimensions.Count - 1

                Set objIMdhDimension = _
                    objIMdhCube.Dimensions.Item(intDimension)

                    strResults = strResults & _
                        " Dimension Name: " & _
                        objIMdhDimension.Name & vbCrLf & _
                        "  Unique Name: " & _
                        objIMdhDimension.UniqueName & vbCrLf & _
                        "  Caption: " & _
                        objIMdhDimension.Caption & vbCrLf & _
                        "  Cube Name: " & _
                        objIMdhDimension.Cube.Name & vbCrLf & _
                        "  Is This A Measures Dimension: " & _
                        objIMdhDimension.IsMeauresDimension & vbCrLf

                    For intHierarchy = 0 To _
                        objIMdhDimension.Hierarchies.Count - 1

                        Set objIMdhHierarchy = _
                            objIMdhDimension.Hierarchies.Item
                            (intHierarchy)

                            strResults = strResults & _
                            "  Dimension Hierarchy Unique Name: " & _
                            objIMdhHierarchy.UniqueName & vbCrLf & _
                            "   Caption: " & _
                            objIMdhHierarchy.Caption & vbCrLf & _
                            "   Cardinality: " & _
                            objIMdhHierarchy.Cardinality & vbCrLf & _
                            "   Default Member Caption: " & _
                            objIMdhHierarchy.DefaultMember.Caption &
                              vbCrLf & _
                            "   Dimension Caption: " & _
                            objIMdhHierarchy.Dimension.Caption & vbCrLf

                    Next intHierarchy

            Next intDimension

    Next intCube

    Debug.Print strResults

End Sub

IMdhHierarchy Object: Levels Property
IMdhLevel Object: Caption Property, Dimension Property, LevelNumber Property, MemberProperties Property, Members Property, MemberUserProperties Property, Name Property, Type Property, UniqueName Property
IMdhLevels Object: Item Property
IMdhMember Object: Children Property, Dimension Property, Level Property, Name Property, Properties Property, Type Property, UniqueName Property, UserProperties Property
IMdhMembers Object: Item Property

The following code lists information about the first cube on a particular OLAP server, the first dimension in the cube, the first hierarchy in the dimension, the first level in the hierarchy, and the first member in the level.

Private Sub Max3Ax1_Initialized()

    Dim daApp As Max3API.Application
    Dim objCMdhInterfaces As MdhInterfacesLib.CMdhInterfaces
    Dim objIMdhCube As MdhInterfacesLib.IMdhCube
    Dim objIMdhDimension As MdhInterfacesLib.IMdhDimension
    Dim objIMdhHierarchy As MdhInterfacesLib.IMdhHierarchy
    Dim objIMdhLevel As MdhInterfacesLib.IMdhLevel
    Dim objIMdhMember As MdhInterfacesLib.IMdhMember
    Dim intCube As Integer
    Dim strResults As String

    Set daApp = Max3Ax1.Application

    daApp.ActiveView.Connect _
        "location= MyOLAPServerName;provider=msolap"

    Set objCMdhInterfaces = daApp.ActiveView.MetaData

    For intCube = 0 To objCMdhInterfaces.Cubes.Count - 1

        Set objIMdhCube = objCMdhInterfaces.Cubes.Item(intCube)

        strResults = strResults & "Cube: " & objIMdhCube.Name & vbCrLf

        Set objIMdhDimension = objIMdhCube.Dimensions.Item(0)

        strResults = strResults & _
            " First Dimension Name: " & objIMdhDimension.Name & vbCrLf

        Set objIMdhHierarchy = objIMdhDimension.Hierarchies.Item(0)

        strResults = strResults & _
            "  First Hierarchy Unique Name: " & _
            objIMdhHierarchy.UniqueName & vbCrLf

        Set objIMdhLevel = objIMdhHierarchy.Levels.Item(0)

        strResults = strResults & _
            "    First Level Name: " & objIMdhLevel.Name & vbCrLf & _
            "     Unique Name: " & objIMdhLevel.UniqueName & vbCrLf & _
            "     Caption: " & objIMdhLevel.Caption & vbCrLf & _
            "     Dimension Name: " & objIMdhLevel.Dimension.Name & _
            vbCrLf & _
            "     Level Number: " & objIMdhLevel.LevelNumber & vbCrLf & 
              _
            "     Count of Member Properties: " & _
            objIMdhLevel.MemberProperties.Count & vbCrLf & _
            "     Count of Members: " & objIMdhLevel.Members.Count & _
            vbCrLf & _
            "     Count of Member User Properties: " & _
            objIMdhLevel.MemberUserProperties.Count & vbCrLf & _
            "     Type: " & objIMdhLevel.Type & vbCrLf

        Set objIMdhMember = objIMdhLevel.Members.Item(0)

        strResults = strResults & _
            "      First Member Name: " & objIMdhMember.Name & vbCrLf &
             _
            "       Unique Name: " & objIMdhMember.UniqueName & vbCrLf
             & _
            "       Caption: " & objIMdhMember.Caption & vbCrLf & _
            "       Count of Children: " & objIMdhMember.Children.Count
              & _
            vbCrLf & _
            "       Dimension Name: " & objIMdhMember.Dimension.Name &
              _
            vbCrLf & _
            "       Count of Properties: " & _
            objIMdhMember.Properties.Count & vbCrLf & _
            "       Count of User Properties: " & _
            objIMdhMember.UserProperties.Count & vbCrLf & _
            "       Type: " & objIMdhMember.Type & vbCrLf & _
            "       Level Name: " & objIMdhMember.Level.Name & vbCrLf

    Next intCube

    Debug.Print strResults

End Sub

IMdhLevels Object: Count Property
IMdhMembers Object: Count Property

The following code sample provides a count of cubes on a particular OLAP server, a count of dimensions in the first cube, a count of hierarchies in the first dimension, a count of levels in the first hierarchy, and a count of members in the first level.

Private Sub Max3Ax1_Initialized()

    Dim daApp As Max3API.Application
    Dim objCMdhInterfaces As MdhInterfacesLib.CMdhInterfaces
    Dim objIMdhCube As MdhInterfacesLib.IMdhCube
    Dim objIMdhDimension As MdhInterfacesLib.IMdhDimension
    Dim objIMdhHierarchy As MdhInterfacesLib.IMdhHierarchy
    Dim objIMdhLevel As MdhInterfacesLib.IMdhLevel

    Set daApp = Max3Ax1.Application

    daApp.ActiveView.Connect _
        "location= MyOLAPServerName;provider=msolap"

    Set objCMdhInterfaces = daApp.ActiveView.MetaData

    Debug.Print "Cubes: " & objCMdhInterfaces.Cubes.Count

    Set objIMdhCube = objCMdhInterfaces.Cubes.Item(0)

    Debug.Print " Dimensions in First Cube (" & _
        objIMdhCube.Name & "): " & _
        objIMdhCube.Dimensions.Count

    Set objIMdhDimension = objIMdhCube.Dimensions.Item(0)

    Debug.Print "  Hierarchies in First Dimension (" & _
        objIMdhDimension.UniqueName & "): " & _
        objIMdhDimension.Hierarchies.Count

    Set objIMdhHierarchy = objIMdhDimension.Hierarchies.Item(0)

    Debug.Print "   Levels in First Hierarchy (" & _
        objIMdhHierarchy.UniqueName & "): " & _
        objIMdhHierarchy.Levels.Count

    Set objIMdhLevel = objIMdhHierarchy.Levels.Item(0)

    Debug.Print "    Members in First Level (" & _
        objIMdhLevel.UniqueName & "): " & _
        objIMdhLevel.Members.Count

End Sub

Show: