Page 1 of 1
Using a Sequential number as a purely arbitary Element Key
Posted: Mon May 10, 2010 4:14 pm
by John Hammond
Hi
We have a situation in a CoA where both the description and the code may change in the future. The problem in TM1 is that you can not change an element without losing the data in the cube (by doing delete then reinsert).
The idea is to replace the code used as an element with a purely arbitary number and demote the code to an alias
Thus
Code: Select all
Inventory
Gross Inventories
Common Stock
100100 Common Stock - Components
100200 Common Stock - Finished Goods
100300 Common Stock - Raw Materials
100400 Common Stock - Spares
Consignment Stock
101100 Consignment Stock - Account
Contract Work in Progress
102100 Contract WIP - Direct Materials
102200 Contract WIP - Direct Labour
Becomes
Code: Select all
IC01 Inventory
IC02 Gross Inventories
IC03 Common Stock
IC04 100100 Common Stock - Components
IC05 100200 Common Stock - Finished Goods
IC06 100300 Common Stock - Raw Materials
IC07 100400 Common Stock - Spares
IC08 Consignment Stock
IC09 101100 Consignment Stock - Account
IC10 Contract Work in Progress
IC11 102100 Contract WIP - Direct Materials
IC12 102200 Contract WIP - Direct Labour
Where IC01 to IC12 are internal codes which have no meaning to anyone else.
Now there are some drawbacks to this because you would have to have the alias switched on all the time to make sure the end user saw something meaningful and TM1 is designed around the idea of a stable key.
This idea is close to the sequence idea in RDBs.
Now my middle name is not Einstein and I am sure that someone else out there might have tried this to get round the problem of not having a stable key.
Did you find:
- The internal codes kept on appearing to users and confusing the issue.
Rules became meaningless if you were to use the internal codes to make the rules always work since using an alias would mean you could not change it without changing the rules and the whole idea of this is to be able to change codes and descriptions without having to worry.
Similarly TIs become very difficult.
Any assistance would be appreciated as always.
Re: Using a Sequential number as a purely arbitary Element K
Posted: Mon May 10, 2010 4:51 pm
by MarioRubbo
Sorry, I have nothing constructive to offer. I just wanted to express my sympathy for your predicament. I find this aspect of TM1 a bit too restrictive.
Re: Using a Sequential number as a purely arbitary Element K
Posted: Mon May 10, 2010 5:16 pm
by John Hammond
MarioRubbo wrote:Sorry, I have nothing constructive to offer. I just wanted to express my sympathy for your predicament. I find this aspect of TM1 a bit too restrictive.
Thanks for your comment Mario
To some degree its the same in rdbms'es in that if you want to change a key value there is a load of work to do so its not just a TM1 thing (though rdbms'es dont just silently remove your data). Clearly some of the blame rests with accounting for not coming up with a code structure that has some longevity.
Re: Using a Sequential number as a purely arbitary Element K
Posted: Mon May 10, 2010 10:23 pm
by Steve Rowe
Hi John,
I'm not really getting how the second approach helps with a changing chart of account structure.
In my experience there is no such thing as a stable / static chart of accounts and TM1 has dealt with this well. I'm not knocking your approach I just don't understand what the structure you propose gives you over simple adding new accounts and consolidations.
With your account code 100100 if it is replaced by account 100110 then why not just create a new account and keep 100100 in the same piece of structure?
It's my week for being confused!
Cheers,
Re: Using a Sequential number as a purely arbitary Element K
Posted: Tue May 11, 2010 7:15 am
by John Hammond
Hi Steve
Not your week for being confused - mf for not explaining it clearly
Our accounts people want to go from this:
Code: Select all
IC01 Inventory
IC02 Gross Inventories
IC03 Common Stock
IC04 100100 Common Stock - Components
IC05 100200 Common Stock - Finished Goods
IC06 100300 Common Stock - Raw Materials
IC07 100400 Common Stock - Spares
IC08 Consignment Stock
IC09 101100 Consignment Stock - Account
IC10 Contract Work in Progress
IC11 102100 Contract WIP - Direct Materials
IC12 102200 Contract WIP - Direct Labour
to this
Code: Select all
IC01 200000 Inventory
IC02 210000 Gross Inventories
IC03 211000 Common Stock
IC04 211100 Common Stock - Components
IC05 211200 Common Stock - Finished Goods
IC06 211300 Common Stock - Raw Materials
IC07 211400 Common Stock - Spares
IC08 212000 Consignment Stock
IC09 212100 Consignment Stock - Account
IC10 220000 Contract Work in Progress
IC11 220100 Contract WIP - Direct Materials
IC12 220200 Contract WIP - Direct Labour
Whereby the accounts are renumbered to reflect the hierarchial nature of the CoA.
With the IC dummy codes this is easy enough to do, but if they are element keys then you end up having to unload and reload all cubes referenced by that dimension.
It is not a problem with the consolidations which can be deleted and readded but the problem is with the N level elements that trigger deletions.
For example the renaming of 102100 as 220100 cannot be done unless you delete and readd.
Re: Using a Sequential number as a purely arbitary Element K
Posted: Tue May 11, 2010 7:40 am
by Steve Rowe
Morning John,
IMO if the client/users did this then best approach would be to
1. Have both dimension structures in the account dimension.
2. TI the data from the old code structure to the new code stucture. It should not be too hard provided you have the mapping, which I think you would need with your approach anyway.
3. Reconcile.
4. Delete the original structure.
IMO you would build in long term design issue into the system to cope with a one-off / infrequent event. It's a challenge to strike a balance between maintainability and user-friendlyness sometimes and I think this would really reduce the user-friendlyness too far.
Cheers
Re: Using a Sequential number as a purely arbitary Element K
Posted: Tue May 11, 2010 7:47 am
by olapuser
interesting, but still do not understand what you want. just guessing add level in rdbms, and re update the dimension without the need to unload / reload the cube.
Code: Select all
IC01 Inventory        3
IC02  Gross Inventories      2
IC03    Common Stock    1
IC04Â Â Â Â Â Â 100100Â Â Common Stock - Components 0
IC05Â Â Â Â Â Â 100200Â Â Common Stock - Finished Goods 0
IC06Â Â Â Â Â Â 100300Â Â Common Stock - Raw Materials 0
IC07Â Â Â Â Â Â 100400Â Â Common Stock - Spares 0
IC08    Consignment Stock    1
IC09Â Â Â Â Â Â 101100Â Â Consignment Stock - Account 0
IC10    Contract Work in Progress    1
IC11Â Â Â Â Â Â 102100Â Â Contract WIP - Direct Materials 0
IC12Â Â Â Â Â Â 102200Â Â Contract WIP - Direct Labour 0