Implementing accumulation D-link functionality in TM1

[jbcraigs]

Can someone advise as to what would be the best way of achieving the below functionality in TM1 through rules?

I have a source cube which has column using Accounts as picklist. So users can pick the account for each row from dropdown and then type in expense for that account in corresponding month. Users can pick same account in multiple rows and add corresponding expense values.

In the target cube that account list is one of the dimensions and obviously there is just one row for each account.

In Enterprise Planning I can use accumulation d-link for such a thing but in TM1 how do I accumulate the expense values from multiple rows in source cube for the same account and put the data in the corresponding row in Target cube?

Thanks.

[Martin Ryan]

While I wouldn't go so far as to say that this is impossible, this would not be straightforward in TM1 and trying to explain to users how to use it would be more difficult than explaining an alternative approach.

You're describing what I would define as a transactional approach. Inputting a starting point, then adding or subtracting numbers subsequently. While you could do this in TM1, that's not what it's designed for. It's designed for having a single number at a single point. Any changes can be checked out via the transaction log.

If you want to record journal entries then you need a GL system. If you want to report the numbers from your GL system, or budget for them, that's where TM1 is useful.

(But... if you insist on doing this in TM1 you'll need an extra dimension that just has 1 to 1000. Users input the first number against "1", the next at "2" and so on. The summary cube would sum up all the individual transactions via a consolidation. That's how I'd do it anyway. But it would look pretty ugly to the users and would require them to know what's going on.)

Martin

[jbcraigs]

Thanks Martin.
I agree with you that this is a Transactional approach and TM1 is not built for this. But client requirements rule.

Since TM1 is being promoted as an Cognos EP replacement, I would expect it to at least be able to do some basic functions that are available in EP like an Accumulation D-Link.


You're right. We're building the input cube just the way you described it but we will just have 1-50 rows in there and the user will pick an account for each row and same account might repeat across rows. That wasn't the question.

My question was how to accumulate the values for rows with same account and transfer it to another cube which has Account list as dimension. So this target cube will just receive one consolidated value for each account.

Thanks again.

[Martin Ryan]

Let's call your 1-50 dimension the "Transaction" dimension. It will need to include a consolidation called "All transactions" that has 1-50 as the children. The rule for this detailed cube will be along the lines of

Code: Select all

SkipCheck;
Feeders;
['All transactions'] => DB('SummaryCube', !Account, !Month, !Year, !Department, 'Value');

The summary cube's rule will be something like

Code: Select all

SkipCheck;
['Value'] = N: DB('DetailCube', !Account, !Month, !Year, !Department, 'All Transactions', 'Value');

I would expect it to at least be able to do some basic functions that are available in EP like an Accumulation D-Link.

What is basic depends on the architecture of the system. E.g. it is basic in TM1 to write a different rule for Forecast and Actuals for a given account, but (as I understand it) that is not possible in EP.

EP does have some great features and I don't think anyone's pretending that you'll be able to pick up an EP model and dump it straight into TM1. They are different tools, so are going to have different functions, strengths and weaknesses. If your client is expecting the two tools to be the same they're going to be simultaneously disappointed and delighted by the differences.

Martin

[tomok]

Why do you need to "accumulate" the data and then post it to an account in another cube? I have clients ask for the ability to plan at a level lower than GL account all the time. All you have to do is add another dimension like Martin suggested, call it "Input_Line", and have a top level node like "All_Input" and then detail lines 1 thru 50 or whatever. There really is no need to move the total of the input somewhere else unless you just want to. Reports can just pull from the element "All_Input" if you want the summarized transactions.

[AbhijeetChalke]

Hello,

Implementing the accumulation D-link funtionality in TM1 is possible
Please find attached document of Implementing accumulation D-link in TM1 and follow the steps .

[rambo]

You can now achieve this WITHOUT an item dimension in the summary cube. Performance Modeler will let you do this. It creates a few control cubes in the background (behind even control objects) to achieve this. Very useful stuff. I have attached couple of screenshots showing this functionality.

D-Link.png (32.33 KiB) Viewed 7141 times

Cubes.png (23.03 KiB) Viewed 7141 times

[mvaspal]

Hi

I would be very cautious with this built-in functionality, especially when you are using virtual dimensions in the source cube. You should always check the generated code and feeders and then decide whether you keep it or go rather the self-developed TI way.