Expert Cube Development with SSAS Multidimensional Models (2014)

Chapter 7. Adding Currency Conversion

In this chapter, we will discuss how we can implement currency conversion in an Analysis Services solution. As we will see, it is not always a good idea to perform this currency conversion in the cube itself, it could be better to implement it in the ETL phase—it all depends on our requirements and analysis needs. We will provide some examples of different choices that are available in different circumstances.

SQL Server Data Tools (SSDT) provides a wizard to implement currency conversion in the cube, automatically generating the necessary objects and MDX Scripts. We will describe how to use this wizard in the most effective way. Then, we will see how to implement currency conversion using the MeasureExpression property of a measure, a feature that is faster but that is not available in the Standard Edition of Analysis Services (as it requires either the Business Intelligence or Enterprise edition).

Introduction to currency conversion

The need for currency conversion occurs when measure values have to be reported in a currency that is different from the one used to collect the data originally. It might seem as though this can be solved using a simple calculation; however, your assumptions might be wrong. Consider the following approach to currency conversion:

· Collect currency exchange rates on a daily basis

· Use daily exchange rates to convert each transaction into the desired currency

Why could this pattern be wrong? It might be correct in some cases, but it might be wrong for a variety of reasons. For example, the date used to link the exchange rate value to a transaction might not be the right one because the date of payment is not recorded in the transaction itself. In another example, if the payment has been made to a foreign currency bank account, the conversion to a different currency should be made using a fixed exchange rate instead of a daily changing exchange rate.

Therefore, before making any decisions on how to implement currency conversion, it is necessary to have very good and detailed business specifications. When they are not available, sit down with a business user who has the necessary knowledge of the data and work through the issues with him or her.

Note

More often than not, decisions about currency conversion are not technical issues but business decisions.

After we defined the requirements for currency conversion, we probably might find that they resemble one of the following three scenarios:

· Data is collected in a single currency and we need to display the results using multiple currencies. For example, a company has a list price defined using Euros and they want to show a product catalog on the Web using different currencies.

· Data is collected in several different currencies and we always need to display the results using just one currency. For example, different subsidiaries of a company operate in different countries using different currencies, but all data must be converted into a reference currency for reporting at the company's headquarters.

· Data is collected in several different currencies and we need to display the results using multiple currencies. For example, an international corporation wants to publish balance sheets of its various subsidiaries on its intranet, and allow users to select the currency to be used to show the data.

We will describe these scenarios in more detail and provide guidance on the use of currency conversion related features in Analysis Services.

Data collected in a single currency

If our source data is already stored in a single currency, we probably don't have very much work to do. If the original transactions were made using different currencies, probably the conversion to a single reference currency is done at the transaction level, otherwise it happens somewhere during the ETL phase. If the original transactions were made using a single currency, conversion is not necessary at all.

In both cases, the only reason we'll need to convert values from one currency to another is for reporting purposes because we don't have information about the original currency of the transaction any more (if indeed it was different from the one used to store the transaction amount in our fact table). This kind of conversion can happen at the highest level of aggregation using the same exchange rate regardless of the date of the transactions. However, one common requirement is to perform the conversion using the exchange rate that was correct on the date of each transaction. We suggest double-checking this kind of requirement if you have it, as if all the transactions have been stored using the same currency, it is probably the currency that was used when the transaction was originally made.

From a business point of view, forcing the use of a different exchange rate for each day does not respect reality because no currency conversion took place when the transactions were loaded into the fact table. The story is different if the original transactions were made using different currencies and they were converted to a single currency before loading the fact table. In that case, we should also store the original currency of the transaction, and also the original amount itself and/or the exchange rate that was used.

Thus, in the situation where we have data collected in a single currency, there are two possible subcases:

· Transactions were originally made in the same currency, and there are no signs of currency conversion having taken place for any transactions, so:

· It should be possible to convert an aggregated amount by a fixed exchange rate

· There should be no relationship between the fact table containing the transaction data and a historical exchange rate table

