SSAS Locking: CommitTimeout and ForceCommitTimeout

There are already plenty of good posts out there on this topic:

As mentioned by Andrew Calvett, it is possible to set the CommitTimeout for a Process command.  Here is how to do it.

<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
  <Command>

    <Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <Type>ProcessFull</Type>
      <Object>
        <DatabaseID>AdventureWorks</DatabaseID>
        <DimensionID>Date_d2c7ec3d-c72c-435d-bd43-8283714cc2dd</DimensionID>
      </Object>
    </Process>

  </Command>
  <Properties>
    <PropertyList>
       <CommitTimeout>20000</CommitTimeout>
   </PropertyList>
  </Properties>
</Execute>

 

Setting CommitTimeout to 20 seconds (20000 milliseconds) means it will kick in before the server-level default ForceCommitTimeout of 30 seconds. To try this out, run the following query, which takes about 50 seconds on my laptop.  As soon as the query starts running, execute the process command.  It should rollback the process command and allow the query to run to completion.

DEFINE
MEASURE 'Date'[Bad Measure] =
   COUNTROWS(GENERATE(ALL('Internet Sales'), VALUES('Date'[Day Of Year])))
EVALUATE
ADDCOLUMNS(
   VALUES('Date'[Calendar Year])
   ,"Bad Measure", [Bad Measure]
)

 

Change CommitTimeout to 40 seconds (40000 milliseconds) and the default ForceCommitTimeout of 30 seconds will kick in instead.  The query will fail – instead of the Process command – “because of locking conflicts”.

Advertisements

Multidimensional or Tabular

This post is not a list of multidimensional features unsupported by tabular. This has been documented already by various reliable sources including the following posts, as well as PASS presentations.

First thing I would like to say is I agree that there is lots of work for tabular to catch up to the feature-rich multidimensional. The tabular-model designer inherited from Power Pivot is sluggish for models with lots of tables. The Excel-like DAX formula bar is, to put it politely, annoying. Without saying anything too controversial, MS corporate BI has been playing second fiddle lately.

However, tabular does make sense for many customers today. For most customers, having fast performance is more important than the unsupported features – which invariably either have “workarounds” or are fringe use cases.

On the workarounds, if the same functionality can be delivered to the business, they don’t care if we technical people see it as a “workaround” because it’s not delivered the same way we are used to. And the business people are the ones that matter. This applies to many-to-many relationships, parent-child hierarchies, role-playing dimensions (can create multiple instances of same table), and various other items.

For what I’m calling the fringe use cases, the supportability of some of these does not make sense for many customers. Hand a solution to support that uses MDX stored procs, extensive scoped-cell assignments and they will struggle. How many implementations use these features because the developer thought they were cool rather than having any real business need? I think quite a few.

Other use cases may be showstoppers like unary operators and writeback, but not for the majority of implementations.

Scoped-cell assignments are in the potential showstopper list too, but in most cases if calculation logic is pushed to the ETL layer (where it belongs if not one of the strengths of the cube/tabular model like aggregated level calcs, or those which would cause a data explosion problem at the relational level, etc) to avoid the formula engine where possible, then DAX is a pretty capable and powerful language for calculations built into the tabular model.

On the tabular memory limitation, many customers I’ve talked to are worried they won’t fit into memory when they are actually nowhere near the upper limit of what they can relatively easily get on a server (especially when limited to the required data). Also, more memory will only become more viable in the future. For a multi-terabyte data warehouse implementation like a Yahoo.com, then yes but again this is a fringe use case.

As noted elsewhere,

  • When a project chooses multidimensional or tabular, it is not possible to change your mind without starting development again.
  • I think it is widely accepted that Microsoft is more likely to build new features and put future development investment into tabular than multidimensional.

It is a valid statement that a project may need some of the features unsupported by tabular at a later date, which could be a problem. Conversely a project may encounter performance issues with multidimensional that were not anticipated at the start of the project.  One could also argue that, as new features are built into tabular, could be stuck with multidimensional and unable to leverage better tabular capabilities in the future.

Update Nov 13 2013 – post from Marco Russo: Updates about Multidimensional vs Tabular

Currency Conversion in SSAS 2012 (Multidimensional & Tabular)

Here is a copy of an article I wrote on the TechNet wiki.  It  merges the information in my previous posts (here and here) on currency conversion in Analysis Services. This new article is refreshed for SSAS 2012 and covers all the material in one place.

