Many-to-Many Currency Conversions in SQL Server Analysis Services

Abstract

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 dales or multiple exchange rales. If you're using a separate database server, you'll need to move the Sales.bak database backup h'!e to a local or mapped drive where that database server is able to locate backups. * Restore the sample Sales database fronrlhe SQL Server 2008 R2 backup file named Sales .bak. * Open the Currency Conversion solution in Business Intelligence Development Sludio (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 cube will use these dimensions imernully Io perform currency conversions.\n According to BOL, the Scope function "Limits the scope of specified Multidimensional Expressions (MDX) statements to a specified sub cube."

Headnote

How to work around the Business Intelligence Wizard's limitations

Within financial reporting, transaction amounts are typically entered in multiple currencies and these amounts are entered al different times throughout the year. Take, lor 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 d iHeren t date and with a different currency. More than one currency has been entered and. depending on the authence, 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 dales or multiple exchange rales. 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-lomany currency conversions in SSAS is possible. I'll show you how io create a SSAS solution thai returns and reports against multiple values, multiple currencies, and multiple entry dates . -and siili 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 Io 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 from the SQL Server Magazine website. Go to www.sqlmag.com. enter 139797 in the Search box. and click the 139797.zip hotlink. Then, do the following:

* Unzip the Currency Conversion.zip Ole 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 h'!e to a local or mapped drive where that database server is able to locate backups.

* Restore the sample Sales database fronrlhe SQL Server 2008 R2 backup file named Sales .bak.

* Open the Currency Conversion solution in Business Intelligence Development Sludio (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 I : 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 BtDS's Solution Explorer, right-click Dimensions and select New Dimension to bring up the Dimension Wizard.

2. Select Use an existing iahlf. 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 ist, select Currency fSOCode. 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 at msdn.microsoft.com/en-us/library/ msI75452(v=SQL.105).aspx.

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

Stage 4: Add Exchange Rate Dimensions

To be able to use the current dimensions without mapping them to an exchange rale, you need to add new exchange rale dimensions. The cube will use these dimensions imernully Io 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! 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.

Stage 5: Set Up Dimension Usage

After adding the four exchange rale dimensions, you need to map them to the Exchange Rale 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. Sel the Granularity attribute to Date Key.

4. In the Dimension Columns entry, make sure DaieKey from the dimension specified in step I 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 I 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 ExchangeDale and click OK.

11. Repeat steps I through IU to map the Exchange Payment Date dimension. In step I. replace "Exchange Transaction Dale" with "Exchange Payment Date." In step 5. replace "Transaction Date" with "Payment Dale."

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 Currency Key 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 Rale measure group and the dimension specified in step I intersect.

7. Select Regular as the relationship type.

8. Set the Granularity ¿illribute Io Currency Key.

9. In the Dimension Columns entry, make sure Currency Key from the dimension specified in step I 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 I, 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, SaIeID. in the Dimension Usage tab. This is a degenerate dimension you can use for validating the data when browsing.

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 ad the MDX code below the CALCULATE command. Your Calculations lab should look like that in Web Figure 1 (www.sqlmag.com. InstanlDoc ID 139797).

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 ?1 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:

* Your cube needs io include a ime dimension that's defined as type Time. You've already met this prerequisite by adding the Exchange Date dimension in stage 3.

* Your cube needs to include a currency dimension that's defined as type Currency and whose Visible properly is set to True. You've already met this prerequisite by adding the Exchange Currency dimension in stage 3.

* Your measure group needs to include an exchange rate measure. You've already met this prerequisite by adding the Exchange Rate measure group in stage 3.

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

1. Righi-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 rale 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 I ?? USD to currency 2).

6. Specify how you entered the exchange rates in the database. The exchange rate used in the Sales database is ? Currency per I USD. To indicate this, begin by choosing EURO as the sample currency. In response, the wizard displays two options: n EURO per I US Dollar and n US Dollar per I EURO. Select n KURO per i US Dollar, and click Next. (The wizard uses the sample currency you select only Io 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 rales, 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 arc 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 lew changes have occurred in the data source view. If you open the data source view, youll 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. ?G you rightclick 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 I O, you'll now see a red squiggly line under Currency Key. If you hover over this attribute, you'll see the error message The KeyColwmix have Null PHH -easing sel i? Unknown M ember. To remove this message, set the Un known M em be r 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 KeyDuplicale property to ReportAndStop. At this point. Currency Key will still have a red squiggly line because the NullProcessing property of the KeyC'olumns is still set to UnknownMember. So. in the Currency Key properly pane, drill down to KcyColumns, 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 il, and select the Calculations tab. Click lhe Script View icon. Notice that the MDX code references one currency and one dale. 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-lhe-box generated M DX, you won't be able lo 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 properly 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 iransaction 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 paymeni date and paymcni currency.

To add the new MDX code, go to the Calculations lab 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 fund ion 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 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 L7SD to the reporting currency. Once again, this conversion uses the Exchange Payment Date and Exchange Payment Currency dimensions.

Listing 2 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 5.

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 íeave ií as is, the users in the finance department will love it, EHl

InsìantDoc ID 139797

Sidebar

MORE on the WEB

See the web figure at InstantDoc ID 139797.