Skip to main content
B
BenvanBergen
May 31, 2024
Solved

Copy account to other account on IC entity

  • May 31, 2024
  • 2 replies
  • 0 views

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

    Best answer by FredLucas

    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-Available-Now/td-p/33894 

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

     

    Hope this helps.

    2 replies

    H
    Henning
    June 3, 2024

    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.


    Henning_2-1717398903723.png

    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.

    B
    BenvanBergenAuthor
    June 3, 2024

    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!

     

    H
    Henning
    June 3, 2024

    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.

    H
    Henning
    June 3, 2024

    (deleted)

    Terms & ConditionsAccessibility statement