· Transactions were made in different currencies, and currency conversion took place during the transaction or in the ETL phase:

· Each transaction could have the exchange rate used recorded with the transaction itself

· There could be a relationship between the fact table containing the transaction data and a historical exchange rate table

Data collected in a multiple currencies

Our source data might have transactions stored in their original currency. When this happens, the transaction amount cannot be aggregated across different currencies because we cannot add Euros to Dollars without converting them to a reference currency first. This normalization requires currency conversion using an exchange rate that could be different for each transaction or, at the very least, for each day if we can apply the same exchange rate to all the transactions that took place on the same day.

We'll assume that, in this case, for each transaction, we have the currency used and the amount expressed in that currency stored in the fact table, but that we don't have a measure value in a single reference currency, otherwise we'd be in the situation described in the previous section (Data collected in a single currency).

Again, we have two possible subcases:

· Data collected in multiple currencies has to be displayed in a reference currency chosen by the end user at the query time:

· Conversion to the reference currency must take place at query time. If we don't do this, we will have to convert the source data into all the possible reference currencies in advance during ETL.

· Data collected in multiple currencies can only ever be displayed in a single reference currency:

· In this case, we could perform the conversion at query time or during the ETL phase.

Where to perform currency conversion

In the next section, we will see how to use the Add Business Intelligence wizard in SQL Server Data Tools to implement currency conversion. This wizard can be useful when we decide to implement currency conversion inside the Analysis Services cube itself. However, this is not always the best choice, as we are going to explain in this section.

Generally speaking, it's always better to perform any currency conversion during the ETL phase. However, this is usually only feasible when data will only ever be displayed in one reference currency—in other words, when the end user is not able to modify the currency to be used to display aggregated data. This does not mean we do not need to have a currency dimension in our cube because it could be useful to know the original currency of the transactions. It only means that aggregated data will always be displayed in the same currency, regardless of any selection made on any dimension.

For example, end users could see how many transactions were originally made in Euros and how many in US dollars. However, the aggregated values of transactions originally made in those currencies are always shown in US dollars, perhaps because the company's headquarters are in New York.

Doing the currency conversion in the ETL phase does not remove the need for an analysis of what the right exchange rate to apply is. For some businesses, the date of the payment is more relevant than the date of the transaction. In other cases, a monthly average of exchange rates would be more accurate because the real currency conversion is not made transaction by transaction, but takes place separately in bulk conversion operations. Henceforth, we will assume that an analysis of the right exchange rate to apply has already been done, for whatever scenario we are going to face.

Thus, there are at least three cases where we suggest implementing currency conversion directly in an Analysis Services cube. The first case is when the requirement is to display aggregated data in several different currencies to the end user, regardless of the original currency that the transaction was made in. If the requirement is to display values in a small number of currencies, for example, just Euros or US Dollars, the conversion can be handled in the ETL phase, but if end user potentially wants to see values converted to every currency in the world, it is not going to be feasible to do the conversion in the ETL.

The second case is when the user wants to modify the exchange rate values stored in the cube using Writeback, which means we cannot know what the rates we need to use are going to be at ETL time.

The third case is when we are not able to implement conversion in the ETL phase. In reality, this third case is more a workaround than a good pattern. For example, we might not have access to the ETL code, or there might be time constraints on ETL development, or we simply might not have any ETL at all, for example, if the cube was built directly on an OLTP database (definitely not something we suggest doing!).

For these reasons, we will describe how to implement all of the scenarios we described in Analysis Services, but we will focus mainly on the case where the end user wants to display aggregated data and choose the reference currency at query time.

Note

The existence of a feature does not mean that you should use it. Before performing currency conversion inside Analysis Services, check if your requirements can be met by implementing conversion during your ETL phase instead.

The Add Business Intelligence wizard

The Add Business Intelligence wizard can be used to implement currency conversion in a cube; you can find it under the Cube menu by clicking on the menu option Add Business Intelligence and then, on the second step of the resulting wizard, selecting theDefine Currency Conversion option. This wizard leverages Analysis Services features that are available in both Standard and Enterprise editions, such as MDX Script calculations. We can achieve the same result without using the wizard, or we can modify the objects and code produced by the wizard so they do what we want.

