Search for member usage in cube views

[MatthieuR]

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

[FredLucas]

Hello @MatthieuR,

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

[Henning]

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 · Accepted solution]

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

[Henning]

Great, thanks a lot for sharing!!

[MatthieuR]

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