Please enable cookies and refresh the page. 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. Add currency dimensions to the cube. Add the Exchange Rate measure group. Add exchange rate dimensions. 3 thoughts on “ How I fixed “Could not load file or assembly ‘Microsoft.sqlserver.smo, version=10.0.0.0′” issue ” $sammy! October 25, 2010 at 4:37 pm. · This Release Notes document describes known issues that you should read about before you install or troubleshoot Microsoft SQL Server 2012 (click here to. You cannot restore system database backups to a different build of SQL Server. Set up dimension usage. Add a reporting currency dimension. 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 2. 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. If you're using a separate database server, you'll 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 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 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 BIDS's 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, it's 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 attribute's 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, 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. Select your exchange rate fact table and click Add. In this example, it's 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 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 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 don't 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. Ubisoft Il 2 Sturmovik 1946 Patch on this page. 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. Figure 3: The cb. Windows 8 Permanent Activator By Misy Rar Files . Sales 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. Select Regular as the relationship type. Set the Granularity attribute to Date Key. In the Dimension Columns entry, make sure Date. Key from the dimension specified in step 1 has been selected. In the Measure Group Columns entry, select Transaction Date and click OK. Click the ellipsis button where the Exchange Rate measure group and the dimension specified in step 1 intersect. Select Regular as the relationship type. Set the Granularity attribute to Date Key. In the Dimension Columns entry, make sure Date. Key from the dimension specified in step 1 has been selected.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |