Database Reference
In-Depth Information
For example, end users could see how many transactions were originally made in
Euros and how many in US dollars. However, the aggregated values of transactions
originally made in those currencies are always shown in US dollars, perhaps because
the company's headquarters are in New York.
Doing the currency conversion in the ETL phase does not remove the need for an
analysis of what the right exchange rate to apply is. For some businesses, the date
of the payment is more relevant than the date of the transaction. In other cases,
a monthly average of exchange rates would be more accurate because the real
currency conversion is not made transaction by transaction, but takes place
separately in bulk conversion operations. Henceforth, we will assume that an
analysis of the right exchange rate to apply has already been done, for whatever
scenario we are going to face.
Thus, there are at least three cases where we suggest implementing currency
conversion directly in an Analysis Services cube. The first case is when the
requirement is to display aggregated data in several different currencies to the end
user, regardless of the original currency that the transaction was made in. If the
requirement is to display values in a small number of currencies, for example, just
Euros or US Dollars, the conversion can be handled in the ETL phase, but if end user
potentially wants to see values converted to every currency in the world, it is not
going to be feasible to do the conversion in the ETL.
The second case is when the user wants to modify the exchange rate values stored in
the cube using Writeback, which means we cannot know what the rates we need to
use are going to be at ETL time.
The third case is when we are not able to implement conversion in the ETL phase.
In reality, this third case is more a workaround than a good pattern. For example,
we might not have access to the ETL code, or there might be time constraints on ETL
development, or we simply might not have any ETL at all, for example, if the cube
was built directly on an OLTP database (definitely not something we suggest doing!).
For these reasons, we will describe how to implement all of the scenarios we described
in Analysis Services, but we will focus mainly on the case where the end user wants to
display aggregated data and choose the reference currency at query time.
The existence of a feature does not mean that you should use it.
Before performing currency conversion inside Analysis Services,
check if your requirements can be met by implementing conversion
during your ETL phase instead.
 
Search WWH ::




Custom Search