Concepts and prerequisites

Before using the wizard, we have to model our cube so that we have:

· A Currency dimension:

· The Type property of the dimension must be set to Currency

· The Type property of one attribute for the dimension must be set to CurrencyName

· The IsAggregatable property must be set to False for the key attribute

· A Time dimension:

· The Type property of the dimension must be set to Time

· The Type properties of dimension attributes must be set appropriately. For example, for an attribute representing a date it must be set to Date

· An Exchange Rate measure group containing:

· A regular dimension relationship with the Time dimension

· A regular dimension relationship with the Currency dimension

· The Type property of the measure group is set to ExchangeRate (this is optional, but it is a best practice)

· One or more other measure groups containing measures to be converted and which have:

· A regular dimension relationship with the Time dimension

· If the measures are stored in multiple currencies, a regular dimension relationship with the Currency dimension

Many of the preceding objects, which you create in your data model, are equivalent to objects that are created by the wizard, such as a Currency and Time dimension, or an Exchange Rate measure group. There are also other important concepts to learn before using the wizard.

The Exchange Rate measure group contains the exchange rates for converting many different currencies to a single reference currency that we will call the Pivot Currency. For example, consider the following table:

The Rate column here contains the exchange rate between the currency in the Currency column and the US Dollar (USD). In this case, the Pivot Currency is USD. Note that the Pivot Currency is not an attribute of the preceding table and the use of USD is implicit.

The measures that have to be converted using the Currency wizard store their values in a Local Currency. The local currency can be identified directly (for example, when the Currency dimension is directly related to the measure group) or indirectly (for example, when it is defined as an attribute of another dimension with a regular relationship to the measure group, such as the Country of a Customer). When data is collected in just one currency, the Local Currency will always be the same. In this case, making the Local Currency the same as the Pivot Currency is a good idea, just to make the conversion process more linear and easier to debug.

Finally, we will refer to the currency that users want to display data in as the Reporting Currency. It will be a member that the end user selects from the Reporting Currency Dimension, a dimension which will be created by the wizard and which will contain all the possible values for the Reporting Currency. This dimension will be based on a named query defined in the Data Source View, which in turn queries the main table of the Currency dimension.

How to use the Add Business Intelligence wizard

We will demonstrate the use of the wizard in three main scenarios. As we will see, the key decision you have to make in using the wizard comes on the third step, the Select Conversion Type page. Some of what the wizard generates is the same regardless of which option you choose and we will discuss these parts in the first scenario, and then we will comment only on what is different for the remaining scenarios.

Data collected in a single currency with reporting in multiple currencies

The first scenario we consider is probably the most common, where we have data collected using a single currency, and the same currency is used as the Pivot Currency. We will use a simple model to show the steps of the wizard. This model has a transaction fact table with two dimensions (Product and Date) and a currency exchange rate fact table with two dimensions (Date and Currency), as seen in the following screenshot. The Pivot Currency in the Rate measure group and the Local Currency in the transaction measure group are US dollars.

After having selected the Add Business Intelligence on the Cube menu in the Cube Editor in SQL Server Data Tools, and having chosen Define Currency Conversion on the list of available enhancements, the first step of the wizard is Set Currency Conversion Options, which we can see in the following screenshot:

The list of measure groups containing exchange rates will display all measure groups in the cube that are related to a Currency dimension. Remember that it is best practice to set the Type property of the exchange rates measure group to ExchangeRate; however, this is not mandatory for the wizard to work. The wizard also displays all the measure groups that are related to a Currency dimension, which is any dimension with a Type property set to Currency.

The choice of the Pivot Currency must be consistent with the data we have in the Exchange Rate measure group. In the example shown, we use USD as the Pivot Currency. Finally, we have to specify if the exchange rate we have is from the Conversion Currency to the Pivot Currency or vice versa. In our previous example, the rate represents how many US dollars are equal to one Euro. The only purpose of the combobox in the lower part of the dialog is to show the right currency names in the examples next to the two radio buttons below it.

