Loading data into TM1 using element Attributes

[Jodha]

My client has resently changed all his accounting codes from old to new and decided to go with only new codes from end of this year.

new code Old code
367 110

but all historical data in database still points to old codes they are planning to make changes at database level at later point of time. My client is intrested in historical data in order to perform anlaysis trend .

In orede to get historical data into Tm1 i am planning to create an attribute which points to old code for each new code elemnt for each dimension and map this old code attribute to
old codes that are available in database such that irresespective of changes for code i can still see historical data for all codes.

only new code are present as elements in dimension

element old code attribute for element
367 110


i cannot use old codes as alias for new codes because few of the new codes has circular refernces with old codes and my client wants to adress this isuue at later point of time(he feels this is not crtical for now).

Is there a way to bring element name from elemnt attribute ??

can any one suggest me any better approach for this requirement.

your advise is appreciated .

[declanr]

i cannot use old codes as alias for new codes because few of the new codes has circular refernces with old codes and my client wants to adress this isuue at later point of time(he feels this is not crtical for now).

Assuming you already have your cubes built using an accounts dimension which uses the old codes I would simply start by creating an alias called "old codes" then move all the old codes into that alias but prefix them with a letter such as "z". Then go through and change all the principle names to be the new codes... assuming that there isn't also an issue with codes moving between parents as a result of this?

[Jodha]

Thanks for the replay i will try this but is there any other approach apart from using alias

[Jodha]

Can any one can suggest me another approach please

[AmbPin]

Hello Jodha,
Based on the information you have provided, the approach that declan has suggested would seem to me be the best approach. Is there some reason, that perhaps is not described in your original post, that means you are looking for an alternative method?

[Jodha]

Thanks for the reply .I tried declan's method it worked for me but just want to know still there are any other approaches apart from using alias.

[declanr]

Thanks for the reply .I tried declan's method it worked for me but just want to know still there are any other approaches apart from using alias.

It would help if you could elaborate on why the structure has changed and what the client does/doesn't need to still report in terms of history.

That being said, one other solution does come to mind but it entirely depends on their situation:

• Standard Accounting practice would generally state that you would be unlikely to report current/future numbers against a historic GL structure
• You would have to view the historic values against the current GL structure to be able to get a true "like for like" comparison

On these 2 points what you could do is have 2 cubes:

• Historic-Cube
• Cube

Both cubes will be identical in terms of dimensionality with the exception of your GL_Accounts dimension of which the "Historic-Cube" would contain a "Historic_GL" and the standard Cube would contain your current GL.

The historic cube would only hold data up until the last reported point for your historic GL.

The (current) cube would contain all the historic data (which you can TI from the old codes to current ones with a VERY basic TI) and then all your data from after the point the GL changed would be input into this cube only as it becomes available.


The exact split of when you stopped needing to hold data in the "old" format would be prescribed by the most Senior Reporting Accountant or FD etc but the majority of the time significant changes to the GL would be done over the course of a Financial Year End... but not always.



I hope that perhaps puts things in perspective but I would very much suggest if your original solution doesn't actually fit the bill, you provide as much detail as is possible about what the customer wants/needs and we may then be better able to make a suggestion before you go off and put another solution into practice that isn't actually the best option for the circumstances.

[foogy]

they are planning to make changes at database level at later point of time.

Adapting the source data from old to new accounts is crucial for such changes and should be the first step, if analysis on old data is still required, especially if to be analyzed against future data in new structure. To me it looks that someone defined a new account structure without taking other involved parties with him. Does the same person also cross the street without looking to the left and to the right? Sure, he may be faster across the street, but chances are also high to not reach the other side at all.

The fact that circular reference occurs when using the new codes as an alias for the old ones furthermore indicates that the change from old to new account structure has been carried out without sufficient analysis of dependencies.

But this is now all history and you're the lucky one that need to deal with the situation. May be the customer at least recognized the consequences and will have a more detailed look the next time he changes such central structures.

Our company has learned from such things in the past and nowadays discusses each change in the account structure with all parties affected before changing anything.

foogy

[Jodha]

Thanks for you reply Foogy

declanr as usual you are the first one to reply my querries thanks for you response . I will try to implement your two cube structure idea i think this one fits to my req.

sorry for late response my customer planned to revisit the req so i was stuck on that .