Many-To-Many Currency Conversions in Microsoft's SQL Server Analysis Services

Within financial reporting, transaction amounts are typically entered in multiple currencies and these amounts are entered at different times throughout the year. Take, for example, a sale that has a transaction date and transaction currency with one date and one currency, but payment was made on that sale on a different date and with a different currency. More than one currency has been entered and, depending on the audience, many currencies might need to be returned. This is a case of many-to-many currency conversions.

Using the Business Intelligence Wizard in SQL Server Analysis Services (SSAS), you can perform currency conversions, but you unfortunately aren't able to enter multiple dates or multiple exchange rates. Furthermore, if you map the exchange dates and currencies to the transaction and payment dates and currencies, you'll likely have problems when trying to use those dates and currencies in aggregations within your reports.

Despite these problems, performing many-to-many currency conversions in SSAS is possible. I'll show you how to create a SSAS solution that returns and reports against multiple values, multiple currencies, and multiple entry dates-and still use those dates and currencies in your cube for aggregations. The seven-stage process to set up currency conversion in a multidimensional database and cube is as follows:

To demonstrate this process, I'll be using a sample solution named Currency Conversion, which I designed in Visual Studio 2008. You can download this solution by clicking the downloads link at the top of this page. Then, do the following:

•  Unzip the Currency Conversion.zip file and copy the contents to your Visual Studio projects folder. If you're using a separate database server, you'll need to move the Sales.bak database backup file to a local or mapped drive where that database server is able to locate backups.
• Restore the sample Sales database from the SQL Server 2008 R2 backup file named Sales.bak.
• Open the Currency Conversion solution in Business Intelligence Development Studio (BIDS) and change the deployment server name to the name of your SSAS server.
• Open the data source in the solution and change the server name to name of the server on which you restored the Sales database.

The Currency Conversion solution is now complete and ready to experiment with. However, if you want to follow along and create your own solution as I explain the seven stages in detail, you need to open the cbSales cube in BIDS and go to the Cube Structure tab in the cube designer. Make the cube's Measures and Dimensions panes look like those in Figure 1. You can simply delete the additional measures and dimensions.

 

Stage 1: Add a Currency Dimension to the Database

The first task is to add a currency dimension to the Sales database. To add this dimension, follow these steps:

1. In BIDS's Solution Explorer, right-click Dimensions and select New Dimension to bring up the Dimension Wizard.

2. Select Use an existing table, and click Next.

3. Select the data source view dsvSales in the Data source view list, then select your currency dimension from the Main table drop-down list. In this solution, it's named DimCurrency. Click Next.

4. In the Key columns list, select CurrencyKey. In the Name column list, select CurrencyISOCode. Click Next.

5. In the dimension attributes, change the CurrencyKey attribute's type from Regular to CurrencyISOCode.

6. Rename the dimension from DimCurrency to Currency, and click Finish.

If you open the Currency dimension in Dimension Designer, you'll see that the Type property is set to Currency. Setting the dimension type to Currency is necessary for the Business Intelligence Wizard to successfully determine which dimension and fact table to use. For information about the available types, see the "Dimension Types" web page.

Stage 2: Add Currency Dimensions to the Cube

Next, you need to add currency dimensions to the cube. To do so, go to the Cube Structure tab, right-click the Dimensions pane within your cube, and select Add Cube Dimension. In the dialog box that appears, select Currency and click OK. At this point, you should see that two dimensions have been added: Payment Currency and Transaction Currency.

Stage 3: Add the Exchange Rate Measure Group

The Exchange Rate measure group holds all the exchange rates used for converting all other measures within the cube. To add this measure group, follow these steps:

1. On the Cube Structure tab in the cube designer, add a measure group to your cube by right-clicking the Measures pane and selecting New Measure Group.

2. Select your exchange rate fact table and click Add. In this example, it's named FactExchangeRate.

3. Rename your new measure group to Exchange Rate.

4. Right-click your new measure group, select Properties, and change the Type property to ExchangeRate.

5. Notice that two dimensions have added to the Dimensions pane: Currency and Date. Rename these dimensions to Exchange Currency and Exchange Date, respectively.

Steps 4 and 5 are necessary so that the date and currency dimensions can still be used for reporting.

At this point, your cube's Measures and Dimensions panes should look like those in Figure 2. The blue squiggly lines under the Sale and Exchange Rate measure groups indicate that they don't share any dimensions. (This will be addressed in stage 5.)