Forum Discussion

Brooks's avatar
Brooks
New Contributor III
9 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
    8 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. 

    • Brooks's avatar
      Brooks
      New Contributor III
      5 days ago

      Hello, TheJogG,

      This is both a long overdue thank you for your response and a request for clarification.  In your last paragraph, are you suggesting that we load the old data, remapped to the new chart of accounts into a new scenario and continue to use that going forward, keeping the old scenario for posterity?  If that is the case, what method do you suggest for remapping the old data to the new COA.  That seems like a Herculean task.  It is also a task that my management mentioned as one they feared.

      I do like the new hierarchies, data sources et al approach.  It seems the most straight forward, but I agree, historical comparisons will be a challenge, especially if we do this mid-year.