Seeding forecast/Copying data to one entity

[psc]

Hello,

I am trying to set up a business rule that seeds the forecast from actuals to the forecast. This works easily when I am not using all entities to seed. However, I need to somehow take the actual data from each base entity and copy it all into 1 entity for the forecast. The client only needs to forecast at 1 entity but wants to include the actuals from every base. Is this possible to do in 1 rule? I know you can't have the entity on the left side of the calculation so I'm not sure how to solve.

Thanks!

[Koemets · Accepted solution]

I'm thinking you can achieve your result using databuffers.

1) You can only write into your pov entity, so:

Dim strTargetEntity = "TargetEntity"

If api.pov.entity.name = strTargetEntity Then

2) Set the target databuffer something like:

Dim targetBuffer As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("E#" & strTargetEntity)

Dim sourceDataBuffer As Databuffer = api.data.GetDataBufferUsingFormula("FilterMembers(E#TopEntity.Base)",DataApiSctiptmethodType.Calculate,False,targetBuffer)

3) Save to the target buffer:

api.data.SetDataBuffer(sourceDataBuffer,targetBuffer)

Please note, this is not the exact syntax, so corrections on your side are expected.

[rhankey]

There are a few ways this could be accomplished, but in simple terms, when processing the destination entity in forecast, you would cycle through each of the source base level entities from Actual, while aggregating the results to output data buffer.  I presume all base level entities share the same currency of the destination entity, else you have a bit more work to do.

[Sai_Maganti]

Loading via the workflow might be the best option in this case. Here are a couple of options

1. Extract to csv => Change entity as required and load or use a transformation rule for mapping source entities to destination entity
2. Use a DM sequence and transformation rule for mapping source entities to destination entity

[NicoleBruno]

Maybe I'm misunderstanding but wouldn't you use the "If (api.Cons.IsLocalCurrencyForEntity() And Not api.Entity.HasChildren()) Then" beginning to narrow down all base entities then have your calculate line as normal? 

[rhankey]

Something along those lines might be possible if you could "push" the data from the source entities to the destination entity.  However, I don't believe you can write data outside the Data Unit currently being processed.  As such, you have to "pull" the data from the source entities to the destination entity.  The other problem with "pushing" the data is that you would have to come up with a way of aggregating the data in the destination entity, whereas "pulling" makes it easy to reliably aggregate the data.

[NicoleBruno]

Apologies - I read it wrong. I didn't see that it was adding together multiple base entities into one entity. Ignore my comment 🙂 

[Koemets · Accepted solution]

I'm thinking you can achieve your result using databuffers.

1) You can only write into your pov entity, so:

Dim strTargetEntity = "TargetEntity"

If api.pov.entity.name = strTargetEntity Then

2) Set the target databuffer something like:

Dim targetBuffer As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("E#" & strTargetEntity)

Dim sourceDataBuffer As Databuffer = api.data.GetDataBufferUsingFormula("FilterMembers(E#TopEntity.Base)",DataApiSctiptmethodType.Calculate,False,targetBuffer)

3) Save to the target buffer:

api.data.SetDataBuffer(sourceDataBuffer,targetBuffer)

Please note, this is not the exact syntax, so corrections on your side are expected.

[adykes]

Hi Koemets, I have tried adding a separate entity in the ExpressionDestinationInfo property as you implied, but I am getting an "Invalid destination data unit in script" error. This is the error I am actually trying to avoid by using this method as opposed to an api.Data.Calculate, so is there a way to resolve this issue? 

[NickKroppe · Accepted solution]

Hello,

As others in this thread have mentioned, you could do this in many different ways. Possibly the easiest way would be to design a custom calculate finance rule that is executed by a custom calc DM job that cycles through the base entities to copy, fills a result data buffer with data, and writes the results to a given target entity. Further below is an example script you can study to see one possible approach you could take.

To avoid a rule that loops through an entity member list, you could also consider taking the reverse approach by designing a DM sequence that has two custom calc DM steps. The first custom calc DM step would use an entity filter such as E#TopEntity.base that calls a custom calc business rule for each base entity in the DM step filter. Within the business rule called by the first DM step, you could then simply retrieve the Actuals data for each entity and store it in a global data buffer object that would gradually build up with data after iterating through each base entity. The second DM step would then be designed to run for the target base entity to seed the data to, and in this DM step you would call a business rule that retrieves the global data buffer object from memory (all the previously collected base entity data) and sets the data to the target destination for the forecast. The idea is to leverage and build up a global data buffer object that will persist in memory for the user throughout the completion of the entire DM sequence (both steps). The reason you might explore this approach is it could be potentially faster and help you avoid the need to loop through a member list to retrieve the data to seed.

Select Case api.FunctionType

	Case Is = FinanceFunctionType.CustomCalculate
		If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("SeedActualsToForecast") Then
			'only run the rule for a specific target entity (which should be defined in the custom calc DM entity filter) and local currency
			If api.Pov.Entity.Name.XFEqualsIgnoreCase("SomeTargetEntity") AndAlso api.Cons.IsLocalCurrencyForEntity() Then
				'declare the result buffer and empty destination
				Dim resultBuffer As New DataBuffer()
				Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("")
				'declare a list of base entities to pull data from
				Dim baseEntityList As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Pov.EntityDim.DimPk, "E#Houston.base", Nothing)
				If (Not baseEntityList Is Nothing) Then
					'loop through the base entities to copy and retrieve the data from the actual scenario
					For Each baseEntity As MemberInfo In baseEntityList
						Dim baseBuffer As DataBuffer = api.Data.GetDataBuffer(DataApiScriptMethodType.Calculate, "S#Actual:E#[" & baseEntity.Member.Name & "]", destinationInfo)
						'fill the result buffer with data
						resultBuffer = resultBuffer + baseBuffer
					Next
					'save the result buffer to the target destination and set IsDurableData to True as this is a custom calc.
					api.Data.SetDataBuffer(resultBuffer, destinationInfo,,,,,,,,,,,,,True)		
				End If
			End If
		End If
						
End Select

Nick Kroppe

OneStream Software

[psc]

Thank you all, this is very helpful!