Referencing system tables to build a view

Kuga901
New Contributor

Are there any issues with using the OS Database "Member" table to generate a view? The solution requires a dynamic list of account members for a SQL View query and the member table seems like the perfect source table. Just to clarify, nothing is being written to this table. 

3 REPLIES 3

Use the NOLOCK hint and I think you should be fine.

tledet
New Contributor III

An alternative to the "Member" table is to use a Business Rule of type, Dashboard Data Set, which would return a DataTable to a Data Adapter.

For example, to get base members of a member.  "Dimension" and "Member" could be passed as string parameters.

Public Function getBaseMembers(ByVal si As SessionInfo) As DataTable
    Try
        Dim lomBaseMembers As New List(Of member)
        lomBaseMembers = BRApi.Finance.Members.GetBaseMembers(si, _
            (BRApi.Finance.Dim.GetDimPk(si,("Dimension"))), _
            BRApi.Finance.Members.GetMember(si, _
                DimType.Account.Id,"Member").MemberId)

        Dim dt As New DataTable
        dt.Columns.Add("MemberName", GetType(String))

        Dim mBaseMember As Member
        Dim strBaseMemberName As String = Nothing

        For Each mBaseMember In lomBaseMembers
            Dim Row As DataRow = dt.NewRow()
            Row("MemberName") = mBaseMember.Name
            dt.Rows.Add(row)
        Next

        Return dt
    Catch ex As Exception
        Throw New System.Exception ("An error has occurred.")
    End Try
End Function

 

ChrisLoran
Valued Contributor

The main issue is that the Member table may be subject to change in a future release of OneStream, e.g. renamed columns, new columns, removal of columns, move of columns into other linked tables.
The documented methods to retrieve member information, by using a Method Query in a Data Adapter, or the BRAPI methods, are safer because they are documented and any changes to these in a future release should be properly notified in the updated documentation, as well as likely to be backward compatible.
The Data Adapter already has a Dynamic Member List as a built-in method, so you don't even have to write SQL.

There aren't many use-cases where I would recommend using an SQL to query the [Member] table directly, but one use-case could be if you are doing it from an external process (i.e. not a process that runs within the OneStream platform) which needs to pulls Member information from OS.