The basic expression for currency conversion is:

[Measure in Target Currency] = [Measure in Source Currency] * [Currency Conversion Rate]

In this example, we have a measure that represents a value converted from the source currency to a target currency.  The initial value of this measure is set to the source currency (specified by the Source column). 

Now, we need to overwrite the measure’s value in the MDX script so it represents the measure in the target currency (in this example, the measure we’re overwriting is called ‘Quantity Available Cost Converted’. The measure ‘Quantity Available Cost Local’ is in the original/source currency).  The target currency is selected by the end-user.

SCOPE([Measures].[Quantity Available Cost Converted]
,LEAVES([Source Currency])
,LEAVES([Destination Currency])
,LEAVES([Currency Rate Type])
,LEAVES([Dates]));
THIS = [Measures].[Quantity Available Cost Local] * [Measures].[Currency Rate Ov];
END SCOPE;

The SCOPE function ensures that we are only affecting the portion of the cube specified in the function.  In this case, the function overwrites the measure Quantity Available Cost Converted where it intersects the leaves of the Source Currency, Destination (target) Currency, Currency Rate Type and Dates dimensions.  The LEAVES function is important since the measure uses the ‘Last Non-Empty Value’ aggregation and is therefore semi-additive.  We only want to perform the calculation at the leaf level and not use the aggregations.

This blog post describes implementing currency conversion in an SSAS cube via Measure Expressions.  In this scenario, the data is collected and reported in multiple currencies.

Dimensional Modeling

The dimensions and facts are modeled as follows:

• Source Currency Dimension
  • Attributes:  CurrencyCodeKey, CurrencyCode (e.g. USD)
• Target / Destination Currency Dimension
  • Attributes:  CurrencyCodeKey, CurrencyCode (e.g. USD)
• Date/Time Dimension
  • Attributes: DateKey, Date, Fiscal Week, Fiscal Month, etc.
• Exchange Rate Measure Group (labeled FactConversionMRate in the diagram below) 
  • Attributes: SourceCurrencyKey, DestinationCurrencyKey, CurrencyRate
• Transactional Measure Group(s) (e.g. sales data, purchase order data, etc.)
  • Relationship with the Date/Time dimension (DateKey) and Source Currency Dimension (CurrencyCodeKey).

The diagram below shows the relationships between the fact and dimension tables.  I did not include the table with transactional data in the diagram.  The transactional data will generally have a relationship to the DimDate table, DimSourceCurrency, and DimCurrencyRateType tables.  The currency rate type is only necessary if  several different types of conversion rates are used. 

SSAS Currency Conversion

Once you have the data loaded into the physical fact and dimension tables, you can begin to modify the cube.  The actual conversion calculations will be performed via Measure Expressions in SSAS.

The first step is to add the fact and dimension tables to the data source view.

Next, you will need to create a measure group that represents the conversion rate.  In the example diagram above, the measure group is based off the FactConversionMRate table with two measures CurrencyRateOv and CurrencyRateDiv.  These measures are the rates used to convert from the ‘FromCurrency’ to the ‘ToCurrency’ and vice versa.

You will also need to include dimensions for each of the dimension tables shown in the diagram above.  This includes date, source currency, target / destination currency and currency rate type (optional).  The source currency and target currency have one attribute: currency code.  The currency rate type dimension also has one attribute: currency rate type key. 

The Source Currency Code and Destination Currency Code attributes have their KeyColumn set to CurrencyCodeKey and NameColumn set to CurrencyCode.  Similarly, the Currency Rate Type Key attribute has the KeyColumn set to CurrencyRateTypeKey and the NameColumn set to CurrencyRateType.

Both the source currency and currency rate type dimensions may be hidden (Visible set to False) since the values are already defined in the transactional data and should not be modified by the end-user.

The Destination Currency dimension should be visible so the end-user has the ability to select the desired target currency.

After creating the measure groups and dimensions in the cube, you will need to modify the relationships in the ‘Dimension Usage’ tab.  The relationships should be configured as follows (I am only displaying the measure groups and dimensions that relate to the currency conversion.):

The Many-to-Many relationship is based on the Conversion Rate measure group.

Once the relationships between the facts and dimensions are established, the measure expressions may be added.

First, create a new measure in the transactional measure group (e.g. sales).  Select the appropriate Source Table and Source Column.  The source column contains the value you want to convert to the target currency.

In the measure properties ‘Measure Expression’ field, enter in the appropriate calculation to perform the currency conversion.  Something similar to that shown below:

[Extended Price] * [Currency Rate]

You now have all the pieces in place to perform currency conversions in SSAS.

Summary

