01-25-2023 09:58 AM
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.
01-25-2023 10:44 AM
Use the NOLOCK hint and I think you should be fine.
01-25-2023 12:01 PM - last edited on 01-26-2023 06:45 AM by JackLacava
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
01-26-2023 05:51 AM
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.