Forum Discussion

BenvanBergen's avatar
BenvanBergen
Contributor
7 months ago

Copy account to other account on IC entity

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

Planning
  • Henning's avatar
    Henning
    Valued Contributor II
    6 months ago

    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's avatar
      BenvanBergen
      Contributor
      6 months ago

      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's avatar
        Henning
        Valued Contributor II
        6 months ago

        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.