<Warning WarningCode=”1092550658″ Description=”Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_DimClaim’, Column: ‘ClaimCanceledReasonCode’, Value: ”. The attribute is ‘Claim Canceled Reason Code’.” Source=”Microsoft SQL Server 2008 R2 Analysis Services” HelpFile=”" />

In the example above, the error was due to a carriage return stored in the ClaimCanceledReasonCode column.  During processing, SSAS converted the carriage return to an empty string and since the empty string was already processed as a key value from another row the ‘duplicate attribute key’ error was generated.

When I first encountered this error, I was confused because a Select Distinct query should never return a duplicate in the first place.  I then discovered that SSAS maps certain types of values (e.g. NULL, carriage returns, etc.) to an empty string which causes this error to arise if there is another row in the dataset that contains an empty string value.  In my experience, this is the most common cause of this error.

The following blog post provides a good overview of the various scenarios that may cause the duplicate attribute key error and how to avoid them:  http://ms-olap.blogspot.com/2009/11/duplicate-attribute-key-has-been-found.html.

The fix is pretty easy, just change the Code Contracts targets file to the correct path. The targets file is located at C:\Program Files (x86)\Microsoft\Contracts\MsBuild\v4.0\Microsoft.CodeContracts.targets. Line 267 should be changed to:

<CodeContractsSnExe>”$(CodeContractsSdkPath)Bin\NETFX 4.0 Tools\sn.exe”</CodeContractsSnExe>

To process all dimensions:

1.  Connect to an SSAS instance in SSMS.

2. Expand the appropriate node / cube.

3. Select ‘Dimensions’ and in the SSMS menu at the top select ‘View’ > ‘Object Explorer Details’.

4.  You will then see the following window with a list of the dimensions.

5. Select all dimensions by first clicking on the top dimension in the list, hold down the ‘Shift’ key and then click the last dimension.  You should now have all the dimensions selected as shown below.

6.  Right click anywhere on the dimensions and then click ‘Process’.

7.  You should now see a ‘Process Object (s)’ window.  Update the ‘Process Option’ for each dimension if needed and then click ‘OK’.

8.  A dimension ‘Processing Progress’ window is displayed. If the processing completes successfully, you should see a ‘Process succeeded’ message.    When finished, close the window.

The steps to process all the measures are almost identical:

1.  Connect to an SSAS instance in SSMS.

2. Expand the appropriate node / cube.

3. Expand the Cubes node to the ‘Measure Groups’ level.

4.  Select ‘Measure Groups’ and in the SSMS menu at the top select ‘View’ > ‘Object Explorer Details’.

5.  You will then see the following window with a list of the measure groups.

6.  Select all dimensions by first clicking on the top measure in the list, hold down the ‘Shift’ key and then click the last measure.  You should now have all the measures selected.

7.  Right click anywhere on the measures and then click ‘Process’.

8.  You should now see a ‘Process Object (s)’ window.  Update the ‘Process Option’ for each measure if needed and then click ‘OK’.

9.  A measure ‘Processing Progress’ window is displayed. If the processing completes successfully, you should see a ‘Process succeeded’ message.    When finished, close the window.

#File Locations
$file1 = \\yourfileshare\file1.txt

$file2 = \\yourfileshare\file2.txt

#Check for Existence of Files
$file1Exists = Test-Path $file1

$file2Exists = Test-Path $file2

#Email Settings
$emailFrom = “EmailFromAddress@yourdomain.com”
$emailTo = “EmailToAddress@yourdomain.com”
$subject = “Missing Files”
$body = “The following files are missing:`r`n”
$smtpServer = “123.45.67.890″
$smtp = new-object Net.Mail.SmtpClient($smtpServer)

If ($file1Exists -and $file2Exists)
{ 
     #Do Nothing
}
Else
{

     #Generate Text for Email Body
     If (-not $file1Exists) {
        $body += “`r`n” + $file1 + ” .”
     }
     If (-not $file2Exists) {
        $body += “`r`n” + $file2 + ” .”
     }

     #Send Email
     $smtp.Send($emailFrom, $emailTo, $subject, $body)

     #Throw Exception to Terminate Job
     throw “Missing Files”

}

I recently used an asynchronous transform to group rows based on two column values (ShipToNumber and NoteDateTime) and then concatenated the values from a third column (Note) and output a single row to the downstream data flow.

After configuring the data source to extract the desired data, the first step is to add a Sort component to sort the rows based on the two columns we wish to group by (in this example I also needed to sort on the sequence column so the Note column values are concatenated in the correct order).

The Sort component is configured as follows:

In this example, I am sorting by three input columns in ascending order.  The remaining columns are configured to pass through the component.  Performing this sort functionality is necessary so the rows are sent in the desired order to the Script Component.

Now we will add a Script Component to the data flow.  The Script Component is represented by the following icon in the toolbox.

