Forum Discussion

MatthieuR's avatar
MatthieuR
New Contributor III
7 months ago

Search for member usage in cube views

Hello,

Could you tell me if there is a way to search in which cube views a certain member is used/referenced ?

I thought of doing a export of XML and do a search in notepad++ but maybe there is a better way/more user friendly for administrators which are not technical (which is the case for my current client)

Thank you

Matthieu

Metadata
User Interface
  • MarcusH's avatar
    MarcusH
    7 months ago

    That is a really good idea Henning. And I have written a BR to do just that. Here is the code I used for reading the XML string in case it helps:

    Dim sql As New Text.StringBuilder
sql.Append("SELECT Name, Description, XMLData ")
sql.Append("FROM CUBEVIEWITEM ")
sql.Append("ORDER BY NAME ")

Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    Dim dt As DataTable = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False)
    If dt.Rows.Count > 0  Then
        For Each dr As DataRow In dt.Rows()
            Dim CVName As String = dr("NAME")
            Dim CVDesc As String = dr("DESCRIPTION")
            Dim XMLString As String = dr("XMLData")
            Dim DimensionStringID as String = "A#Revenue"
            If Not String.IsNullOrEmpty(XMLString) Then
                ' Load the XML string into an XDocument
                Dim xdoc As XDocument = XDocument.Parse(XMLString)
                ' Query all memberFilter nodes and filter those containing strings starting with the DimensionStringID eg A#, U1#
                Dim memberFilters = From mf In xdoc.Descendants("memberFilter")
                                    Where mf.Value.Contains(DimensionStringID)
                                    Select mf.Value
                
                ' Process each memberFilter node value
                For Each filter As String In memberFilters
                    ' Split the filter value by colon and find elements starting with DimensionStringID eg A#, U1#
                    Dim elements = filter.Split(":"c)
                    Dim memberElements = From el In elements
                                         Where el.StartsWith(DimensionStringID)
                                         Select el

                    ' Save the member element
                    For Each memberElement As String In memberElements
                        ' Comes through as A#Revenue so get just Revenue
                        Dim HashIndex As Integer = memberElement.IndexOf("#"c)
                        Dim thisMember As String =  memberElement.Substring(HashIndex + 1)
                        ' Add to output somehow....
                    Next
                Next
            End If
        Next
    End If
End Using

     

  • FredLucas's avatar
    FredLucas
    Contributor III
    7 months ago

    Hello MatthieuR,

    That's the way I do it too, I don't think there's any other supported way unfortunately.

  • Henning's avatar
    Henning
    Valued Contributor II
    7 months ago

    Hi, you could create a quick dashboard with a view on the CubeViewItem table. You could allow the customer to search for his members in the XmlData column and return the Name column.

    With some effort, this could be really nice and - of course - be re-used at other customers too. The XmlData column in itself is probably a little cryptic for non-technical customers, but if you apply proper filters, you could just pull the information that is needed and differentiate between columns and rows too.

    • MarcusH's avatar
      MarcusH
      Contributor III
      7 months ago

      That is a really good idea Henning. And I have written a BR to do just that. Here is the code I used for reading the XML string in case it helps:

      Dim sql As New Text.StringBuilder
sql.Append("SELECT Name, Description, XMLData ")
sql.Append("FROM CUBEVIEWITEM ")
sql.Append("ORDER BY NAME ")

Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    Dim dt As DataTable = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False)
    If dt.Rows.Count > 0  Then
        For Each dr As DataRow In dt.Rows()
            Dim CVName As String = dr("NAME")
            Dim CVDesc As String = dr("DESCRIPTION")
            Dim XMLString As String = dr("XMLData")
            Dim DimensionStringID as String = "A#Revenue"
            If Not String.IsNullOrEmpty(XMLString) Then
                ' Load the XML string into an XDocument
                Dim xdoc As XDocument = XDocument.Parse(XMLString)
                ' Query all memberFilter nodes and filter those containing strings starting with the DimensionStringID eg A#, U1#
                Dim memberFilters = From mf In xdoc.Descendants("memberFilter")
                                    Where mf.Value.Contains(DimensionStringID)
                                    Select mf.Value
                
                ' Process each memberFilter node value
                For Each filter As String In memberFilters
                    ' Split the filter value by colon and find elements starting with DimensionStringID eg A#, U1#
                    Dim elements = filter.Split(":"c)
                    Dim memberElements = From el In elements
                                         Where el.StartsWith(DimensionStringID)
                                         Select el

                    ' Save the member element
                    For Each memberElement As String In memberElements
                        ' Comes through as A#Revenue so get just Revenue
                        Dim HashIndex As Integer = memberElement.IndexOf("#"c)
                        Dim thisMember As String =  memberElement.Substring(HashIndex + 1)
                        ' Add to output somehow....
                    Next
                Next
            End If
        Next
    End If
End Using

       

  • MatthieuR's avatar
    MatthieuR
    New Contributor III
    7 months ago

    Thank you all for your valuable insights, we will look into this!