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

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

5 Replies

FredLucas
OneStream Employee
2 years ago
Hello MatthieuR,

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

Henning

OneStream Employee

2 years 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

Valued Contributor

2 years 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

Henning
OneStream Employee
2 years ago
Great, thanks a lot for sharing!!

MatthieuR
New Contributor III
2 years ago
Thank you all for your valuable insights, we will look into this!

Forum Discussion

Search for member usage in cube views

5 Replies

Related Content

Workspaces usage

Where Clause usage for Member Filter in Cube View

How to search for member in list of members

Usage of DataBuffer1, DataBuffer2

Usage of Report component