When you drag the script component onto the data flow surface, you will be prompted to select one of the following options:

In this case you will want to select the ‘Transformation’ option if it is not already selected and click ‘OK’.

Double click on the Script Component to configure its properties.

Setting the Script Language

In this example, I am using C# as the script language.  You can change the script language by setting the ScriptLanguage property on the Script page of the editor as shown below.

Configuring Input Columns

The Script Component has one configured input by default named Input0.  In this example, that is sufficient for our needs.   We will now need to add columns to this input.  All columns we want to flow downstream to subsequent components should be included in the input.  In this example we are including all available columns as Input Columns, except for the Sequence column.  All input columns are set to ‘Read Only’, since their values should not change.

Configuring the Output Columns

Once we have selected the appropriate Input Columns, we now will configure the Inputs and Outputs.  Select the ‘Inputs and Outputs’ tab on the left-hand side.

First, we need to configure Output 0 to be an asynchronous output.  Click on ‘Output 0’ in the ‘Inputs and outputs:’ hierarchy.  Set the ‘SynchronousInputID’ property to ‘None’.

Now we can add the Output columns.  To add a column, you first need to select ‘Output 0’ in the ‘Inputs and outputs:’ hierarchy.  Then click the ‘Add Column’ button.  You will be prompted to give the column a name and then select the correct data type in the right hand panel.  In this example, the output columns are the same as the input columns with the exception of the ‘Note’ column which is re-named to ‘CombinedNote’.  CombinedNote is a concatenation of the Note column of the input rows.

Modifying the Script Code

Now we are ready to modify the code in the script.  First, click the Script tab in the left-hand side of the Editor window.  Next, click the ‘Edit Script’ button.

In public class ScriptMain : UserComponent, we first need to create some private member variables that represent the columns in our output.  We also need a Boolean variable that is used as a flag to check for the first row.

private string CombinedNote;
private int ShipToNumber;
private DateTime NoteDateTime;
private DateTime Timestamp;
private string User;
private bool firstRow = true;

We must then update the Input0_ProcessInputRow(Input0Buffer Row) method.  In this example, we are ‘grouping’ the input rows by the columns ShipToNumber and NoteDateTime.  The rows are already sorted by the Sort component in ascending order based on the ShipToNumber and NoteDateTime.  

If we are processing the first row we first need to store the values of these columns into the class variables.  For all subsequent rows, we check to see if the value of the current row’s ShipToNumber and NoteDateTime matches what we have stored in the class variables.  If they are the same, then we concatenate the value from the Note column to the ConcatenatedNote variable.  We continue to process rows, concatenating the value of the Note column to ConcatenatedNote for each row, until we come across a row with a new ShipToNumber or NoteDateTime. 

When we find a row with new values, we add a row to the OutputBuffer0 and then assign the values of the OutputBuffer0 rows to the values stored in our class variables.  We then update the class variables with the values from the current row. 

The code is shown below.

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        {
  if ((Row.ShipToNumber == ShipToNumber && Row.NoteDateTime == NoteDateTime) || firstRow)
            {
                CombinedNote += (Row.Note_IsNull ? “” : Row.Note);
                ShipToNumber = (int)Row.ShipToNumber;
                NoteDateTime = Row.NoteDateTime;
                Timestamp = Row.Timestamp;
                User = Row.User;
                firstRow = false;
            }
            else
            {
                Output0Buffer.AddRow();
                Output0Buffer.NoteDateTime = NoteDateTime;
                Output0Buffer.Timestamp = Timestamp;
                Output0Buffer.ShipToNumber = ShipToNumber;
                Output0Buffer.User = User;
                Output0Buffer.CombinedNote.AddBlobData(
                        System.Text.UnicodeEncoding.Unicode.GetBytes(CombinedNote ?? “”));
               
                ShipToNumber = (int)Row.ShipToNumber ;
                NoteDateTime = Row.NoteDateTime;
                Timestamp = Row.Timestamp;
                CombinedNote = (Row.Note_IsNull ? “” : Row.Note);
                User = Row.User;
            }
        }
    }

Just as we needed special processing for the first row, we also need some special processing for the last row.  This involves overriding the FinishOutputs method.  We first must add a row to our OutputBuffer0 and then assign the values of the OutputBuffer0 output columns to the values in our class variables as shown below.

public override void FinishOutputs()
{
     Output0Buffer.AddRow();
     Output0Buffer.NoteDateTime = NoteDateTime;
     Output0Buffer.Timestamp = Timestamp;
     Output0Buffer.ShipToNumber = ShipToNumber;
     Output0Buffer.User = User;
     Output0Buffer.CombinedNote.AddBlobData(
     System.Text.UnicodeEncoding.Unicode.GetBytes(CombinedNote ?? “”));
    
     base.FinishOutputs();
}

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.