Currency conversion with a currency cube

[marine]

Hi!

I am new to TM1, and I am currently developing my very first TM1 model. I hope I can explain my problem clearly enough!

More specifically, I am building a multi-company group budgeting application, and I have imported test data from a couple of subsidiaries. I have six different currencies, all of which should be converted to the reporting currency and to be summarized on the group level in this one currency. The data is imported by the subsidiaries in their local currency and the reporting currency is also local currency for some of the companies. There is only one currency/market(geographical area) but one market can have different subsidiaries, all using the same local currency though. The initial idea was to have separate data import processes for all the companies, and to import all the data (through a mapping cube) to one group Income Statement cube. So far I have imported the data in the following way: I have mapped the data column of a fact file (CSV) to a corresponding local currency in the 'Currency' dimension, which is the measure dimension of the cube. For all this I have used Performance Modeler.


Income Statement cube:

Rows: 'Accounts'
Columns: 'Time', 'Currency'
Context: 'Area', 'Functions', 'Company'...

I also have a currency cube that I have attached a view from to this post as a picture.


So in the Income Statement cube, I have one dimension with all the currencies, and at the moment I can see the data imported only by market areas when the right local currency is chosen from the 'Currency' dimension. However, I'd still need the local data to be summarized on the group level in Euros (the group reporting currency).


So I am asking more experienced developers, what would be the smartest way to proceed in this case? I've thought about adding a 'Reporting EUR' element to the 'Currency' dimension and writing rules to the Income Statement cube, but I haven't gotten the DB-rules work.

Any tips are appreciated, thank you!

[Wim Gielis]

Hello

You could use this technique:

- drop the different currencies / measures
- add a CurrencyType dimension (or rework your current measures diimension), containing 2 elements: 'Local Currency' and 'Reporting Currency'
- add a Text attribute to the Company dimension holding the currency of each company
- add a rule to calculate the Reporting currency, each time applying the correct rate from the lookup cube to the respective value in Local currency.

Wim

[marine]

Hello

You could use this technique:

- drop the different currencies / measures
- add a CurrencyType dimension (or rework your current measures diimension), containing 2 elements: 'Local Currency' and 'Reporting Currency'
- add a Text attribute to the Company dimension holding the currency of each company
- add a rule to calculate the Reporting currency, each time applying the correct rate from the lookup cube to the respective value in Local currency.

Wim

Thank you for your answer, that makes sense. I however realized that the numbers need to be shown also in each local currency, so I created a look-up cube with information on currencies used in each company. Then I wrote rules to Income Statement cube to retrieve the right currency and exchange rates with nested DB functions. Anyway, the conversion works now!

[tomok]

Standard practice, at least from what I've seen and done, is to have a single currency dimension with a currency called "Local" and then all the other currencies you want. All users load their data to Local and then you convert to all the other currencies via a fxRate lookup cube that has average and period end rates for each currency (USD, EUR, GBP, CAD, etc.), for each period in the model. Which currency to look up can be based on an attribute in the company, cost center, or any other organizational dimension, your choice.