Measures to be revalued for change in currency rates

Hi,

I am looking for expert inputs on designing a new fact table withing our existing DW architecture.

Currently, we have a holdings fact table which is loaded incrementally each month (around 1M records monthly). The reporting on this table is ITD (Inception till date). On this table, we have measures stored in local curency(which could be AUD, CAD, GBP....etc) and we have separate column for measures calculated in USD (corporate currency). The rates that are used to calculate these corporate values are as of when the records were loaded.(i.e. month end rate as of the month when monthly load was done).

Now, we have a new requirement that business wants to report the corporate(USD) values which are calculated as per the current month end rate for each month and the same rate should be applied for all the measures in the periods going back.

E.g:

The data in current fact table is like :

Product Local Amount Local currency Corporate(USD) Amount Period
P1 10 AUD 15 JAN'10
P2 20 GBP 40 JAN'10
P3 50 USD 50 JAN'10
P1 40 AUD 100 FEB'10
P4 30 EUR 75 FEB'10
P2 10 GBP 17 MAR'10
P5 60 AUD 66 MAR'10


Currency Rate table:

Local Curency Rate to USD Period
AUD 1.5 JAN'10
GBP 2 JAN'10
EUR 3 JAN'10
AUD 2.5 FEB'10
GBP 1.5 FEB'10
EUR 2.5 FEB'10
AUD 1.1 MAR'10
GBP 1.7 MAR'10
EUR 2.8 MAR'10


The new fact table should have values like the one below:

Product Local Amount Local currency Corporate(USD) Amount Period
P1 10 AUD 15 JAN'10
P2 20 GBP 40 JAN'10
P3 50 USD 50 JAN'10
P1 50 AUD 125 FEB'10
P2 20 GBP 30 FEB'10
P3 50 USD 50 FEB'10
P4 30 EUR 75 FEB'10
P1 50 AUD 55 MAR'10
P2 30 GBP 51 MAR'10
P3 50 USD 50 MAR'10
P4 30 EUR 84 MAR'10
P5 60 AUD 66 MAR'10

If you see in the above table, the values ITD values are calculated each month based on current rates applied to all the periods prior to it.

As you can see, the no. of records in the new proposed structure are increasing exponentially every month. As the currency rate fluctuates every month, we cant even think about using some SCD Type 2 kind of logic only for changed records, as practically every currency rate fluctuates by small difference and we will end up recalculating all the records every month.

Couple of other options that we thought were:

1. Calculating the new USD values at runtime in reporting layer using currency rate table join with the existing fact table. This has a disadvantage of slowing down the user query execution time as calculations are done on the fly.

2. Creating materialized views which store pre-calculated data. This option is still in research phase and we are evaluating this further.


We are more looking forward for a solution which will have data precomputed at DB level itself. Any inputs are welcome.

Maintain historical rates rather than historical facts. Use the rates dynamically when restating values in a query.

ngalemmo wrote:Maintain historical rates rather than historical facts. Use the rates dynamically when restating values in a query.

We absolutely thought about it as a part of our design, but the drawback is the calculation is done during query execution which delay the output results. Also, if we have the results stored in an efficient manner in DB itself as pre-calculated values, then any other process(may be any downstream application) which would need this data can directly fetch it from here instead of 2 different processes doing the same calculations twice.

Hence, we were looking for an efficient way of storing this data at table level itself.

Having a extremely large fact table will also impact performance.

Have you actually tried doing the conversion in a query against an optimized physical model? My personal experience has been that it is no big deal (Oracle, Netezza, SQL Server) when properly done (physical implementation details) for the particular platform.

ngalemmo wrote:Having a extremely large fact table will also impact performance.

Have you actually tried doing the conversion in a query against an optimized physical model? My personal experience has been that it is no big deal (Oracle, Netezza, SQL Server) when properly done (physical implementation details) for the particular platform.

I appreciate your quick response. Thanks for responding so quickly.

We will definitely settle down to one of such approaches once we evaluate all the options. At this moment, we are trying to evaluate the implementation options that are followed in the industry for such business cases. Are there any best practices/standards we can refer to? Any links?

No links I can give you, although I think Kimball does mention it in Toolkit somewhere. I'm also an eternal skeptic. Every consultant I've ever met follows 'best practice', and they all do it different ways. And standards are great once you settle on which one to follow.

If you are using SSAS as an OLAP tool, you may find the following link from one of my colleagues useful:

http://www.bp-msbi.com/2010/10/a-guide-to-currency-conversions-in-ssas/

pjammi ,

Do you think that solution that you willing to take is sustainable over the long period of time ??
The calculation that you are talking about is not a very big and complex calculation , it is simply a multipication for each row.

It has to be done at repoting layer because.

