BPC NW 10: VBA to get dimension members list and properties

Updated on 2019.06.18: Complete code redesign for better performance.

Updated on 2019.06.14: Code corrected to support proper case for ID column.

Updated on 2019.06.13: Fast code to get property value and late binding to work on EPM or AO.

EPM Excel Add-In provides number of methods to get members and properties. I decided to write some procedure to get all members of the specified dimension with all properties including hierarchies parents. The result is written to the selected Excel sheet the same way as in Administration client of BPC NW 7.5.

Methods of FPMXLClient.EPMAddInAutomation used:

GetPropertyList(strConn, strDim)

to get all properties of the dimension strDim

GetHierarchyMembers(strConn, "", strDim)

to get all members of all hierarchies (“”) of the dimension strDim (base members in different hierarchies will be duplicated!)

GetPropertyValue(strConn, strMemFullName, strProperty)

to get member strMemFullName ([…].[…].[…]) property strProperty

epm.GetPropertyValue function was replaced by Application.Run(“EPMMemberProperty”… to improve performance based on Evgeniy Samardak finding in comment to my other blog!

Application.Run("EPMMemberProperty", "", strMemFullName, strProperty)

to get member strMemFullName ([…].[…].[…]) property strProperty

There is some specifics of getting property values – full member name have to be in the proper hierarchy to get properties like PARENTH1…

The following references are required in Tools -> References:

Reference to FPMXLClient is not required, universal late binding is used in the code!

The code of the procedure:

Option Explicit

Public Sub ShowDimMembers()
' To test DimMembersProperties

    DimMembersProperties "Your_Connection_Name", "Dimension_Name", ThisWorkbook.ActiveSheet

End Sub

Public Sub DimMembersProperties(strConn As String, strDim As String, wsh As Worksheet)
' References required: Microsoft Scripting Runtime
' Will fill wsh sheet starting from the first row, first column
' Developed by Vadim Kalinin 2014.06.04
' Redeveloped by Vadim Kalinin 2019.06.18

    Dim objAddIn As COMAddIn
    Dim epm As Object
    Dim AOComAdd As Object
    Dim blnEPMInstalled As Boolean

    Dim strProps() As String
    Dim strMem() As String
    Dim strMemID As String
    
    Dim lngTemp As Long
    Dim lngTemp1 As Long
    
    Dim lngMemUCount As Long
    Dim lngPropCount As Long
    
    Dim dctMembers As New Scripting.Dictionary
    Dim dctProps As New Scripting.Dictionary
    
    Dim varKey As Variant
    Dim varItem As Variant
    Dim varResult() As Variant
    
    'Universal code to get FPMXLClient for standalone EPM or AO
    For Each objAddIn In Application.COMAddIns
        If objAddIn.progID = "FPMXLClient.Connect" Then
            Set epm = objAddIn.Object
            blnEPMInstalled = True
            Exit For
        ElseIf objAddIn.progID = "SapExcelAddIn" Then
            Set AOComAdd = objAddIn.Object
            Set epm = AOComAdd.GetPlugin("com.sap.epm.FPMXLClient")
            blnEPMInstalled = True
            Exit For
        End If
    Next objAddIn
    
    If Not blnEPMInstalled Then
        MsgBox "EPM is not installed!"
        Exit Sub
    End If
    
    'Get all properties of dimension strDim
    strProps = epm.GetPropertyList(strConn, strDim)
    
    'Fill dictionary with properties
    dctProps.Add "ID", "ID"
    dctProps.Add "EVDESCRIPTION", "EVDESCRIPTION"
    
    For lngTemp = 0 To UBound(strProps)
        If strProps(lngTemp) <> "47932f46-b7b1-4207-b693-d9f7a18aaaed" And _
            strProps(lngTemp) <> "CALC" And _
            strProps(lngTemp) <> "HLEVEL" And _
            strProps(lngTemp) <> "HIR" And _
            Not dctProps.Exists(strProps(lngTemp)) Then
                dctProps.Add strProps(lngTemp), strProps(lngTemp)
        End If
    Next lngTemp
    
    lngPropCount = dctProps.Count
    
    'Get all members with possible duplicates due to multiple hierarchies
    strMem = epm.GetHierarchyMembers(strConn, "", strDim)
    
    'Fill dictionary dctMembers with unique member ID's
    For lngTemp = 0 To UBound(strMem)
        lngTemp1 = InStrRev(strMem(lngTemp), "[", -1) '"[DIM1].[PARENTH1].[MEM1]"
        strMemID = Mid(strMem(lngTemp), lngTemp1 + 1, Len(strMem(lngTemp)) - lngTemp1 - 1)
        'strMemID will be in upper case!
        If Not dctMembers.Exists(strMemID) Then
            dctMembers.Add strMemID, strMem(lngTemp)
        End If
    Next lngTemp
    
    lngMemUCount = dctMembers.Count
    
    ReDim varResult(1 To lngMemUCount + 1, 1 To lngPropCount)
    
    'Fill header row - list of properties
    lngTemp = 1
    For Each varKey In dctProps.Keys
        varResult(1, lngTemp) = varKey
        lngTemp = lngTemp + 1
    Next varKey
    
    'Fill table of members and properties
    lngTemp = 2
    For Each varItem In dctMembers.Items
        lngTemp1 = 1
        For Each varKey In dctProps.Keys
            If Left(varKey, 7) = "PARENTH" Then
                'For PARENTx properties
                strMemID = Left(varItem, InStr(2, varItem, "[")) & varKey & Mid(varItem, InStrRev(varItem, ".", -1) - 1)
                varResult(lngTemp, lngTemp1) = Application.Run("EPMMemberProperty", "", strMemID, varKey)
                If varResult(lngTemp, lngTemp1) = "The member requested does not exist in the specified hierarchy." Or _
                    varResult(lngTemp, lngTemp1) = 0 Then
                        varResult(lngTemp, lngTemp1) = ""
                End If
            Else
                'For other properties
                varResult(lngTemp, lngTemp1) = Application.Run("EPMMemberProperty", "", varItem, varKey)
            End If
            lngTemp1 = lngTemp1 + 1
        Next varKey
        lngTemp = lngTemp + 1
    Next varItem
    
    Set dctMembers = Nothing
    Set dctProps = Nothing
    
    wsh.Range(wsh.Cells(1, 1), wsh.Cells(lngMemUCount + 1, lngPropCount)).Value = varResult
    
    Set wsh = Nothing

End Sub

The result of the procedure execution:

You can modify this code to create custom member selection pop-ups, etc…

B.R. Vadim

P.S. If you implement Excel Add-In for BPC 7.5 to test Dimension then you can view hierarchy on the result of this code!

References:

BPC NW 10: VBA function to get dimension members list by Property value