How to populate same leaf level data at consolidation

[pmathur]

Hi All,

We have a requirement, according to attached scenario :

untitled.JPG (75.64 KiB) Viewed 5272 times

For every consolidated account number, for instance, just say that we dont want the aggregation of the leaf level elements , inspite we just want that every consolidated node have the same value as the leaf node have.

Just for example : For Current DSC if we take account number -> "AC_307099001038" then every of its subsequent consolidated account should with hold the same value i.e. "1.9738". Every Account(at leaf) for each Ess_Entity dimension will hold the same data i.e. "1.9738"

We are feeding the leaf level data using TI.

Any suggestions are welcomed.


Regards
Priyank.

[Christopher Kernahan]

Hi,

I don't quite get why you'd want this - are you saying the up the entire chain of the hierarchy, from 'AC_307099001308' through 'AC_307' up to 'AC_A000000' and then 'Accounts', you want the value has to be '1.9738'?

Why? When you get to 'AC_A0000200' which value does it take on, 'AC_307099001305' or 'AC_442101001000'?

[Christopher Kernahan]

Is current DSC a Discount rate for each account? If it is, and it is the same for all accounts, why not put it in a separate Info cube and use a DB() rule to refer to it. If it needs to change by Quarter or Month you can add in a time dimension.

I think this approach would be a better design practice than forcing your hierarchy to be equal the leaf level accounts, especially in the circumstance where all the acccounts are the same value.

[qml]

So you basically just want to designate one N element in each consolidation branch that will consolidate up, whereas all others should not. Putting aside the question whether this should be done at all, this can be easily achieved by setting weights of all the elements that should be ignored in a given consolidation to 0 while keeping the weight for the chosen element as the default 1.

This however, will only block consolidations up to a point where different hierarchy branches meet. So element "Accounts" for example would still be a sum of all the N elements that have a non-zero weight, not just a chosen one. That is, unless of course you use the weight trick on consolidated elements as well, in which case you can always block any branches from consolidating at the level of your choosing.

Also, note that this solution has a big drawback - it's not transparent and intuitive to users. However, if you want to go that way...

[tomok]

Hi,

I don't quite get why you'd want this

Agreed. Very poor design. I would definitely store the discount rates in a "lookup" cube, adding the dimensions that the rate could vary by. However, I would keep the spot in the main cube, for reporting purposes and do the following to make it really beneficial.

1. For leaf elements I would reference in the value from the lookup ['Disc']=N:DB(LookupCube, !Dim1, !Dim2......);
2. Extend out the rate in the reporting cube by multiplying it by whatever it s being used for, probably sales ['Extended Disc']=['Sales']*['Disc'];
3. Calculate a weighted average discount, but only for consolidated nodes ['Disc']=C:[Extended Disc']\['Sales'];

A weighted average discount is going to be a better measure than just duplicating the discount all up and down the tree. If the discount rate for each node is the same then the weighted average discount is going to be the same, resulting in what the OP is trying to accomplish. However, if the discount rate is NOT the same for each node then it will be the weighted average discount at each level of the tree.

[jim wood]

This has been discussed before in the thread:

http://www.tm1forum.com/viewtopic.php?f=3&t=6129

[lotsaram]

This has been discussed before in the thread:

http://www.tm1forum.com/viewtopic.php?f=3&t=6129

Ahh, I was trying to find that, I could also remember something very similar being discussed quite recently.
This might also help http://www.tm1forum.com/viewtopic.php?f=3&t=4048

[pmathur]

Hi all,

Thanks for the replies.. I will check on them and give you an update.


Regards.
Priyank