Types of Currency Conversion

As defined by the Business Intelligence Wizard in Multidimensional, there are 3 types of currency conversion.

  • One-to-many.  In this case we store the transactions (for example in the data warehouse) in a single base currency.  Even if they originally took place in different countries, we can convert to a single currency in the ETL.  The cube or tabular model will then convert to the reporting currencies.
  • Many-to-many.  Here we store the transactions in the original, different currencies.  The cube or tabular model now has (roughly) twice as many calculations that need to be performed.  First convert to a single “pivot” currency, and then to the reporting currencies.
  • Many-to-one.  Again (like many-to-many), we store the transactions in the original, different currencies.  However, in this case there is no business requirement to report in different reporting currencies.  We just choose not to utilize the ETL to convert to a base currency.  Instead, the cube or tabular model is used for this purpose.

The most common of these, in a data warehousing environment at least, is one-to-many.

Business Requirement

The primary business requirement is that users can view all transactions in any of the supported reporting currencies.  As a secondary requirement, the user may also be interested in what country/currency the original transaction took place.

Even the secondary requirement can be delivered by one-to-many.  We can achieve this by storing the lineage of the original currency in the model and report on it as required.

This article will therefore focus on one-to-many.  Apart from being the most common form of currency conversion, the techniques used for the other 2 types (many-to-many and many-to-one) are basically variations of the techniques used by one-to-many.

Extra Columns in Currency Dimension Table

As a prerequisite to the examples in this article, the following 2 columns are added to the DimCurrency table in AdventureWorksDW.  LCID is used for multidimensional and FormatString is for tabular.

LCID

The LCIDs are available here: http://www.microsoft.com/globaldev/reference/lcid-all.mspx  

Tip: an easy way to get the format strings is in the tabular-model designer in SSDT (for a dummy temporary project), select Format=Currency and set the currency symbol in the properties window of SSDT.

CurrencyPropertiesWindow

This internally sets the FormatString property to the custom string.  Can then right-click View Code on the .bim file and search for instances of the format string.

<Format Format="Currency" Accuracy="2" xmlns="">
 <Currency LCID="2057" DisplayName="£ English (United Kingdom)" Symbol="£" PositivePattern="0" NegativePattern="1" />
</Format>
 ...
<ddl300_300:FormatString>"£"#,0.00;-"£"#,0.00;"£"#,0.00</ddl300_300:FormatString>

Multidimensional

Consider the following DSV on top of AdventureWorksDW, which includes DimTime, FactInternetSales, FactCurrencyRate and DimCurrency, it should look something like this.

DSV

We are only concerned with one of the 3 relationships between Internet Sales Facts and Date (the one based on OrderDateKey).

Consider the following statements.

  • For a particular date, there will be many exchange rates; one for each of the supported reporting currencies
  • For a particular currency, there will be many exchange rates; one for each of the dates in the date range for which we are loading data.  Exchange rates change over time.
  • Therefore, the correct way to model this is with a many-to-many dimension between Internet Sales and Currency.  This will be shown below when we refer to the Dimension Usage tab.

The only measures in the (simplified) cube are Sales Amount (from Fact Internet Sales) and End Of Day Rate (from Currency Rate).  End Of Day Rate has an AggregateFunction property value of “LastNonEmpty”.  LastNonEmpty is defined as a semi-additive measure.  This means it will sum for all dimensions except Date.  For the Date dimension, it will take the last non-empty child.  For example, the exchange rate we would want to see for a week member in a report would not be the sum of the exchange rate on Monday + exchange rate on Tuesday + exchange rate on Wednesday, …  Instead, we just want the most recent exchange rate (maybe exchange rate on Saturday).

LastNonEmpty

The Date attribute in the Date dimension is the key attribute.  It has a KeyColumns property of DateKey (which is the surrogate key of the DimTime table) and a NameColumn of SimpleDate (what the users see at Date level).

If we use the Dimension Wizard to create the Date dimension, it makes it easy to flag with Type = Date, etc.  This is one of the few cases where having these properties set correctly actually affects cube behavior.  For example, semi-additive measures and some MDX functions like YTD won’t work without Type = Date.

Date attribute

Here are the mappings in the Dimension Usage tab.  They are pretty straightforward.  There is a many-to-many relationship between Currency and Fact Internet Sales.

Relationships

