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.