Many To Many Currency Conversions in Microsofts 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 arent 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, youll 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. Ill 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. Add currency dimensions to the cube. Add the Exchange Rate measure group. Add exchange rate dimensions. Set up dimension usage. Add a reporting currency dimension. Add the new MDX code. To demonstrate this process, Ill be using a sample solution named Currency Conversion, which I designed in Visual Studio 2. How to create a currency converter using MS Excel 2013 without addons. Many get confused with how to interpret pricing Schema, the following is an example you might see in an exam. I have seen many students come up with 1054. Global Blue official site offers you all the information you need during your shopping trip in Europe. It offers travellers a step by step guide on how to shop tax. NewUser Registration Member Login Recent Topics Forum Rules Forum FAQ User Profile. How To Calculate Currency Conversion Manually Remove' title='How To Calculate Currency Conversion Manually Remove' />Revision History. This document will continue to evolve as existing sections change and new information is added. All updates are logged below, with the most recent. Google Can Now Remove Leaked Medical Records From Search Results. If theres something you dont like about yourself on the web, usually Google isnt going to. This example describes how to use a Java Server Page with JSP tags and HTML with JavaScript and Java programs to call COBOL programs for business processing and. How To Calculate Currency Conversion Manually Remove' title='How To Calculate Currency Conversion Manually Remove' />Target pulled the plug on its massive Canadian expansion less than two years after opening. This is the incredible untold story of how it all went wrong. You can download this solution by clicking the downloads link at the top of this page. Then, do the following  Unzip the Currency Conversion. Visual Studio projects folder. Charm City Devils Doorstep Download Itunes. If youre using a separate database server, youll need to move the Sales. Restore the sample Sales database from the SQL Server 2. R2 backup file named Sales. 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 cb. Sales cube in BIDS and go to the Cube Structure tab in the cube designer. Make the cubes 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 cb. Sales 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 BIDSs Solution Explorer, right click Dimensions and select New Dimension to bring up the Dimension Wizard. Select Use an existing table, and click Next. Select the data source view dsv. Sales in the Data source view list, then select your currency dimension from the Main table drop down list. In this solution, its named Dim. Currency. Click Next. In the Key columns list, select Currency. Key. In the Name column list, select Currency. ISOCode. Click Next. In the dimension attributes, change the Currency. Key attributes type from Regular to Currency. ISOCode. 6. Rename the dimension from Dim. Currency to Currency, and click Finish. If you open the Currency dimension in Dimension Designer, youll 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. Select your exchange rate fact table and click Add. In this example, its named Fact. Exchange. Rate. 3. Rename your new measure group to Exchange Rate. Right click your new measure group, select Properties, and change the Type property to Exchange. Rate. 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 cubes 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 dont share any dimensions. This will be addressed in stage 5. Figure 2 The cb. Sales 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. Select the Date dimension and click OK. Rename the new dimension to Exchange Transaction Date. Right click the new dimension, select Properties, and set the Visible property to False. This configuration is made so that users dont have the ability to use this dimension. 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. Select the Currency dimension and click OK. Rename the new dimension to Exchange Transaction Currency. Right click the new dimension, select Properties, and set the Visible property to False. 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.