The next step of the wizard is the Select Members page. Here, we can select the measures that will be converted using the exchange rates defined in the previous step. Instead of converting the value of one or more measures, we could select members from an Account hierarchy—useful if only some members on your Account hierarchy contain currency values. This option is disabled in the following screenshot, because there are no Account hierarchies defined in the cube we're using here:

The following step is the Select Conversion Type page, which is the most important step for the scenario we are describing now. In this case, we have to choose the One-to-many option because in the fact table we have measures collected using a single currency and we just want to be able to display the aggregated data using the Reporting Currency dimension of our choice.

In the next step, the Specify Reporting Currencies page, we can filter the list of currencies that can be used as Reporting currencies. The result of this choice determines the list of currencies included in the WHERE condition of the Named Query that is created in the Data Source Views for the Reporting Currency dimension.

The last step of the wizard, the Completing the Wizard page, simply summarizes the changes to the cube that the wizard is going to make. Note that cancelling the wizard at this point will not apply any of the changes to the cube. One other important thing to point out is that if we run the wizard twice on the same cube, it will show only the differences made to the cube on the second run, which will typically affect only the MDX Script.

Note

The section of the MDX Script generated by the wizard is completely replaced if the wizard is executed again. Bear this in mind before modifying any of the MDX code enclosed by the commented out <Currency Conversion> tag in the Script.

After clicking on Finish, we can see that the wizard has created a Reporting Currency dimension, which has been added to the cube and has no relationship with any measure group as we can see in the following screenshot:

It might seem strange that now we have two Currency dimensions. However, there is a good reason for this: it's the way it supports the requirement to be able to allow only some currencies to be used for reporting purposes.

Note

If we don't need the filter on available currencies provided by the Reporting Currency dimension, we could simplify the structure of the cube by using the original Currency dimension instead (although this would also require some modifications to the MDX Script generated by the wizard).

To make the Reporting Currency dimension work, the wizard adds a section of MDX code to the Script, similar to what's shown as follows:

// All currency conversion formulas are calculated for the

// non pivot currency and at leaf of the time dimension

SCOPE ( { Measures.[Amount]} );

SCOPE( Leaves([Date]) ,

Except(

[Reporting Currency].[Currency].[Currency].Members,

[Reporting Currency].[Currency].[Currency].[USD]));

SCOPE( { Measures.[Amount]} );

THIS = [Reporting Currency].[Currency].[USD] /(Measures.[Rate], LinkMember(

[Reporting Currency].[Currency].CurrentMember,

[Currency].[Currency])) ;

END SCOPE;

END SCOPE; // Leaves of time and non pivot currency

END SCOPE; // Measures

This script is interesting as it shows off some of the capabilities of MDX. The nested SCOPE statements define the area within the multidimensional space of the cube that the assignment affects. In the first and third SCOPE statement, the space is restricted to the Amountmeasure. The second SCOPE is the most important one because it defines the granularity of the assignment we are going to do. The LEAVES function returns the most detailed level of the Date hierarchy (which is an attribute hierarchy in our sample cube) that corresponds to the day level. The remaining part of the SCOPE excludes the Pivot Currency from this calculation. Thus, the assignment will change the value of the Amount measure only at the day level. If we will query any part of the cube above the day level (for example, a month or a year), the calculations will take place at the day level and the result will be aggregated up to the level we're querying at. The important concept here is that a scoped assignment at a low level of granularity on a real measure leads to the results of the calculations being aggregated up using the natural aggregation behavior of the measure (which is SUM in our case).

The assignment inside the SCOPE statements converts the Pivot Currency value (which is in USD) into the selected currency in the Reporting Currency dimension. The LinkMember function transfers the selection made on Reporting Currency dimension to the Currency dimension. Since this is the case, the user might not ever need to select anything directly on the Currency dimension, so it could be made invisible to them to avoid confusion.

