TM1 Forum

Hello,

I have an account dimension that can change their structure over time (slowly changing dimension). I saw some examples with SCD’s where an employee changes his unit and for every structure change the employee gets another key in front of the name. So for the account dimension it would be something like this:

Structure in 2010
All Costs
----1-DirectCosts
----2-IndirectCosts
----3-OtherCosts

Structure in 2009

Direct
----4-DirectCosts

Others
---5-IndirectCosts
---6-OtherCosts

So, although 3-OtherCosts and 6-OtherCosts are same cost center there is an additional key in front of the name. Is this the best approach for SCD’s?
If yes, how do you generate these keys when you upload the costcenter-structure from the database and with Turbointegrator process? Is there one function that gives you the highest number in the dimension and from there the new numbers are generated?

Thanks a lot in advance!

Toto

You have broached a subject that TM1 doesn't do well, or should I say AT ALL. If your hierarchies vary over time you really only have two options:

1) Maintain multiple trees, which looks like you have attempted here. I am not familiar with any "best practice" on this, mainly because I avoid it whenever I can. If I need multiple trees I try to limit it to some form of time grouping like month, quarter, or year and then place the period name in front of the node for each rollup in the tree. Not very pretty, and then you have to devise a scheme for selecting the correct node for reporting.

2) Separate into two dimensions and use zero suppress. In this example you separate the Account rollup and cost center into two dimensions. In your load proces you load data to whichever element in the Account rollup dimension it belongs to based on the period. If you zero suppress your views and reports then the cost center will show only in those Account rollups in the periods where you loaded balances.

Solution 2 is a lot cleaner, but you lose the drill-down the tree type of functionality. Solution 1 keeps that, but can be a nightmare to maintain and also raises user training issues and of course the inevitable string of questions from them when the numbers don't look right (because they looked at the wrong tree, trust me, it WILL happen).

Good luck.

Hi

Do you want to model a full Slowly Changing Dimension, ie capture every single change to the Account Dimension, or do you want to just be able to report in the year-end hierarchy for each Financial Year?

If its the latter, then I suggest that you just prefix the account consolidations with the financial year. The other alternative is to copy the cube data at the end of the financial year, so that you create, eg a Financial_2010 cube, and that would need to have an Account_2010 dimension.

If its the first, then, I agree with the previous comment, this is not easy in TM1 (but I don't think it is easy in any other MOLAP tool either, but probably a bit easier in a ROLAP).

If the requirement to report in a previous account hierarchy is infrequent then one possibility is to rebuild the Account hierarchy on an adhoc basis to the hierarchy in effect at that date.

This assumes that you have an Account Hierarchy in a relational table along the lines of

Child Id Parent Id Start Date End Date
00001 Sales Jan-09 Apr-09
00001 UK Sales May-09 -

Therefore you can say that prior to May-09 Account 00001 should be reported as being part of Sales, but from May-09 it should be reported as being part of UK Sales, eg perhaps the company starting selling in Germany then and set up an Account 00002 for German Sales, and wanted 00001 to just continue for purely UK Sales.

If you have a relational table like that to draw from ( and you would need some way to store the SCD ) then you can rebuild the hierarchy as at any point in time just by passing a parameter into a TI. Eg if you pass in Mar-09 then it should only select links where the Start-Date is on or before Mar-09 and the End Date is on or after Mar-09 or Null.

When rebuilding the hierarchy be sure to avoid deleting Account Ids that have been added after this time, just because they are not in the hierarchy.

The approach should be

1) Break all consolidation links.
2) Rebuild all links using the earlier hierarchy
3) Link any Account Id that does not have a parent to an Orphan consolidation. Link this in turn to all Accounts. Verify that the Orphan consolidation does not have any values on it. This would indicate that values had been possibly backdated to an Account Id that did not exist at the time the old hierarchy was in force, or that the old hiearchy was not complete.

If the approach is frequent, then you could pursue something along the lines of what you are suggesting. However, I would suggest that that is a road to a rather difficult and cumbersome cube. I have worked with accountants for a good many years and I have never come across a requirement for historic hierarchies beyond the year end case. In most cases that has just been a matter of taking a copy of the cubes directory at the end of each financial year, and just starting it up as a separate server if anyone ever needed to refer to it.

Regards

Paul Simon