Here is a screenshot of the Currency dimension.  The main points about the Currency dimension are as follows.

  • The KeyColumns property of the Currency attribute is set to CurrencyKey (which is the surrogate key in for the DimCurrency table in AdventureWorksDW).
  • The NameColumn property of the Currency attribute is set to CurrencyName (which is what we want the users to see).
  • The Type property of the dimension (not attribute) is set to Currency.  The only purpose of this (unlike Type=Time that does affect cube behaviour) is to inform client tools in case they want to display currency dimensions differently to regular dimensions.
  • The Type property of the Currency attribute is set to CurrencyName.  Again, this is just to inform client tools.
  • The IsAggregatable property of the Currency attribute is set to False.  This removes the All level for the attribute. We would after all not want to sum the values of different currencies.  500 pounds sterling plus 100,000 cruzeiros does not make any sense.
  • Set the DefaultMember property of the Currency attribute to whatever the Sales Amount values are stored in.  In the case of AdventureWorksDW, it is US Dollars.
  • The ValueColumn property is set to LCID, which is an extra column on the Currency table.  See section below on currency formats for an explanation of this.

Currency Dim

Next we need to perform the conversion calculation itself.  There are various options here, some of which are better than others.

  • Measure expressions
  • MDX script cell assignments (which is how the SSAS Multidimensional BI Wizard does it)
  • MDX script calculated members
  • Performing the conversion in the MDX client queries

Measure expressions are arguably the best way to perform currency conversion in Multidimensional.  They tend to perform better than the other options.  Hence this article will use the measure expressions option to perform the conversion calculation.

Measure Expressions

The Sales Amount measure has the following measure expression: “[Sales Amount] / [End Of Day Rate]”.  The facts are stored in US Dollars and the base currency is US Dollars.  The measure expression is a division rather than a multiplication because this is the way the exchange rates are stored in AdventureWorks.  For example, the exchange rate for GBP is roughly 1.5 in the FactCurrencyRate table.  Therefore, to convert $15 from the pivot currency to GBP, 15 / 1.5 gives us 10.  Doing a multiplication would result in £22.50 (obviously wrong).

Note: measure expressions are performed at leaf level.

Measure Expressions

Back in the Dimension Usage tab, it is worth setting the DirectSlice property of the many-to-many relationship.  Setting it to “([Currency].[Currency].&[100])” means that, when querying Sales Amounts by the base/pivot currency (US Dollars), SSAS has the option to just return the value as it appears in the fact table – i.e. without applying the measure expression.  After all, there is no need to convert US Dollars into US Dollars!  This gives SSAS the option of using DirectSlice as a performance optimization.

We need a tuple with the default member for every enabled attribute in the outer many-to-many dimension.  Incidentally, this is what the Root(<dimension_expression>) function would return, but we can’t use any MDX functions in the DirectSlice property (hence the Functions pane is disabled), so we have to explicitly list each default member in a tuple.

Note: SSAS may choose not to use the DirectSlice optimization (depending on internal algorithms beyond the scope of this article).  Therefore, we still need to ensure the FactCurrencyRate table has an exchange rate of 1 for converting US Dollars into US Dollars – and for every single day for which we have data.  Otherwise, Analysis Services would be looking for an End Of Day Rate to divide by, find null and return null.

DirectSlice

Currency Format

Lastly, we need an MDX script cell assignment (in the Calculations tab) to set the Locale ID for currencies.  This will avoid displaying 500 pounds sterling as “$500”.  For a complete list of the Locale IDs, seehttp://www.microsoft.com/globaldev/reference/lcid-all.mspx 

Language([Currency].[Currency].[United Kingdom Pound]) = 2057;
Language([Currency].[Currency].[Brazilian Real]) = 1046;
Language([Currency].[Currency].[US Dollar]) = 1033;

The above assignments are for illustration purposes only.  The main problem with this approach is maintainability.  If we bring in new currencies in the future, we need to modify the MDX script.  The ideal would be to store them in the DimCurrency table, expose them using the ValueColumn property of the Currency attribute, and use that for the assignment.

Language(([Currency].[Currency].Members, [Measures].[Internet Sales Amount])) =
[Currency].[Currency].CurrentMember.MemberValue;

Note: the ValueColumn property of the dimension can be used as a form of “property bag” to store additional information for each dimension member.

Note: the MDX MemberValue (or Member_Value) function will …

  • If no ValueColumn property is specified, it will return the value from NameColumn
  • If no ValueColumn and NameColumn property, it will return the value from KeyColumn