In the following screenshot, we can see the result of a query which has the Reporting Currency dimension on rows and two dates on columns. The original data contained sales of 210.00 USD for both the selected days. The conversion to the other two currencies has been made using the daily exchange rate applicable for each day. Thus, values above the day granularity cannot be pre-calculated using cube aggregations, because MDX Script will always aggregate the daily calculated data at runtime.

Data collected in multiple currencies with reporting in a single currency

The second scenario we're going to look at is not so common. In fact, if we have transactions stored in different currencies and we want to report in a single fixed currency, it would make sense to implement the currency conversion for each transaction during the ETL phase. However, there could be reasons why this is not possible. For example, we might receive exchange rate data very late and we don't want to delay data processing, or to have to reprocess that data once the exchange rates are available. Another reason could be that users want to use Analysis Services Writeback to change exchange rates in an interactive way, and watch the impact of these changes on the aggregated transactional data in the cube. Whatever the reason, we can use the wizard to implement this kind of currency conversion in Analysis Services.

The data model we will use in this section is shown in the following screenshot. It is similar to the previous model, but this time the transaction fact table also has a link to the Currency dimension. In this example, the Pivot Currency in the rate measure group is still US dollars, but the Local Currency in the transaction measure group will vary for each transaction and is recorded using the Currency dimension.

The same selections should be made on the wizard as in previous scenario, until we reach the Select Conversion Type page. Here we have to choose the Many-to-one option, because the Local Currency is specific to each transaction and we want to report on our data using a single, fixed currency. The destination currency will be the one chosen as Pivot Currency.

At this point, the wizard shows a new page, Define Local Currency Reference, where we tell the wizard how to determine the Local Currency used in each transaction. In the examples we are using, we have to select the Currency attribute of the Currency dimension.

Alternatively, it is possible to use an attribute of another dimension to store the Local Currency. This would be the case, for example, if the currency to use was stored in an attribute related to a Country attribute on the Customer dimension.

Note

It is very unlikely you'll have a source data mart where measures are stored in a Local Currency, but where you don't know what that currency is. This is only likely to happen in prototypes of cubes based on raw data imported from several sources without any ETL in the middle.

The wizard then finishes and displays the Completing the Wizard summary page. At this point we have a new Reporting Currency dimension, which has its IsAggregatable property set to False, and contains only two members: one is USD, which we have used as the Pivot Currency, and the other is the constant Local. The default member for this dimension is USD, but the user can always choose to see the original value of the measure in its Local Currency by choosing the Local member on the Reporting Currencydimension.

Note

If the data type of the field used to store the currency name in the Currency attribute on the Currency dimension is less than five characters long, the wizard creates a Reporting Currency dimension with an attribute that is too short to hold the string "Local". To avoid a truncation error during dimension processing, use a field at least five characters long to store the currency name in the relational table on which the Currency dimension is based. Alternatively, we can modify the Named Query that the wizard generates in the Data Source View for the Reporting Currency dimension, for example, using a cast in the SQL statement.

This is the MDX created by the wizard:

SCOPE ( { Measures.[Amount]} );

SCOPE( Leaves([Date]) ,

[Reporting Currency].[USD],

Leaves([Currency]));

SCOPE( { Measures.[Amount]} );

THIS = [Reporting Currency].[Local]

* Measures.[Rate];

END SCOPE;

END SCOPE;

END SCOPE; // Measures

The code here is similar to the MDX generated in the previous scenario. In this case, we have a single Reporting Currency (USD) and the conversion formula is applied to all the Local Currencies through a scoped assignment on LEAVES([Currency]).

The resulting cube will have two currency dimensions: the original dimension is used for the Local Currency and the dimension added by the wizard is used for the Reporting Currency. When nothing is selected on these dimensions the end user will see USD used as the Reporting Currency; the following screenshot shows both the original values of the Local Currencies and the converted USD values:

