Forum Discussion

Satish's avatar
Satish
New Contributor II
26 days ago

Custom Consolidation

Hi,

I have a requirement from business that, want to restrict the roll-up in the entity dimension.

for ex: i have Top entity, and all the members underneath are rolling up to Top, but i want to restrict one entity (ex: entity A) not to roll up for particular users which are part of group A.
So, when Group A users retrieve the data at Top entity, he/she should see the total number except restricted entity(entity A) and all other can see the total numbers including entity A at Top.

is there any way to restrict the roll-ups based on security.

Thanks in Advance.
any suggestions please, Appreciate your help.

Thanks
Satish P

Finance

9 Replies

  • Henning's avatar
    Henning
    Icon for OneStream Employee rankOneStream Employee
    26 days ago

    Hi, just create an alternate hierarchy in the entity dimension without entity A and give your restricted user only access to this alternate hierarchy. Seems like the easiest option to me.

    • Satish's avatar
      Satish
      New Contributor II
      25 days ago

      Hi,

      Thanks for your comments.
      We would prefer not to create an alternate hierarchy. Is there any other approach to achieve this?

      I am aware that OneStream provides Custom Consolidation options within the Cube settings, which allow us to control or override parts of the consolidation process.

      If anyone has experience implementing or configuring Custom Consolidation rules, insights or examples would be really helpful.

      Thanks
      Satish P

      • Henning's avatar
        Henning
        Icon for OneStream Employee rankOneStream Employee
        25 days ago

        Custom consolidation rules are not a good fit for the described purpose. That would bring along added complexity and impacting performance for something that can be solved easily by different means. 

        Another scenario such as the one Kamila suggested is an option too, though the total data volume stored in the app is larger than using an alternate hierarchy. In normal apps that is still perfectly fine though.

        One could also use dynamic cube services (DCS). But again, that is more complex and I want to describe it as "shooting sparrows with a cannon" in the given context. 

  • Kamila's avatar
    Kamila
    Contributor
    25 days ago

    Hello Satish,

    What came to my mind is just create a new scenario, copy there records from your old scenario. Then in Entity dimension -> Relationship Properties -> scenario type you can change consolidation from 100 to 0. It will just affect this one scenario 

     

  • MarcusH's avatar
    MarcusH
    Valued Contributor
    25 days ago

    I agree with Henning​ that an alternative hierarchy is the simplest way to do it. Another scenario I think has too many problems associated with syncing data. I suppose you could write a custom consolidate BR that updates a UD member during consolidation. You have UD members GroupA, OthersGroups and their parent is TotalGroups. During Consolidate, the BR looks for the Entity being consolidated - if it's GroupA, the UD member for the data is switched to GroupA, everything else goes to OthersGroups. Then use Slice Security so that GroupA users only see OthersGroups and everyone else sees TotalGroups. I'm not recommending it; it's a possibility.

    • Kamila's avatar
      Kamila
      Contributor
      24 days ago

      Hi Marcus,

      Just for the context - since we have the 'Hybrid Source Data' settings under Scenario the syncing shouldn't be that problematic. Btw Satish​ I can even see here you can exclude your member:

       What it's trying to say setting these above parameters means that user doesn't need to even write formula.

      The only thing with which I agree is that they need to remember about consolidation, but that might be also scheduled.

  • vasantharaidu's avatar
    vasantharaidu
    New Contributor III
    19 days ago

     

    1. Parent entities always include child entity values because consolidation follows the hierarchy structure by design.
    2. User security cannot change consolidation results—it only controls visibility, not the underlying financial rollup.
    3. Even if a user has no access to a child entity, its values still flow into the parent totals.
    4. Setting consolidation % to 0% would remove the child entity’s impact, but, It affects all users, not specific ones. It alters financial logic, which is usually not acceptable.
    5. Because consolidation cannot be modified per user, you cannot exclude a specific child entity for a specific user within the main hierarchy.
    6. The correct and recommended approach is to use an Alternate Entity Hierarchy.
    7. An alternate hierarchy allows: Excluding specific entities from rollups Creating user‑specific reporting views Keeping the main hierarchy intact for official consolidation
    8. Users can be assigned access to the alternate hierarchy to see clean parent totals without restricted children.
  • Satish's avatar
    Satish
    New Contributor II
    20 days ago

    Hi All,

    Thanks all for your comments, will try out the options which you all have suggested and let you know guys how it worked.
    but still i am happy to receive if any suggestions/options (other than above options) you guys think will work for my requirement, pls don't hesitate to post.

    Thanks in Advance.

    Thanks
    Satish P

    • rhankey's avatar
      rhankey
      Contributor III
      20 days ago

      Unlike other dimensions, data in parent entities is stored in the cube.  So, you cannot have the consolidated values in a parent entity vary based on the user running the report.

      The simplest and most performance efficient solution if you can't just add an intermediate parent in the existing hierarchy, as already mentioned, would be to have a second Entity hierarchy in which all the existing base entities roll up to a new parent and use PercentConsolidation=0 to exclude entities, or only map the existing base entities to be consolidated to the new parent.  This solution requires a second hierarchy to be consolidated, but the only extra data generated is O#Elimination, O#Share between new parent & existing base, and the new parent.  With this method base entities are only Calculated and Translated once, so the second consolidation will run quicker.

      As also pointed out, you could retain a single Entity hierarchy, but copy the base entity data to another Scenario, or even use a hybrid or dynamic Scenario in which you exclude copying or set PercentConsolidation=0 for the entities to exclude.  However, these options require all the base entity data to be duplicated one way or another, and a second complete consolidation.  This is much more costly, as you are doubling the amount of data and consolidation will run longer since all the base entities need consolidating a second time.