1) You might not need all the fact data in most of the requirments.
2) In case you need old data or currency converted data to the date when transacion happened (past).
3) You can think about cube creation with the old transaction values as well as today's currentcy converted values.

The solution that you are taking is like a train track, you won't be able to do or meet any additional requirements and doing it via maintainng a seperate table for old/lastest currency is like a bus route .. you can move/turn as per your wish.

Vishy,

Thanks for inputs but I don't think that I really followed your advice.

Are you trying to say:

1. Create the join between the existing fact table, date dimension and the currency rate fact table at the reporting layer and then get the expected results by Cartesian product between them.

OR

2. Create a separate new fact table and store the data as I have shown in my exhibit table no. 2 above.

OR

3. That I completely miss understood your response. If this is the case, can you please elaborate your idea?

Thanks.

You almost got it right , but now I am a bit confused when you wrote "Cartesian Product" .

By the way what i meant was

1) Currency table -- Type 1 table which will have only latest conversion rates.
2) Fact table -- Having transaction in same currecy in which transaction happened.
3) Time dim -- Having time key.

--

One more thing that you can do is at the time of fact population you can also put transaction values in USD using "that day" currency conversion rates.

that will help you get "that day" conversion directly from fact and for any other thing you can use currency table.

If I understand your problem correctly, here's what Kimball does in his book, and I believe it should work perfectly in your case:

Periodic snapshot fact table

DateKey
SourceCurrencyKey
DestinationCurrencyKey
SourceToDestinationRate
DestinationToSourceRate

This is a pretty common problem in financial services. Restating things with the correct fx rate (fxr) to convert from local to base (reporting currency) ie GBP to USD. There is usually an fx rate table with from currency to currency and date with the appropriate fxr and you join to that to get the rate and do the calculation. I'm usually of the opinion to grab the fx rate on load and do the calculation and store it in the fact table. (note for security positions the fx rate is specific to the transaction and won't change). I also usually store the fx rate in there as well so that there's no question as to what was used. Don't want some trader going postal over P&L whose fx he/she doesn't believe is right. The rate used is in the fact table and easy to double check.

I'm not sure if I understood correctly- that people want the historical data restated in USD using today's rate not the rate in effect on that date? What use case is this supporting? I can't think of one. If I have a local value of 100 GBP and today the value of the dollar is 2 dollars for per pound then the value is $200. If three months ago it was 1.5 dollars per pound then the value at that time was $150.

Larry,

Thanks for your response. We are already doing what you stated i.e. converting original to USD as of the rate when the transaction was received.

But for some balance sheet reporting, our users want to see all the original(ITD) values revalued to USD as of current month rate. Going forward, they want to see similar calculations done for every month. The current table we have is already being used for income statement reporting which uses exchange rate as of when transaction was received. But in this new requirement, they want to re-evaluate all the original values to current USD values as of current month rates.

A hypothetical use case of this I might think is: If there are no transactions in a given month, business users might want to see how much the total balance sheet figures are affected because of FX rate change. Hope this example helps.

Hi,

How many rows are there in a month if it is not many then generating it on the run is a better approach also if the user want the currecny conversion at a frequency which can change any time then generating on the fly is better.

if you know that the user are always going to ask the month end currency rates then one approach will be to create a MV and on each month generate the data (need to have a limit that you will generate for a certain period of time only otherwise your MV will have a exponential large number of rows every month).


thanks

The no. of records inserted each month are about 500k records. The BIG thing is till date the existing fact tables has about 70 million records. In order to revaluate and store all these no.s each month, it would definitely be an exponential growth. The option of MV was thought about but becuase of the exponential growth in underlying data, we forsee issues with MV refresh performance and all... hence, not really an option in our case... Any other suggestions are welcome...

Data growth example:

First month:

Current fact record count: 70 million

Hence records revaluated for first month would be : 70 million reprocesses records with the current month end rate

Second Month : 500k new records received

Current fact record count : 70 million + 500k

Records in the new structure just for second month: 70 million + 500k

Total no. of records in new structure at end of second month: 70 million ( first month) + (70 million + 50 k) second month

I really don't understand why this thread has gone on for as long as it has.

Best practice is to simply calculate the appropriate converted value based on an exchange rate table containing whatever rates for whatever time periods. Small rate table. No exponential growth. Why do you feel the need to calculate such values in advance?

Hi,

the numbers are pretty big. Is the frequency of this process going to be once a month or it can become daily/weekly ?
Once you have the new rates calculated are you going to use the old data/previous months's rate?
Did you try to partition's on the table ?

thanks

Himanshu

If the number of original rows is an issue, and if the 'pre-calculated restatement' is an aggregate, then simply aggregate the original data and use that as the source for calculating the restated values. Do not store the restated values, calculate them on the fly. There is no significant difference in query time.

And, if you do this very frequently, just have the DBA's set it so the currency conversion rate table is in memory. It won't be very big.