One important thing to point out is that to get the results we just showed, we need to define an exchange rate for converting USD values to USD. This is a requirement of the MDX code generated by the wizard; if we don't do this, we'll see an empty cell for the USD/USD cell in the previous report and this will affect the grand total too. This is not smart behavior on the part of the wizard-generated code because after all there should be no need to convert a currency into itself. As a result, we need an exchange rate table like this:

Data stored in multiple currencies with reporting in multiple currencies

The third and last scenario is the most complex one. Here, we have transactions stored in different currencies, but this time we want to report in any currency chosen by the end user. Even in this case, some currency conversion could take place during the ETL phase to normalize data to the Pivot Currency, but we might need to do the conversion in Analysis Services for the same reasons we discussed in the previous scenario.

The data model that we're going to use is the same as in the previous scenario. We'll also makes the same selections in the wizard until the Select Conversion Type page, where we have to choose the Many-to-many option. The next step will be the Define Local Currency Reference page, and after that will be the Specify Reporting Currencies step, just the same as in the first scenario.

The MDX generated is a combination of that seen in both the previous scenarios:

SCOPE ( { Measures.[Amount]} );

SCOPE( Leaves([Date]) ,

[Reporting Currency].[USD],

Leaves([Currency]));

SCOPE( { Measures.[Amount]} );

THIS = [Reporting Currency].[Local]

* Measures.[Rate];

END SCOPE;

END SCOPE; // Leaves of time and non pivot currency

SCOPE( Leaves([Date]) ,

Except(

[Reporting Currency].[Currency].[Currency].Members,

{[Reporting Currency].[Currency].[Currency].[USD],

[Reporting Currency].[Currency].[Currency].[Local]}));

SCOPE( { Measures.[Amount]} );

THIS = [Reporting Currency].[Currency].[USD] /

(Measures.[Rate], LinkMember(

[Reporting Currency].[Currency].CurrentMember,

[Currency].[Currency])) ;

END SCOPE;

END SCOPE; // Leaves of time and non pivot currency

END SCOPE; // Measures

The first assignment converts the Local Currency into the Pivot Currency, USD, as in the previous scenario. The second assignment converts the Pivot Currency we just calculated into the Reporting Currency. This second statement has a slightly different SCOPE to the one we saw in the first scenario, because it has to exclude both the USD and Local members from the Local Currency dimension (which is simply named Currency).

After running the wizard, we can combine any Local Currency with any Reporting Currency, as in the following screenshot:

Notice that, once again, a special Local member has been added to the Reporting Currency dimension. And once again, an exchange rate with the value 1 for the currency used as Pivot Currency has to be present in the Exchange Rate measure group.

Measure expressions

We have now seen how to implement currency conversion using the Add Business Intelligence wizard in BI Developer Studio. The wizard implements currency conversion using techniques, such as MDX Script calculations that are available in both the Standard and Enterprise editions of Analysis Services. However, there is an alternative way to implement currency conversion without the need for calculations in the MDX Script. We can use the MeasureExpression property of a measure instead, combined with a many-to-many dimension relationship in the cube.

The data model that we'll use to demonstrate this technique is very similar to the data model we used in the first scenario for the wizard. The only change is the definition of a many-to-many dimension relationship between the Currency dimension and the measure group containing the transaction data.

The Currency dimension, here, will work in the same way as the Reporting Currency dimension does in the solution generated by the wizard. The MeasureExpression property contains an MDX expression that returns the value of the measure. This expression will be evaluated at the lowest common granularity of the two measure groups before aggregation takes place. Functionally, it is not much different to the MDX Script calculations generated by the currency conversion wizard. However, from the point of view of query performance, it is potentially faster.

The MDX expression used in a MeasureExpression property must be in the form M1 op M2, where the operator op can be only * or /. M1 and M2 must be measures from different measure groups, with the condition that these measure groups share at least one common dimension. In our example, we have two dimensions that are used by both measure groups: one is Date and the other is Currency (which has a many-to-many relationship). We can use the following expression in the MeasureExpression property of the Amountmeasure:

Amount / Rate

