Copy account to other account on IC entity

[BenvanBergen]

Hello,

I'm looking for a member formula, or business rule to copy data from an entity to the IC entity, where also another UD member is substituted.

Example of source data:

Cb#100:E#3100:P#?:C#EUR:S#May_C2:T#2024M5:V#Periodic:A#ICfreightPrice:F#Calc:O#Import:I#3342:U1#110F:U2#3100:U3#3344:U4#4002177:U5#None:U6#None:U7#None:U8#USD

Destination:

Cb#100:E#3342:C#Local:S#May_C2:T#2024M5:V#Periodic:A#ICfreightPrice_Pur:F#Calc:O#Import:I#3100:U1#None:U2#3344:U3#None:U4#4002177:U5#None:U6#None:U7#None:U8#USD

So entity becomes IC, U3 becomes U2. To substitute 1 I can use a For each loop, but how can I add a nested For each?

thanks a lot for some guidance.

Ben

[Henning]

Hi Ben, I assume you are familiar with the data unit concept in OneStream. If not, no problem, you can find some information here:

Data Units (onestream.com)

Here, the Level 1 data unit is relevant, which is the combination of the following dimensions: Cube, Entity, Parent, Scenario, Time and Consolidation.

In member formulas and business rules, you can only "pull" data from different data units into the data unit you are currently calculating. I.e. in your case, your processed data unit is Cb#100:E#3100:P#?:C#EUR:S#May_C2:T#2024M5. The crucial part here is the E#3100. So if you were to write an api.data.calculate in which you are trying to pull data to E#3342, this will not execute as this is outside of your current data unit. I assume you tried that, which did not work, which then led to this post ( 😉 ).

So, one way to get around this is by triggering a calculation in your destination data unit, using E#3342. You can do that by triggering a Custom Calculate Data Management Step.

You see under Data Units settings the data units this calculation will be triggered for.

And then under Business Rule you refer to the custom Finance BR you wish to execute pulling the data into your destination data unit.

Attention: Performance Implications!

However, what you describe here sounds like something that may impact your consolidation performance negatively. Be aware that triggering such processes crossing data units as a destination and possibly using loops plus nested loops to manipulate the data as needed often results in a poor performance when not being crafted with care. Please use loops only when really needed, use data buffers and only execute a data management step from your current process once (or at least keep it to a minimum). Test this and make sure this all works as expected. Otherwise, this may get placed outside of the normal consolidation routine and executed directly from DM or via a dashboard button. This way this would be an external process, not impacting consolidation performance. This would need to be embedded into the workflow as needed.

You are probably aware of all of this, but I am adding this also for the wider audience which might refer to this post at some point.

[BenvanBergen]

Thanks a lot Henning!

I'm aware of the Data Unit concept, but not always clear on the implications, good that you mention it.

My main question here is how to write 2 loops that are nested. 1 would be to loop through the 7 IC members, the other to loop through the UD3 (40 members). So that I can specify , the different destination and source members.

A BR is probably better, I can include it in a seeding step, because the input is in another scenario.

I appreciate your valuable input, thanks again!

[Henning]

Hi, sounds like a data buffer will suit this best.

E.g. use ...

api.Data.GetDataBufferUsingFormula("RemoveZeros(myMemberFilter(s)")

 ...loop through the cells of the data buffer and for each IC member and UD3 member, you change the member to your destination member and push the call to your destination data buffer which you post to the cube in the end.

[FredLucas · Accepted solution]

Hi @BenvanBergen,

Another option you could consider which could be easier to set up and best from a performance point of view would be to use Data Management Export Sequences - especially if this is going to apply across most of your entities / ICs.

Have a look at this Tech Talk that explains how to configure a DMES. This subject is covered from minute 20 onwards:

https://community.onestreamsoftware.com/t5/OneStream-Tech-Talks-Continue/New-Episode-of-Tech-Talks-A... 

This will also allow you to easily flip or map your UD2 and UD3 at the same time.

Hope this helps.

[BenvanBergen]

Thanks Fred, I'll have a look at that tomorrow.

[BenvanBergen]

This looks good Fred, what it does is using the already created data to import again and during the import the transformation rules can be used.

I will give it a try.

[BenvanBergen]

I could get this to work, (unfortunately it's not seemless. I need to calculate first to get the USD values. Below I show the export of LC values (on UD8), but when substituting the entity the LC amount is not useful), but it's not that difficult to create, see below the steps and some screenshots.

- Created a DM step of type Export Data

- Then the DM sequence (nothing specific)

- Created a DataSource under Data Management Export Sequence, referencing the DM sequence.

Substituting IC with Entity and UD2 with UD3

For the account I added a complex expression to substitute sales account with purchase account, could also create a separate transformation rule for it.

Then the import step in the workflow profile:

[Henning]

(deleted)