Forum Discussion

Brooks's avatar
Brooks
New Contributor II
5 months ago

Converting Historical Data

We are in the process of changing our chart of accounts and department structure (for us, UD3 and UD1, respectively).  Further complicating things is that some of the old UD3/1 members will likely be consolidated into one UD3/1 member, and vice versa.  We will need to keep the existing data in its original intersections (e.g if UD3#ABC changes to UD3#123, we need the historical data to stay in UD3#ABC).  Or do we?

We currently map each of our 3 different GLs* in UD3 to accounts in the A# dim which is at the P&L/BS level.  Has anybody out there done this before?  What approach did you take?  Can we simply create new transformation rules and somehow reference the old ones for historical data?  Do we simply create a new hierarchy in UD3 for the new members (bloat?)? If so, how do we reference them in dashboards and cube views that compare past and present data? 

I have so many questions as to the best or simplest way to do this.  Bonus points if they are one and the same.

--Brooks

*Only one GL is changing

Metadata
  • TheJonG's avatar
    TheJonG
    Contributor III
    5 months ago

    Hi there - I'm afraid there isn't a perfect solution to this and will likely be pros/cons to any approach taken.

    For transformation rules, the only way to do Time dependent mapping is to use Composite rules e.g. T#2024M1:U1#ABC which is not practical in your situation because it would blow up the mapping and would need to include all time periods. This would also kill performance as composite mappings take significantly longer to process. For this, my only suggestion at the moment would be to create new data sources & import steps in the workflow and only use the new ones going forward.

    Hierarchies are generally the best way to preserve historical structures so this would be my only suggestion although, comparing to previous data will need to be considered but can be accomplished via XFBR rules. You could also use text fields since those are time dependent to denote when members became active which could help with filtering member lists. 

    Again, not super easy solutions for this requirement. My suggestion would be to create a new scenario with the new dimensions assigned and reload all historical data there. They can still have the old scenario there for reference but it doesn't make much sense to have an apples to oranges comparisons across history when the mapping has changed.