The MeasureExpression is not recursive: when the MDX expression refers to Amount in the division, it will use the raw value of the Amount measure before any MDX Script calculations have taken place. The Rate measure in the expression contains the exchange rate value stored in the Exchange Rate measure group. The calculation will take place at the Date and Currency granularity and the result will then be summed up to higher levels of granularity.

Note

The MeasureExpression expression is evaluated before the calculations in the MDX Script are evaluated. Thus, if you perform a calculation in the MDX Script on a measure that has its MeasureExpression property set, the value of that measure will have already been changed as a result of the calculation of the measure expression.

At query time the results we get are identical to the ones we obtained in the first example in this chapter, as we can see in the following screenshot:

At this point we still need to have a row containing a rate of 1 for US Dollars for each day in the Date dimension. Thus, USD values are still converted to USD using the MeasureExpression property, even if it is not really necessary. However, in this case we can optimize the conversion using the DirectSlice property of the many-to-many dimension relationship that relates the Currency dimension to our Transactions measure group.

DirectSlice property

The DirectSlice property of a many-to-many dimension relationship can be set to contain an MDX tuple. When that tuple is selected in a query the MeasureExpression property will be ignored and the raw measure value will be returned instead. In other words, for certain parts of the cube we want the original value of the measure instead of the calculated value returned by the expression in the MeasureExpression property. For example, we can skip the MeasureExpression calculation when the Reporting Currency is the same as the Pivot Currency, which is USD in our example. This has two consequences:

· First, there is no need to put rows in the Exchange Rate fact table containing the rate 1 for US Dollars for each day.

· Second, when the user requests the USD Currency and looks at data that is stored as US Dollars in the fact table, the query response will be faster because there is no need to resolve the many-to-many relationship or perform any currency conversion.

Please note that we cannot use MDX functions to build dynamic expressions in the DirectSlice property, we can only write an explicit tuple such as ([Currency].[Currency].&[USD]).

Writeback

As we mentioned earlier, when working with exchange rates our users may need to do "what-if" analysis to see what the impact of different exchange rates would be on the company's profitability. We can let our users do this by "write-enabling" a measure group: on the Partitions tab of the Cube Editor, right-click on any partition within the measure group and select Writeback Settings to display the Enable Writeback dialog.

Note

You cannot enable Writeback on a measure group if it contains a measure whose AggregateFunction property is set to anything other than Sum.

When you click on OK, Analysis Services will create a new Writeback partition in your measure group and also a new fact table in your relational data source. The fact table does not hold the values that users write back to the cube; instead it holds delta values so that the sum of the original values in the measure group plus the deltas adds up to the last value written back to a cell.

Writeback can lead to poor query performance on larger measure groups, although in the case of an exchange rate measure group this is not likely. There are two pain points:

· When a user commits an updated cell value back to the cube, this value must be allocated down to the granularity of the Writeback fact table. If the cell you're writing back to is at a much higher granularity than the fact table, this can take a very long time.

· When a user queries a cube with Writeback enabled, the Writeback partition has to be queried along with the original partitions. Using MOLAP storage mode for your Writeback partition, which is the default, reduces this performance penalty and we recommend you always do this rather than use ROLAP storage. However, using MOLAP storage means committing updated cell values will take slightly longer because Analysis Services now has to process the Writeback partition as well.

Note

If you intend to use Writeback, first check whether your chosen client tool supports it. Some, for example Excel 2007, do not (it is supported in Excel 2010 and following versions).

Summary

In this chapter, we have seen how to implement currency conversion in several different ways. In many cases currency conversion can take place in the ETL phase and where this is possible it is the best option to take. However, there are some situations that require that currency conversion be implemented in Analysis Services.

We have seen how to use the Define Currency Conversion option in the Add Business Intelligence wizard. This wizard generates the necessary objects and MDX Script calculations to implement currency conversion in both the Standard and Enterprise editions of Analysis Services. For better query performance, we have also discussed how to implement currency conversion using the MeasureExpression property instead, which is available only in Enterprise edition.

In the next chapter, we will discuss tools and techniques to improve query performance.