Allow user to enter data in one currency

Question

I have created four TM1 cubes: Rate for hour, Hours, Rate of exchange and Revenue.

1. In first one, user enters rates(costs) in different currencies.
2. In second one, user enters customer hours (for example, how much time customer consultation took).
3. In third, user enters rate of exchange for every currency.
4. In Revenue, based on data in previous cubes, I calculate all revenue in Euros.

The problem is when user enters same rate in more than one currency. Then revenue in Revenue cube is bigger than it should be.

My question: is there a way to prevent users from entering rates in more than one currency? All approaches I tried ends up with circular reference error.

Answer 1

Your question is almost impossible to answer in specific terms because you've provided no specific details of your cubes, dimensions, elements or rules.

In general terms, however... TM1 is not a relational database and other than picklists has few input restrictions. There are usually at least a couple of ways that you can work around that, though. In this case I assume (again, in the absence of specifics) that the relevant dimension in the first cube has an input element for each currency.

Instead of that you could have two input elements; one for the amount, and another for the currency code (regulated by picklist). Your rule in the Revenue cube then evaluates the relevant currency element by looking at the currency code input. That will allow it to look up the relevant exchange rate from the third cube via a DB() function. That rate is multiplied by the work rate that has been entered into the first cube and the hours entered into the second cube to calculate the revenue.