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:

1. Add a currency dimension to the database.

2. Add currency dimensions to the cube.

3. Add the Exchange Rate measure group.

4. Add exchange rate dimensions.

5. Set up dimension usage.

6. Add a reporting currency dimension.

7. Add the new MDX code.

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:

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.

Figure 1: Starting point for the cbSales cube

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.)

Figure 2: The cbSales cube after the currency dimensions and exchange rate measure group

Stage 4: Add Exchange Rate Dimensions

To be able to use the current dimensions without mapping them to an exchange rate, you need to add new exchange rate dimensions. The cube will use these dimensions internally to perform currency conversions. For this example, two new exchange date dimensions (Exchange Transaction Date and Exchange Payment Date) and two new exchange currency dimensions (Exchange Transaction Currency and Exchange Payment Currency) are necessary.

To add the new exchange date dimensions, follow these steps:

1. Right-click the Cube Dimension pane in the bottom left corner of the Cube Structure tab and select Add Cube Dimension.

2. Select the Date dimension and click OK.

3. Rename the new dimension to Exchange Transaction Date.

4. Right-click the new dimension, select Properties, and set the Visible property to False. This configuration is made so that users don't have the ability to use this dimension.

5. Repeat steps 1 through 4 to create the Exchange Payment Date dimension, replacing "Exchange Transaction Date" with "Exchange Payment Date" in step 3.

To add the new exchange currency dimensions, follow these steps:

1. Right-click the Cube Dimension pane and select Add Cube Dimension.

2. Select the Currency dimension and click OK.

3. Rename the new dimension to Exchange Transaction Currency.

4. Right-click the new dimension, select Properties, and set the Visible property to False.

5. Repeat steps 1 through 4 to create the Exchange Payment Currency dimension, replacing "Exchange Transaction Currency" with "Exchange Payment Currency" in step 3.

At this point, your cube should look like the one in Figure 3.

Figure 3: The cbSales cube after the exchange rate dimensions have been added

Stage 5: Set Up Dimension Usage

After adding the four exchange rate dimensions, you need to map them to the Exchange Rate and Sale measure groups using the cube designer's Dimensions Usage tab. To map the exchange date dimensions, follow these steps:

1. Click the ellipsis (…) button where the Sale measure group and the Exchange Transaction Date dimension intersect.

2. Select Regular as the relationship type.

3. Set the Granularity attribute to Date Key.

4. In the Dimension Columns entry, make sure DateKey from the dimension specified in step 1 has been selected.

5. In the Measure Group Columns entry, select Transaction Date and click OK.

6. Click the ellipsis button where the Exchange Rate measure group and the dimension specified in step 1 intersect.

7. Select Regular as the relationship type.

8. Set the Granularity attribute to Date Key.

9. In the Dimension Columns entry, make sure DateKey from the dimension specified in step 1 has been selected.

10. In the Measure Group Columns entry, select ExchangeDate and click OK.

11. Repeat steps 1 through 10 to map the Exchange Payment Date dimension. In step 1, replace "Exchange Transaction Date" with "Exchange Payment Date." In step 5, replace "Transaction Date" with "Payment Date."

To map the exchange currency dimensions, follow these steps:

1. Click the ellipsis button where the Sale measure group and the Exchange Transaction Currency dimension intersect.

2. Select Regular as the relationship type.

3. Set the Granularity attribute to Currency Key.

4. In the Dimension Columns entry, make sure CurrencyKey from the dimension specified in step 1 has been selected.

5. In the Measure Group Columns entry, select Transaction Currency and click OK.

6. Click the ellipsis button where the Exchange Rate measure group and the dimension specified in step 1 intersect.

7. Select Regular as the relationship type.

8. Set the Granularity attribute to Currency Key.

9. In the Dimension Columns entry, make sure CurrencyKey from the dimension specified in step 1 has been selected.

10. In the Measure Group Columns entry, select Payment Currency and click OK.

11. Repeat steps 1 through 10 to map the Exchange Payment Currency dimension. In step 1, replace "Exchange Transaction Currency" with "Exchange Payment Currency." In step 5, replace "Transaction Currency" with "Payment Currency."

The Dimension Usage tab should look similar to the one in Figure 4 at this stage. You might have noticed the extra column, SaleID, in the Dimension Usage tab. This is a degenerate dimension you can use for validating the data when browsing.

Figure 4: Dimension Usage tab after the exchange rate dimensions have been mapped

Before going to the next stage, you need to deploy and process your database. Afterward, go to the cube designer's Calculations tab, click the Script View icon, and delete all the MDX code below the CALCULATE command. Your Calculations tab should look like that in Figure 5.

Figure 5: MDX script starting point

Stage 6: Add a Reporting Currency Dimension