Now if we run a query we should get back correct numbers with the conversion calculation applied at leaf level, and displayed using foreign currency formats.

Currency format

Note: the values in this screenshot are not the actual numbers that ship with AdventureWorks.

Tabular

The tabular modeling experience is much simpler than multidimensional.  There is nowhere to define the many-to-many relationship, IsAggregatable=False, LastNonEmpty aggregation function, etc.   The tabular modeling experience is basically just a bunch of tables and relationships with fewer options to set properties.  Responsibility for dealing with these features is handed over to DAX.

Tabular model designer

Here is a DAX query that handles (almost) all the features.  Again, instead of “decorating the meta-data of the model” as we did in multidimensional, responsibility is offloaded to DAX.

DEFINE
MEASURE ‘Internet Sales'[Internet Sales] =
SUMX
(
‘Currency Rate’
, CALCULATE( Sum(‘Internet Sales'[Sales Amount]) /
Sum(‘Currency Rate'[End of Day Rate]) )
)
MEASURE ‘Internet Sales'[Internet Sales FORMATTED] =
FORMAT
(
[Internet Sales]
, VALUES(‘Currency'[Format String])
)
MEASURE ‘Internet Sales'[Internet Sales ALL MEMBER DOLLARS] =
IF
(
HASONEVALUE(‘Currency'[Currency Name])
, [Internet Sales FORMATTED]
, [Internet Sales FORMATTED](‘Currency'[Currency Name] = “US Dollar”)
)
EVALUATE
ADDCOLUMNS
(
FILTER
(
VALUES(‘Date'[Calendar Year])
, [Internet Sales] > 0
)
,”US Dollar”, [Internet Sales ALL MEMBER DOLLARS](
‘Currency'[Currency Name] = “US Dollar” )
,”United Kingdom Pound”, [Internet Sales ALL MEMBER DOLLARS](
‘Currency'[Currency Name] = “United Kingdom Pound” )
,”Brazilian Real”, [Internet Sales ALL MEMBER DOLLARS](
‘Currency'[Currency Name] = “Brazilian Real” )
,”Saudi Riyal”, [Internet Sales ALL MEMBER DOLLARS](
‘Currency'[Currency Name] = “Saudi Riyal” )
,”ALL MEMBER”, [Internet Sales ALL MEMBER DOLLARS]
)
ORDER BY ‘Date'[Calendar Year]

Here are some points about this query.

  • All the measures defined could be built into the tabular model for reusability.
  • It is a best practice in DAX to break out calculations into component measures for readability and supportability.  Hence the only measure that would be made visible to the users is “Internet Sales ALL MEMBER DOLLARS” (obviously with a more appropriate name).
  • There are various instances where the shorthand notation for the CALCULATE function is used.  The CALCULATE function allows us to override the filter context.  The following syntax forms are equivalent (the 2nd is the shorthand notation).
    • CALCULATE([Existing Measure],<filter1>,<filter2>…)
    • [Existing Measure](<filter1>,<filter2>…)
  • SUMX is used to create a row context for the rows from the ‘Currency Rate’ table inherited from filter context.  SUMX will iterate each row and apply the conversion calculation at the leaf level.
  • I could not find a way to use LCID in tabular, but we can use the FORMAT function and pass in the FormatString value from the currency table instead.
  • In multidimensional, we can use the following properties to hide the currency all member and choose the default currency of US dollars (summing multiple currencies for the all member obviously doesn’t make sense).
    • IsAggregatable=False removes the all member
    • DefaultMember set to the desired default currency

Given that tabular doesn’t have anything equivalent to these features at this time, we need to check if we are at the all level of the currency dimension using HASONEVALUE(‘Currency’[Currency Name]).  This is equivalent to setting the all member to US dollars.  This also covers the case where a user selects a multi-value filter on currency (for example US dollars and Japanese yen).

Currency Conversion: Formatting Symbols in Tabular

This article on currency conversion was written on my old blog in August 2006.  It still applies today for Multidimensional in 2012.  Update August 17 2013: see revised version for SSAS 2012.  One of the items covered is how to format the currencies so they look like the following rather than all use one currency’s symbol.

I created a couple of extra columns in my currency dimension table.  My examples use the LCID column for multidimensional and FormatString for tabular.

You can get the LCIDs from here.  An easy way to get the format strings is to select Format=Currency and set the currency symbol in the properties window of SSDT.  This internally sets the FormatString property to the custom string.

Multidimensional

Set the currency attribute’s ValueColumn property to the LCID column.

And then the MemberValue function in MDX script to set the Language property.

Language(([Currency].[Currency].Members, [Measures].[Internet Sales Amount])) = [Currency].[Currency].CurrentMember.MemberValue;

Tabular

I couldn’t find a way to use LCID in tabular, but we can use the FORMAT function and pass in the FormatString value from the currency table.

DEFINE
MEASURE 'Internet Sales'[Internet Sales] =
    SUMX
    (
        'Currency Rate'
        , CALCULATE( Sum('Internet Sales'[Sales Amount]) / Sum('Currency Rate'[End of Day Rate]) )
    )
MEASURE 'Internet Sales'[Internet Sales FORMATTED] =
    FORMAT
    (
        [Internet Sales]
        , VALUES('Currency'[Format String])
    )
MEASURE 'Internet Sales'[Internet Sales ALL MEMBER DOLLARS] =
    IF
    (
        HASONEVALUE('Currency'[Currency Name])
        , [Internet Sales FORMATTED]
        , [Internet Sales FORMATTED]('Currency'[Currency Name] = "US Dollar")
    )
EVALUATE
ADDCOLUMNS
(
    FILTER
    (
        VALUES('Date'[Calendar Year])
        , [Internet Sales] > 0
    )
    ,"US Dollar", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "US Dollar" )
    ,"United Kingdom Pound", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "United Kingdom Pound" )
    ,"Brazilian Real", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "Brazilian Real" )
    ,"Saudi Riyal", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "Saudi Riyal" )
    ,"ALL MEMBER", [Internet Sales ALL MEMBER DOLLARS]
)
ORDER BY 'Date'[Calendar Year]

In multidimensional, we can use the following properties to hide the all member and choose the default currency – summing multiple currencies obviously doesn’t make sense.

  • IsAggregatable=False removes the all member
  • DefaultMember set to the desired default currency

Given that tabular doesn’t have anything equivalent to these features at this time, we need to check if we are at the all level of the currency dimension using HASONEVALUE(‘Currency'[Currency Name]).  The above example sets the all member to US Dollars.  This also covers some clever clogs selecting a multi-value filter on currency.

Binding Excel to a custom MDX query supporting PivotTable filters

Chris Webb wrote this post: Binding an Excel table to the results of an MDX query.  In a comment at the bottom, Scott Gall suggested using VBA.  I want to take this one step further to allow custom MDX to be filtered by the filters from the Pivot Table Field List, while not actually using a pivot table (similarly to formulas mode).

This addresses the following limitations of pivot tables/formulas mode …

  • Performance.  MDX generated by pivot tables can be up to 10 times slower than custom MDX.  I haven’t got any hard and fast numbers, but I think the discrepancy is greater in tabular than multidimensional – especially when crossjoining large hierarchies (or nesting in the Pivot Table Field List).
  • Custom calculations that are relevant only to a single report, and therefore don’t warrant being built into the cube/tabular model.  We can tackle this using the OLAP Pivot Table Extensions, but there are still some cases where we might want calculated members on hierarchies, or custom layout of members on axes, etc. for which it would be nice to use custom MDX.
  • Excel formulas on the results.  Formulas mode often works well in this scenario (that’s what it’s for), but
    • Large data sets in formulas mode perform worse than pivot tables, let alone custom MDX.
    • Formulas mode doesn’t work with a variable number of rows depending on filter selection.  Sometimes Excel formulas can be great for things like what-if analysis, etc. in this situation.
  • Multiple charts/grids sharing the same filters.  We can use slicer buttons for this, but we may still be on Excel 2007, or it might just be a report that is better suited to filters than slicer buttons.
  • Endless other customizations.

This Excel file does the above.  Things to note …

  • Don’t get too excited about the code.  It is VBA.
  • It supports multi-value filters.
  • It gets the connection info from an existing connection (Data tab > Connections) called “AdventureWorks”, which you will have to configure.
  • Place custom MDX in the Query tab with the <<Filters>> placeholder in the where clause as per the example.  You can then hide the Query tab from the users.
  • The sample query provided was written against the tabular Adventure Works.  You will have to change it if using the multidimensional Adventure Works or a different database.
  • Check the RefreshButton_Click() macro and change the constants at the top as required.  For example, you will need to change the intFirstReportRow constant if you add more filters.

Currency Conversion in Analysis Services Multidimensional

I wrote this article on my old blog in August 2006 for SSAS 2005, but everything still applies today even for SSAS 2012 in Multidimensional mode.

Update August 17 2013: see revised version for SSAS 2012.

First thing to do: figure out what type of currency conversion you need.  There are 3 types.

  1. MANY-TO-MANY.  This is where the facts are stored in multiple currencies – i.e. amounts for the same measure are in various currencies in the same fact table.  Also, the users might want to report the total amount in different currencies.
  2. MANY-TO-ONE.  The facts are stored in multiple currencies, but this time it is only necessary to ever report the total amount in a single currency – e.g. a corporate currency.
  3. ONE-TO-MANY.   The facts are always stored in one currency only.  However, the users might want to report the total amount in different currencies.

This post focuses on the different options available for One-to-Many.  With One-to-Many, there are 2 options: measure expressions or the currency conversion wizard.

If you create a DSV on top of AdventureWorksDW and include DimTime, FactInternetSales, FactCurrencyRate and DimCurrency, it should look something like this.

The relationship between FactInternetSales and DimCurrency is for the case where your facts are stored in multiple currencies (Many-to-Many and Many-to-One).  To look at One-to-Many, we will delete this relationship.  Also, we will delete the ShipDate and DueDate relationships between FactInternetSales and DimTime, but leave the OrderDate relationship (this post is about currency conversion, not role-playing dimensions!).

We now have the following DSV diagram.

ONE-TO-MANY USING MEASURE EXPRESSIONS

I created a cube on top of this DSV.  Here is a backup of ithttp://blogs.conchango.com/christianwade/CurrencyConversionBackup.zip  You can download, restore and open it using the VisualStudio using the File > Open > Analysis Services Database menu option.

The only measures in it are Sales Amount (from FactInternetSales) and End Of Day Rate (from FactCurrencyRate).  Sales Amount has a FormatString of “Currency”.  End Of Day Rate has an AggregateFunction property value of “LastNonEmpty”.  LastNonEmpty is a semi-additive measure.  We want it to sum for all dimensions except Date.  For the Date dimension, it will take the last non-empty child.  For example, the exchange rate we would want to see for a week member in a report would not be the sum of the exchange rate on Monday + exchange rate on Tuesday + exchange rate on Wednesday, …  Instead, we just want the most recent exchange rate (maybe exchange rate on Saturday).

The Date dimension is very simple.  The Date attribute, which is the key attribute, has a KeyColumns property of TimeKey (which is the surrogate key of the DimTime table) and a NameColumn of FullDateAlternateKey (what the users see at Date level).

I used the Dimension Wizard to create the Date dimension so that it would be flagged with Type = Time, etc.  This is one of the few cases where having these properties set correctly actually affects cube behaviour.  For example, semi-additive measures and some MDX functions like YTD won’t work without Type = Time.

Here are the mappings in the Dimension Usage tab.  They are pretty straightforward.  There is a many-to-many relationship between Currency and Fact Internet Sales.

Here is a screenshot of the Currency dimension.  The main points about the Currency dimension are as follows.

  • The KeyColumns property of the Currency attribute is set to CurrencyKey (which is the surrogate key in for the DimCurrency table in AdventureWorksDW).
  • The NameColumn property of the Currency attribute is set to CurrencyName (which is what we want the users to see).
  • The Type property of the dimension is set to Currency.  The only purpose of this (unlike Type=Time that does affect cube behaviour) is to inform client tools in case they want to display currency dimensions differently to regular dimensions.
  • The Type property of the Currency attribute is set to CurrencyName.  Again, this is just to inform client tools.
  • The IsAggregatable property of the Currency attribute is set to False.  This removes the All level for the attribute. We would after all not want to sum the values of different currencies.  500 pounds sterling plus 100,000 cruzeiros equals 100,500 of what currency?  Monopoly money?
  • Set the DefaultMember property of the Currency attribute to whatever the Sales Amount values are stored in.  In the case of AdventureWorksDW, it is US Dollars.
  • The attribute relationship between Currency and Currency Alternate Key has its Cardinality property set to One. This is because, for a particular Currency, there can be only one Currency Alternate Key – i.e. they have a one-to-one relationship.  This improves efficiency in aggregation because the numbers will always be the same (e.g. £500 for “Great Britain Pounds” will always result in £500 for “GBP”).  Analysis Services will therefore not bother figuring out the aggregate values for Currency Alternate Key.  It will simply re-use those of Currency.
  • Set the AttributeHierarchyEnabled property of the Currency Alternate Key attribute to False.  If it is not set, Analysis Services will not allow deployment of the project because of the attribute relationship with Currency and that Currency has IsAggregatable = False.

Here is the measure expressions bit.  The Sales Amount measure has the following measure expression: “[Sales Amount] / [End Of Day Rate]”.  The facts are stored in US Dollars and the “pivot currency” is US Dollars.  The pivot currency is the currency the exchange rate values convert from.  The measure expression is a division rather than a multiplication because this is the way the exchange rates are held.  For example, the exchange rate for GBP is roughly 1.5 in the FactCurrencyRate table.  Therefore, to convert $15 from the pivot currency to GBP, 15 / 1.5 gives us 10.  Doing a multiplication would result in £22.50 (obviously wrong).  Note: measure expressions are done at leaf level.

It is worth going back into the Dimension Usage tab and setting the DirectSlice property of the many-to-many relationship.  Setting it to “([Currency].[Currency].&[100])” means that, when querying Sales Amounts by the base/pivot currency (US Dollars), Analysis Services will just return the value as it appears in the fact table – i.e. without applying the measure expression.  After all, there is no need to convert US Dollars into US Dollars!  If we did not set DirectSlice, we would have to ensure that the FactCurrencyRate table has an exchange rate of 1 for converting US Dollars into US Dollars (and for every single day for which we have data).  Otherwise, Analysis Services would be looking for an End Of Day Rate to divide by, find null and return null.  It should also perform better when querying by USD with DirectSlice populated correctly because it doesn’t have to bother with the conversion calculation at all.  So what we want is just a tuple with the default member for every enabled attribute in the outer many-to-many dimension.  Incidentally, this is what theRoot(<dimension_expression>) function would return, but we can’t use any MDX functions in the DirectSlice property (hence the Functions pane is disabled), so we have to explicitly list each default member in a tuple.

Lastly, we will insert some MDX in the Calculations tab that sets the Locale ID for currencies.  This will avoid displaying 500 pounds sterling as “$500” (goodness gracious!).  For a complete list of the Locale IDs, see http://www.microsoft.com/globaldev/reference/lcid-all.mspx

Language([Currency].[Currency].[United Kingdom Pound]) = 2057;
Language([Currency].[Currency].[Brazilian Real]) = 1046;
Language([Currency].[Currency].[US Dollar]) = 1033;

These assignments are for illustration purposes only.  The main problem with this approach is maintainability.  If we bring in new currencies in the future, we need to modify the MDX script.  The ideal would be to store them in the DimCurrency table, expose them using the ValueColumn property of the Currency attribute, and use that for the assignment.  Some may prefer to go the member property route, but I think this is a good use case for the ValueColumn property.

Language(([Currency].[Currency].Members, [Measures].[Sales Amount])) =
[Dim Currency].[Currency].CurrentMember.MemberValue;

Let’s build and deploy!  Now let’s browse!

On the face of it, these numbers seem OK.  29,358,677 is roughly 1.5 times 19,685,311.  But let’s double check. Copying and pasting into Excel reveals that this is not the case.  There is actually a discrepancy of £784,195!  Why is that?  Well, this is actually the behaviour that we want …

If you remember, measure expressions are done at leaf level.  This calculation is, as George Spofford would say, “non-commutative”.  Addition/subtraction combined with multiplication/division will result in different numbers depending on whether the multiplication/division is done at leaf level or the aggregated level.  Analysis Services has done the currency conversion at leaf level – i.e. at the day that each transaction actually took place.  This is of course much more accurate.

So let’s test this as well.  Drilling into Date reveals the following.

Copying and pasting the Date-level data into Excel and applying the multiplication reveals that the numbers are indeed calculated correctly at leaf level.

For one-to-many currency conversion, I prefer using measure expressions rather than the currency conversion wizard.  As shown by this post – http://blogs.conchango.com/christianwade/archive/2006/07/25/4256.aspx  – measure expressions are the most performant way of doing this kind of calculation.  The wizard generates MDX that does the calculation at runtime, so it is not quite as performant for large volumes.  Furthermore, virtually all the work we have just gone through to enable measure-expression currency conversion would also have to be done prior to running the wizard (with the exception of the measure expression itself).  Anyway, for completeness, let’s take a look at how the currency conversion wizard does it.

ONE-TO-MANY USING CURRENCY CONVERSION WIZARD

Picking up where we left off, just delete the measure expression and run the wizard.  As I said, pretty much all the other stuff we had to do to enable measure expressions has to be done prior to running the wizard anyway.

From the Cube Structure tab, select Cube > Add Business Intelligence.

Select “Define Currency Conversion” and click Next.

Fill in the next page in the wizard as shown here.  Specify which measure group contains the exchange rates.  The pivot currency, as discussed above, is US Dollars.  “1.5 US Dollars per 1 United Kingdom Pound” sounds right, so let’s go with that.

Select Sales Amount as the measure(s) we want to convert.

Specify One-to-many currency conversion.

Now it wants us to specify the “reporting currencies”.  It will actually create another currency dimension called “Reporting Currency”, which will be based on this selection.  I would imagine that most cubes that only need one-to-many currency conversion will have the base Currency dimension purely for the purpose of viewing the values in foreign currencies! Adding another currency dimension can be a little overkill in my opinion.  I selected United Kingdom Pound and Brazilian Real for my reporting currencies.

The last page shows us what changes will be made to the cube/DSV.  Click Finish.

Let’s take a look at what it actually did.  Firstly, we have a new entity in our DSV called Reporting Currency.  It is basically the same as the FactCurrencyRate, but filtered on the pivot currency and the other currencies we selected as reporting currencies.  Interestingly, it is not related to any other entity in the DSV.

The wizard also created a dimension called Reporting Currency that is based on the Reporting Currency entity in the DSV.  It is very similar to our Currency dimension.  The Currency attribute has a DefaultMember property of US Dolllars, its IsAggregatable property is set to False, etc., etc.  In fact the only difference of any significance is that it has not set the Cardinality property of the attribute relationship to One.

And here is the MDX script it generated.  Note: there is a bug in this script.  If you are using a Date dimension that is called something different to the base dimension name (e.g. Order Date vs. Date), you will have to replace “Scope( Leaves([Date])” with “Scope( Leaves([Order Date])”.

     // <Currency conversion>
            // Currency conversion wizard generated script.      
            // Currency conversion generated on: 24 August 2006 15:36:38
            // by user: Conchango2 
            // Currency conversion type: OneToMany   
            // Selected members to be converted: Sales Amount    
            // Please be aware that any changes that you decide to make to it may be overridden the next time you run the Currency Conversion wizard again. 

            // All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension
            Scope ( { Measures.[Sales Amount]} );
                  Scope( Leaves([Date]) ,
                        Except([Reporting Currency].[Currency].[Currency].Members, [Reporting Currency].[Currency].[Currency].[US Dollar]));

                // This section overrides the Pivot Currency values with the Converted value for each selected measures/account members/account type members needing to be converted with Measure rate End Of Day Rate
                // LinkMember is used to reference the currency from the source currency dimension in the rate cube. 
                Scope( { Measures.[Sales Amount]} );
                       This = [Reporting Currency].[Currency].[US Dollar] / (Measures.[End Of Day Rate],LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) ;
                End Scope;   

                  End Scope; // Leaves of time and non pivot currency  
            End Scope; //
         // End of the currency conversion wizard generated script
      // </Currency conversion>

Having done some (thorough) scoping, the line of code that is of real interest is

This = [Reporting Currency].[Currency].[US Dollar] / (Measures.[End Of Day Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) ;

This line is doing the division in a similar way to the measure expression.  However, it is using the LinkMember function to do a runtime link between Currency and Reporting Currency based on the member name.  This is effectively just replacing the Currency dimension with the Reporting Currency dimension for reporting purposes.

SUMMARY

As I said, if all you have is a one-to-many currency conversion requirement, I think the measure expressions approach is more elegant.  It is optimized to perform a little better for large volumes (despite the fact that cell assignments in MDX script do perform very well).  Also, there is only one currency dimension (instead of Currency and Reporting Currency).  For many cubes that just require one-to-many currency conversion, this is a simpler model.  Plus a lot of the work you have to do to use the measure expressions approach has to be done anyway in order to run the wizard.  This is in contrast to the Time Analysis wizard, which I think adds great value.  I think the currency conversion wizard adds more value for many-to-many currency conversion.  In this case, you would invariably want the 2 currency dimensions.