01-06-2022
11:41 AM
- last edited on
05-02-2023
10:07 AM
by
JackLacava
Hi all, Happy New Year! Is there a way of extracting an active entity list from OneStream including Entity Name, Description, and Currency fields by using a data management job? Any ideas will be appreciated. Thank you!
Solved! Go to Solution.
01-07-2022 08:26 AM
I see why you want to use a DM. Here is some basic BR which gets the list of entities from member info and writes a csv file to the system. You may modify to suit your requirements.
Namespace OneStream.BusinessRule.Extender.ExportEntitiesMetaData
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
Select Case args.FunctionType
Case Is = ExtenderFunctionType.Unknown, ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep
Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppToken.AppName) & "\MetaData"
If Not Directory.Exists(folderPath) Then Directory.CreateDirectory(folderPath)
Dim filePath As String = folderPath & "\EntityList.csv"
If File.Exists(filePath) Then File.Delete(filePath)
Using dbConnFW As DbConnInfo = BRAPi.Database.CreateFrameworkDbConnInfo(si)
Using dbConnApp As DbConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
'Get the Members using member script
Dim mf As String = "E#YourEntityTop.Base"
'Set up entity dimension
Dim objDim As OneStream.Shared.Wcf.Dim = DimsWcf.GetDimUsingName(dbConnFW, dbConnApp, "Entities")
Dim members As List(Of MemberInfo) = MetadataWcf.GetMembersUsingFilter(dbConnFW, dbConnApp, Nothing, si.WorkflowClusterPk, si.PovDataCellPk, objDim.DimPk, mf, 0, Nothing, Nothing, True, False, True)
Dim sb As New StringBuilder
sb.AppendLine("Name,Description,Currency")
For Each memInfo As MemberInfo In members
sb.AppendLine(memInfo.Member.Name & "," & memInfo.Member.Description.Replace(",", "_") & "," & BRApi.Finance.Entity.GetLocalCurrency(si, memInfo.Member.MemberId).Name)
Next
' Write the output string to a file
Dim sw As New StreamWriter(filePath)
sw.Write(sb)
sw.Close()
End Using
End Using
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
01-10-2022 11:44 AM
It's InUse function, use it as follows:
For Each memInfo As MemberInfo In members
Dim boolInUse As Boolean = BRApi.Finance.Entity.InUse(si, memInfo.Member.MemberID, varyByScenarioTypeId, varyByTimeId)
If boolInUse Then
sb.AppendLine(memInfo.Member.Name...
ETC...
Make sure you define varybyscenario and varybytime types in the InUse function.
01-06-2022 11:49 AM
In which format are you expecting to see the list? Also, any reservations about metadata export? You can simply export the whole dimension.
01-06-2022 11:51 AM
Bottom line, yes, it is possible. But there are many ways to skin a cat depending on other factors.
01-06-2022 12:01 PM
Thanks for your feedback. Can you please throw some ideas here and see if that works with our requirements and current application setup?
01-06-2022 12:05 PM - edited 01-06-2022 12:27 PM
Not sure why you want to do from DM job but using the Grid view from the Dimension is easiest and then right click on the results table and export. Data Management as it suggests is ideal for data jobs and not for Metadata exports though it could be possible.
01-06-2022 09:37 PM
Sai, we want this to be lights out - scheduled so that we can send to other applications in our organization. I don't want to manually perform this step every month. The task scheduler is limited in OneStream, thus the reason for asking it to be a data management job. Preferably a delimited file like a CSV but other formats would work.
01-07-2022 08:26 AM
I see why you want to use a DM. Here is some basic BR which gets the list of entities from member info and writes a csv file to the system. You may modify to suit your requirements.
Namespace OneStream.BusinessRule.Extender.ExportEntitiesMetaData
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
Select Case args.FunctionType
Case Is = ExtenderFunctionType.Unknown, ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep
Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppToken.AppName) & "\MetaData"
If Not Directory.Exists(folderPath) Then Directory.CreateDirectory(folderPath)
Dim filePath As String = folderPath & "\EntityList.csv"
If File.Exists(filePath) Then File.Delete(filePath)
Using dbConnFW As DbConnInfo = BRAPi.Database.CreateFrameworkDbConnInfo(si)
Using dbConnApp As DbConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
'Get the Members using member script
Dim mf As String = "E#YourEntityTop.Base"
'Set up entity dimension
Dim objDim As OneStream.Shared.Wcf.Dim = DimsWcf.GetDimUsingName(dbConnFW, dbConnApp, "Entities")
Dim members As List(Of MemberInfo) = MetadataWcf.GetMembersUsingFilter(dbConnFW, dbConnApp, Nothing, si.WorkflowClusterPk, si.PovDataCellPk, objDim.DimPk, mf, 0, Nothing, Nothing, True, False, True)
Dim sb As New StringBuilder
sb.AppendLine("Name,Description,Currency")
For Each memInfo As MemberInfo In members
sb.AppendLine(memInfo.Member.Name & "," & memInfo.Member.Description.Replace(",", "_") & "," & BRApi.Finance.Entity.GetLocalCurrency(si, memInfo.Member.MemberId).Name)
Next
' Write the output string to a file
Dim sw As New StreamWriter(filePath)
sw.Write(sb)
sw.Close()
End Using
End Using
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
01-07-2022 08:34 AM
Thank you, Sai, for your guidance and attaching the sample BR here on the forums. I will try this and let you know if I have any questions .
01-10-2022 11:31 AM
Sai, I was able to pull the entity metadata into a csv file by using the BR you attached earlier. Is there any way to exclude inactive entities in the rule? The current rule is pulling all entities (active and In-active) and we would like to pull only the active members for the current close period set on Global Time POV in OneStream. Thank you in advance.
01-10-2022 11:44 AM
It's InUse function, use it as follows:
For Each memInfo As MemberInfo In members
Dim boolInUse As Boolean = BRApi.Finance.Entity.InUse(si, memInfo.Member.MemberID, varyByScenarioTypeId, varyByTimeId)
If boolInUse Then
sb.AppendLine(memInfo.Member.Name...
ETC...
Make sure you define varybyscenario and varybytime types in the InUse function.
01-10-2022 12:09 PM
That trick worked. Thank you all for your help and guidance.
Regards, Shetal
01-06-2022 12:07 PM
.. and then right click on the results table and export
01-06-2022 12:20 PM
Sucks that the system doesn't let you to edit the original post, does it? 🙂
01-06-2022 12:27 PM
Actually there is (clicking on the down arrow) but only found after posting the last one 🙂
01-06-2022 12:29 PM
Oh, thank you! I learned something today 🙂
02-09-2023 04:11 PM
If trying to pull ParentMember and ParentDescription, using this code I'm not seeing that as an option under memInfo.Member. Does anyone here have any suggestions on how to pull these?