To perform conversions, you'll be using a reporting currency dimension, which is basically a named query that includes the currencies that you'd like to use in your reports. The currencies in this named query map to the Currency table in the Sales database. There are two ways to create this named query. You can manually create it in the data source view, or you can use the Business Intelligence Wizard to create it. Most people prefer to use the wizard, so that's the technique I'll cover here.

There are three prerequisites that must be met before you can use the Business Intelligence Wizard to create a named query that performs a currency conversion:

To create the named query for this solution, follow these instructions:

1. Right-click your cube and select Add Business Intelligence (or click the Business Intelligence icon on the Cube Structure tab) to start the Business Intelligence Wizard.

2. On the welcome screen, click Next.

3. Select Define currency conversion and click Next.

4. Select your exchange rate fact table from the list.

5. Specify your pivot currency. This is the currency that all values will be compared against. For this example, choose US Dollar so that all values will be converted to US dollars (USD). If you choose an output currency other than US Dollar in your reports, the value will be converted from USD (i.e., currency 1 to USD to currency 2).

6. Specify how you entered the exchange rates in the database. The exchange rate used in the Sales database is n Currency per 1 USD. To indicate this, begin by choosing EURO as the sample currency. In response, the wizard displays two options: n EURO per 1 US Dollar and n US Dollar per 1 EURO. Select n EURO per 1 US Dollar, and click Next. (The wizard uses the sample currency you select only to display the two options.)

7. Select the measures you'd like to use for the conversions. For this example, select Transaction Amount and Payment Amount from the Sales fact table. On this screen, you have the option of using different exchange rates, if necessary. For example, you could use two exchange rates: Daily Rate and End Of Day Rate. For this example, select Daily Rate for both measures. Click Next.

8. Select Many-to-many as the conversion type, and click Next.

9. Define the local currency in which the Transaction Amount and Payment Amount values are stored. For this example, select the Identifiers in the fact table option because this information is stored in the fact table's currency keys. Click Next.

10. Select all the currencies you would like returned for reporting. One of the options is Unknown Member. Click Next.

11. Review your implementation. If it's fine, click Finish.

What has the wizard done for you? A few changes have occurred in the data source view. If you open the data source view, you'll find the new Reporting Currency Named Query listed. If you right-click it and select Edit Named Query, you'll find that a UNION statement has been created. If you right-click Reporting Currency Named Query and select Explore Data, you'll find that your new local currency is being used for returning original values.

You'll also find that changes have been made to your database and cube. Both will include a new dimension named Reporting Currency.

In Solution Explorer, navigate to Dimensions and open the Reporting Currency dimension in your database. If you didn't select Unknown Member in step 10, you'll now see a red squiggly line under Currency Key. If you hover over this attribute, you'll see the error message The KeyColumns have NullProcessing set to UnknownMember. To remove this message, set the UnknownMember property of your dimension to None. A blue squiggly line will appear under the Reporting Currency dimension. This squiggly line was added to warn you to not ignore Key Duplicate errors. You can remove it by setting the ErrorConfiguration property to Custom and the KeyDuplicate property to ReportAndStop. At this point, Currency Key will still have a red squiggly line because the NullProcessing property of the KeyColumns is still set to UnknownMember. So, in the Currency Key property pane, drill down to KeyColumns, Reporting Currency.CurrencyKey and locate the NullProcessing property. Set it to Preserve. This will preserve NULL values. The red squiggly line under Currency Key should be gone now.

Right-click your cube to open it, and select the Calculations tab. Click the Script View icon. Notice that the MDX code references one currency and one date. The purpose of setting up the custom dimension usage in stage 5 was so that you could use different exchange rates for different entered amounts. If you just use this out-of-the-box generated MDX, you won't be able to use the dimensions you set up specifically for multiple conversions.

You can either edit this code or manually add new code. Let's look at how to manually add it. Before you add this code, though, you should hide the Exchange Currency dimension created in stage 3. You do this by setting its Visible property to false. This dimension isn't really needed for reporting. It was left visible so that the Business Intelligence Wizard would be able to create the Reporting Currency Named Query.

Stage 7: Add the New MDX Code

At this point, you've created the foundation to convert all specified values. The only thing left is to tell your cube how to work with the new objects. To better understand what the code needs to do, it helps to answer the question, "What do I want to do in my reports?" In this case, you want the ability to convert all transaction amounts by the exchange rate returned for a specific transaction date and transaction currency. You also want the ability to convert all payment amounts by the exchange rate returned for a specific payment date and payment currency.

