Skip to main content
Newcomer
Jan 25, 2023 2:58 PM UTC

Referencing system tables to build a view

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

Member
Jan 25, 2023 3:44 PM UTC

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

OneStream Employee
Jan 25, 2023 5:01 PM UTC

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

 

OneStream Employee
Jan 26, 2023 10:51 AM UTC

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.