Forum Discussion

fc's avatar
fc
Contributor
3 days ago

Split hierarchy into 2 dimensions to ensure retroactivity

Hi everyone,

We are currently developing a planning solution in OneStream for a client who has requested that the Cost Center dimension (and its related nodes) be updateable every year, while still maintaining full retroactivity when users need to view reports for prior years.

We have considered two possible approaches:

OPTION 1
Each year, load a new Cost Center hierarchy associated only with that specific year. When users want to analyze historical data, they simply select the hierarchy corresponding to the year they are reviewing.
The main drawback of this approach is that, over time, multiple versions of the Cost Center hierarchy will accumulate, which could potentially impact maintainability or performance in the long term.

OPTION 2
Separate Cost Centers and their nodes into two distinct dimensions. Introduce a technical member in the Account dimension and use a data entry form (maintained annually) where users enter a value of “1” at the intersection of each Cost Center and its corresponding Node.

Source data is initially loaded only at the Cost Center level (using a dummy member for the Node dimension). At the end of each cycle, a Business Rule is executed to populate the Node dimension based on this mapping.
This approach avoids unnecessary data duplication, but it introduces additional process complexity and reliance on manual maintenance.

Does anyone have experience with similar requirements, alternative approaches or feedbacks on this topic?

Thank you in advance!

Metadata

1 Reply

  • Ruben_Moya's avatar
    Ruben_Moya
    New Contributor II
    16 hours ago

    Hello fc,

    The best and most common approach is the first option, saving the old Hierarchy as an alternative hierarchy with a name like XXX_YYYY. I guess that the Application Control Manager solution can help you with this.

    It will not affect performance if you don't query that alternate hierarchy, neither maintainability because you don't have to change those alternative hierarchies.

    If you worry about managing Cube Views, you can automate it by creating an XFBR or GetMember rule that retrieves the correct hierarchy based on the Year.

    Hope this helps!