To add the new MDX code, go to the Calculations tab and click the Script View icon. Remove all the MDX code below the CALCULATE; statement. (Don't remove the semicolon immediately after CALCULATE.)

Remember that the Report Currency Named Query uses the Transaction Amount and Payment Amount measures. So, you first need to add code that uses the Scope function to perform the Transaction Amount conversion. According to BOL, the Scope function "Limits the scope of specified Multidimensional Expressions (MDX) statements to a specified sub cube." In layman's terms, the Scope function applies a scope to a specific measure or member, which is similar to saying "with or when using this piece of data, return the following result." The result is returned using the This statement.

In this case, you need to use three Scope functions for the Transaction Amount conversion because it's a many-to-many conversion:

1. You need to use a Scope function when retrieving the Transaction Amount values.

2. You need to use the Scope function when you're converting the Transaction Amount values to USD. This conversion uses the Exchange Transaction Date and Exchange Transaction Currency dimensions.

3. You need to use the Scope function when you're converting the Transaction Amount values from USD to the reporting currency. Once again, this conversion uses the Exchange Transaction Date and Exchange Transaction Currency dimensions.

Listing 1 (below) shows this code. You need to put it after the CALCULATE; command.

At this point, you need to add the code for the Payment Amount conversion. Like the code for Transaction Amount conversion, the code for the Payment Amount conversion needs three Scope functions:

1. You need to use a Scope function when retrieving the Payment Amount values.

2. You need to use the Scope function when you're converting all Payment Amount values to USD. This conversion uses the Exchange Payment Date and Exchange Payment Currency dimensions.

3. You need to use the Scope function when you're converting all Payment Amount values from USD to the reporting currency. Once again, this conversion uses the Exchange Payment Date and Exchange Payment Currency dimensions.

Listing 2 (below) shows this code. You need to put it after the code you added for the Transaction Amount conversion.

The MDX script is complete, so you can now process and deploy your database. Afterward, reconnect to your cube and go to the Browser tab. Query your cube using the Transaction Amount and Payment Amount measures, then change the Reporting Currency dimension in your parameter list. Both measures will change. Drag Payment Date and Transaction Date onto your report. Notice that you can use them as dimensions, irrelevant of the fact that they have been mapped against the exchange rate. You can also use the currencies entered for both amounts. You should be able to create output like that shown in Figure 6.

Figure 6: Sample output from the Currency Conversion solution

An Extendable Solution

The Currency Conversion solution is quite useful for financial reporting, as well as for on-the-fly exchange rate conversions. And you can easily extend the solutions' capabilities. For example, you can adapt it to use multiple rates or the latest rates. You can even add flag dimensions so that users can select which rate they want. No matter whether you adapt it or leave it as is, the users in the finance department will love it.

Listing 1: MDX Code That Performs the Transaction Amount Conversions

CALCULATE;// 1. Retrieve the Transaction Amount values.Scope ( { [Measures].[Transaction Amount]} );  // 2. Convert all values to USD using the Exchange Transaction Date  // and Exchange Transaction Currency dimensions.  Scope (Leaves([Exchange Transaction Date]),[Reporting Currency].[USD],Leaves([Exchange Transaction Currency]));This = [Reporting Currency].[Local] / [Measures].[Daily Rate];  End Scope;  // 3. Convert all values from USD to the reporting currency using the  // Exchange Transaction Date and Exchange Transaction Currency dimensions.  Scope (Leaves([Exchange Transaction Date]),Except([Reporting Currency].[Currency Key].[Currency Key].Members,  {[Reporting Currency].[Currency Key].[Currency Key].[USD],  [Reporting Currency].[Currency Key].[Currency Key].[Local]}));This = [Reporting Currency].[Currency Key].[USD] *([Measures].[Daily Rate],  LinkMember([Reporting Currency].[Currency Key].CurrentMember,  [Exchange Transaction Currency].[Currency Key]));  End Scope;End Scope;

Listing 2: MDX Code That Performs the Payment Amount Conversions

// 1. Retrieve the Payment Amount values.Scope ( { [Measures].[Payment Amount]} );  // 2: Convert all values to USD using the Exchange Payment Date  // and Exchange Payment Currency dimensions.  Scope (Leaves([Exchange Payment Date]),[Reporting Currency].[USD],Leaves([Exchange Payment Currency]));  This = [Reporting Currency].[Local] / [Measures].[Daily Rate];  End Scope;  // 3. Convert all values from USD to the reporting currency using the  // Exchange Payment Date and Exchange Payment Currency dimensions.  Scope (Leaves([Exchange Payment Date]),Except([Reporting Currency].[Currency Key].[Currency Key].Members,  {[Reporting Currency].[Currency Key].[Currency Key].[USD],  [Reporting Currency].[Currency Key].[Currency Key].[Local]}));This = [Reporting Currency].[Currency Key].[USD] *([Measures].[Daily Rate],  LinkMember([Reporting Currency].[Currency Key].CurrentMember,  [Exchange Payment Currency].[Currency Key]));  End Scope;End Scope;