This blog post demonstrates one approach to implementing currency conversions.  Many alternative approaches exist in BI.  It is often possible to implement currency conversion directly in the ETL as opposed to SSAS.  This is especially true if you are converting between a small number of source and target currencies.  If your users need the ability to convert to a wide range of target currencies, it may be ideal to implement the currency conversion directly in SSAS using an approach similar to that described in this blog post.

Although on the surface they appear very similar, several notable differences exist.

The following grid lists a few of the differences between the tabular model and PowerPivot:

There are several new features common to both PowerPivot and Tabular Models:

• Hierarchies
• KPIs
• Perspectives

If you’d like to see a list of the new features in PowerPivot 2012, check out this link (http://technet.microsoft.com/en-us/library/hh272053(SQL.110).aspx).

• Flat File Source Error Output Column – The data row that generated the error.
• ErrorCode – Code associated with the error that occurred.
• ErrorColumn – Numeric ID of the column that caused the error.

Obtaining the ErrorDescription requires a bit more work, but can be easily accomplished using the data flow Script Component.

The first step is to add a  ‘Script Component’ to your data flow and connect the Error Output as an input to the script component as shown below.

Double click on the script component to edit.

Under the ‘Input Columns’ tab, check the ErrorCode column (you may also include additional columns if you wish to use them in subsequent data flow components).  This column is required to obtain the Error Description.  The ‘Usage Type’ should be set to ReadOnly.

Next, select the ‘Inputs and Outputs’ tab.  Expand ‘Output 0’ and select ‘Output Columns’. Click the ‘Add Column’ button.  In the ‘Name’ field, enter a descriptive name such as ErrorDescription.  Set the DataType field to string [DT_STR] and the length to 255.

Click on the ‘Script’ tab and then click the ‘Edit Script’ button.

Modify the Input0_ProcessInputRow function (this example uses C#)

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
      Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
  }

Many components in the data flow support error outputs that allow you to control how truncation and errors are handled.  The Error Outputs contain an ErrorCode and ErrorColumn in addition to the data columns in the row that caused the error.  Error outputs can be connected to another component in the data flow (e.g. a flat file destination to log the rows that generated errors).

You can control how data errors and truncation are handled:

1. Double click on the Data Flow component.

2. Select ‘Error Output’.

3. In the ‘Error’ and ‘Truncation’ columns, you specify how each scenario should be handled.  You may click in any cell to set the value or select multiple cells and then use the drop-down at the bottom of the window to set the value.

The options are:

• Ignore Failure – The row is routed to the  ‘Success’ / Default output.
• Redirect Row – The row is routed to the ‘Error’ output.
• Fail Component – The data flow task fails (this is the default option).

In the example below, the error output is being routed to a script component.  The script component is used to obtain a description of the error based on the error code (this blog post provides more details).  The errors are then routed to a flat file destination.

The ‘Flat File Destination’ is configured so the file is overwritten (so it doesn’t grow uncontrollably).

Under the “Mappings” tab, the following columns are written to the file.

• Flat File Source Error Output Column – The data row that generated the error.
• ErrorCode – Code associated with the error that occurred.
• ErrorColumn – Numeric ID of the column that caused the error.
• ErrorDescription – Description of the error that occurred.  The ErrorDescription was obtained in the ‘Script Component’ using the GetErrorDescription function, more details are included in this blog post.

=IIF(Fields!Denominator.Value = 0, 0, Fields!Numerator.Value/Fields!Denominator.Value)

I soon realized that this does not resolve the issue.  It appears that all parameters  in the IIF function are evaluated regardless if the first parameter evaluates to true or false.  Therefore, the divide-by-zero was still occurring.

After doing some research, I decided that the best option to avoid the divide-by-zero error is to implement custom code.

Note: The following screen shots are from Report Builder 3.0

The first step is to open the Report Properties window.  You can access the report properties by clicking anywhere outside of the report body.

If you still cannot see the Report Properties window, make sure you have the ‘Properties’ option checked in the ‘View’ tab.

The Report Properties window is displayed below.  In the Code text box, click the ellipse […].  You may need to click on the Code text box first to see the ellipse button.

Next, select ‘Code’ in the left hand menu if it is not already selected.  Paste the code (displayed below screen shot) in the Custom code field.

Function Divide(Numerator as Double, Denominator as Double)
If Denominator = 0 Then
Return 0
Else
Return Numerator/Denominator
End If
End Function

Now that you’ve created the custom code, you can begin to use the code in your report.  The following is an example of how you can use the Divide function in a text box expression:

=Code.Divide(Fields!CurrentYearSales.Value-Fields!PriorYearSales.Value,Fields!PriorYearSales.Value)*100

I am going to make the assumption that readers are familiar with the SSIS script component.  If not, please feel free to read my prior blog post on the subject: SSIS Dataflow Script Component

To implement isNumeric( ) behavior, I used the C# Int32.TryParse method.

TryParse(String, Int32):  Converts the string representation of a number to its 32-bit signed integer equivalent. A return value indicates whether the conversion succeeded.

Here’s a snippet of the code in C#:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    int BatteryQuantity1Int;

    if (! int.TryParse(Row.BatteryQuantity1Input, out BatteryQuantity1Int))
       BatteryQuantity1Int = 0;   

    //Add an output row and set the column values
    Output0Buffer.AddRow();
    Output0Buffer.ModelNumber = Row.ModelNumberInput;
    Output0Buffer.ModelYear = Row.ModelYearInput;
    Output0Buffer.ModelName = Row.ModelNameInput;

I also came across a blog post by Dustin Ryan where he implements IsNumeric( ) functionality using the Derived Column Transform.  The post is found here.

Whenever I set up logging in an SSIS package, I always log events at the package level using the SQL Server log provider.  The type of events I log are: On Error, On Post Execute, On Pre Execute, On Task Failed, and On Warning.

The following is a list of steps to configure package logging as I described above:

Right click on the control flow surface and select ‘Logging’ (or select ‘Logging’ in the SSIS menu)

In the ‘Containers’ section, you select the container or scope for logging.  I always select the package level (to log events in the entire package) by checking the box next to the package name. 

In the ‘Add a new log’ section, select ‘SSIS log provider for SQL Server’ then click ‘Add’.

In the ‘Configuration’ field, select the SQL Server database where you want to store logged events.

Next, click the ‘Details’ tab.  On the details tab you specify the types of events you want to log.  I always select the following: On Error, On Post Execute, On Pre Execute, On Task Failed, and On Warning.

• On Error – Writes a log entry when an error occurs.
• On Post Execute – Writes a log entry immediately after the executable has finished running.
• On Pre Execute  – Writes a log entry immediately before the executable runs.
• On Task Failed – Writes a log entry when a task fails.
• On Warning – Writes a log entry when a warning occurs.

When finished, click ‘OK’. 

You should also create an event handler for each type of event you’re logging.  To create an event handler, click the ‘Event Handlers’ tab.

In the ‘Event Handler’ drop-down, select the appropriate event type.  Then click the link to create an event handler for that specific event.  You need to repeat this process for each type of event (On Error, On Post Execute, On Pre Execute, On Task Failed, and On Warning)

When you’re finished, the list in the ‘Event handler’ drop-down should look like this:

Logged events are stored in the dbo.sysssislog system table in the database you specified while configuring package logging.

I had one column in my spreadsheet that was sparsely populated, nearly all blank values.  When I ran my SSIS package to extract the data from the spreadsheet, every row in that column was set to NULL including those that had a valid value in the spreadsheet.

Upon further investigation, I discovered the issue stems from the Jet driver.  Apparently, by default, only the first 8 rows of data are scanned to determine the data type for each column.  If the first 8 rows of data in a column are blank, then any valid data in the additional rows is ignored and treated as NULL.  It is possible to change the value in the registry so more than 8 rows are scanned.

In addition, I also discovered that if you use the Excel data source and want to run your package from BIDS, then ‘Run64BitRuntime’ in the solution properties must be set to False.  You can configure this as follows:

Right click on the solution in BIDS and select ‘Properties’.

Set Run64BitRuntime to False.

• I recommend this book to anyone who has a solid understanding of SSAS, works with SSAS on a regular basis and wants to take their cube development and optimization to the next level.  This is not a beginner level book. 
• The content of this book is very well-organized.  The first chapter provides an overview of basic dimensional modeling concepts: Ralph Kimball vs. Bill Inmon, dimensions and facts, star schema vs. snowflake schema, etc.  The remaining chapters are all specific to SSAS starting with building dimensions and cubes, creating measures and measure groups, adding transactional data to fact tables, enhancing the cube with MDX calculations, implementing currency conversions in SSAS, query performance tuning, implementing security, “productionization”, and monitoring cube performance and usage. 
• This book does not focus on  menus or user interface (it is assumed that you work with SSAS regularly and already know this); nor does it provide an overview of every feature SSAS offers.  Instead, the authors focus on best practices (from their perspectives) gained from years of experience working with SSAS. 
• The book describes common scenarios and challenges encountered during cube development and provides reasons why one approach may be better than another and when you’d want to select one option over another.  Many other books I’ve read explain the features and options but never delve into a detailed explanation of the pros/cons like this book does.
• Links to additional sources of information such as blog posts, white papers and other reference materials are shared throughout the book.  I have not read all of the additional reference materials but the ones I did read were high-quality.

I personally have about 8 months of experience working with SSAS – this book provided me with lots of good tips and recommendations that are not ‘common knowledge’ and can only be gained through years of experience with the product.  I feel that this book is a great investment and a resource I can leverage well into the future.