Extract Active Entity list from OneStream by using Data Management Job

[Shetal_P]

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!  

[Sai_Maganti · Accepted solution]

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

[Koemets]

In which format are you expecting to see the list? Also, any reservations about metadata export? You can simply export the whole dimension.

[Koemets]

Bottom line, yes, it is possible. But there are many ways to skin a cat depending on other factors.

[Shetal_P]

Thanks for your feedback. Can you please throw some ideas here and see if that works with our requirements and current application setup?

[Sai_Maganti]

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.

[Mark_DiMatteo]

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.

[Sai_Maganti · Accepted solution]

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

[Shetal_P]

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 .

[Shetal_P]

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.

[Koemets · Accepted solution]

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.

[Shetal_P]

That trick worked. Thank you all for your help and guidance.

Regards, Shetal

[Sai_Maganti]

.. and then right click on the results table and export

[Koemets]

Sucks that the system doesn't let you to edit the original post, does it? 🙂

[Sai_Maganti]

Actually there is (clicking on the down arrow) but only found after posting the last one 🙂

[Koemets]

Oh, thank you! I learned something today 🙂

[Marlin]

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?