Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Tuning the Currency Conversion Calculations created by the Add Business Intelligence Wizard

with 73 comments

I see the ‘Add Business Intelligence’ wizard in BIDS as a bit of a missed opportunity on Microsoft’s part: it was a great idea to have functionality that would automate difficult stuff like adding MDX calculations to a cube, and it’s a shame that most of the MDX code it generates is so rubbish. Take, for example, the MDX currency conversion calculations that it creates. If you’re using Enterprise Edition the best way to do currency conversion is not to use MDX at all but to use measure expressions, but if you have Standard Edition you have no choice but to use MDX to do your currency conversion and if you use the calculations the wizard produces you’d probably think that this type of calculation has to be painfully slow. That’s not true, though – and in this post I’ll show you how to replace the currency conversion calculations created by the wizard with some that are not only much simpler but also much, much faster.

Let’s use a simple cube to illustrate this. In the Adventure Works database I created a cube with two measure groups: one based on the Internet Sales fact table, the other based on the Currency Rates fact table. I then added the Currency dimension and the Date dimension (joining on OrderDateKey in the Internet Sales table), and ran the ‘Add Business Intelligence’ wizard to add many-to-many currency conversion to the cube. Here’s what my Dimension Usage tab looked like after I’d run the wizard (note that it added a new Reporting Currency dimension to the cube):

image

And here’s the MDX that it adds to the MDX Script:

// <Currency conversion>
       
        // Currency conversion wizard generated script.
        // Currency conversion generated on: 11 January 2011 21:05:19   
        // by user: Chris   
        // Currency conversion type: ManyToMany   
        // 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.   
   
        // This is the Many to One section   
        // All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension   
        Scope ( { Measures.[Sales Amount]} );
            Scope( Leaves([Date]) ,
                [Reporting Currency].[USD],   
                Leaves([Source Currency]));
       
               
             
              // Convert Local value into Pivot currency for selected Measures that must be
              //converted with Measure rate [Average Rate]
              Scope( { Measures.[Sales Amount]} );
                    
               This = [Reporting Currency].[Local] / Measures.[Average Rate];
   
              End Scope;
   

            End Scope;   
       
            // This is the One to Many section
            // All currency conversion formulas are calculated for
            //the non pivot currency and at leaf of the time dimension   
            Scope( Leaves([Date]) ,    
                Except([Reporting Currency].[Source Currency Code].[Source Currency Code].Members,
                {[Reporting Currency].[Source Currency Code].[Source Currency Code].[USD],
                [Reporting Currency].[Source Currency Code].[Source Currency Code].[Local]}));
       
               
           
            // This section overrides the local values with the Converted
            // value for each selected measures needing to be converted with Measure rate [Average Rate]…   
            // LinkMember is used to reference the currency from the
            // source currency dimension in the rate cube.
            Scope( { Measures.[Sales Amount]} );
                   This = [Reporting Currency].[Source Currency Code].[USD] * (Measures.[Average Rate], LinkMember([Reporting Currency].[Source Currency Code].CurrentMember, [Source Currency].[Source Currency Code])) ;
            End Scope;   
   
       
            End Scope; // Leaves of time, all reporting currencies but local and pivot currency   
        End Scope; // Measures

        // End of the currency conversion wizard generated script
    // </Currency conversion>

Scary, eh? I won’t explain what it does in detail, but basically it does the following:

  1. Converts the various local currency values held in the Internet Sales fact table into US Dollars
  2. Converts these US Dollar amounts into whatever Reporting Currency the user has selected

It’s a lot of code for something so straightforward, and the reason why so complex is because it needs to use the Currency Rates measure group for both sets of currency rates: from local currency to US Dollars, and from US Dollars to reporting currency. As we all know overly-complex code is usually poorly-performing code, and in this case the use of LinkMember in particular is a killer; also there are no checks for situations where exchange rate values don’t exist, which is a fairly basic mistake.

Take the following query:

SELECT [Date].[Calendar Year].MEMBERS ON 0,
NON EMPTY
[Reporting Currency].[Source Currency Code].MEMBERS ON 1
FROM [CurrencyTestWizard]
WHERE([Measures].[Sales Amount])

It displays all Years on columns and all Reporting Currencies on rows for the Sales Amount measure, so it’s doing quite a lot of work. On my quite beefy laptop, using the cube I’d just created, it ran in just under 9 seconds on a cold cache to give me this:

image

We can do a lot better than 9 seconds though. The first step to optimising this is, as always, to push complexity out of the MDX and back into the cube design. Given that a fact table containing exchange rate values is usually pretty small relative to other fact tables, what I decided to do was to add a second measure group to my cube based on exactly the same Exchange Rate fact table, use the original measure group for the conversion from local currency to US Dollars and then the new one for the conversion from US Dollars to reporting currency. This did increase both the size of the cube and the amount of time taken to process it, but only by a tiny amount.

BIDS didn’t let me create a second measure group from a fact table that was already in use, so I had to create a new named query in BIDS that did a SELECT * from the Exchange Rate fact table and then use that. Having done this, I was able to set up my Dimension Usage as follows, with Reporting Currency joining to this new measure group:

image

Now, I could replace the code above with the following MDX:

SCOPE({[Measures].[Sales Amount]});
    SCOPE([Date].[Date].[Date].MEMBERS);
        SCOPE([Source Currency].[Source Currency Code].[Source Currency Code].MEMBERS);
            SCOPE(EXCEPT([Reporting Currency].[Source Currency Code].[Source Currency Code].MEMBERS
                        ,{{[Reporting Currency].[Source Currency Code].&[2147483647]}}));

                THIS =  IIF([Measures].[Average Rate - Reporting Currency Rates]=0
                            , NULL
                            ,IIF([Measures].[Average Rate]=0
                                , NULL
                                ,([Measures].[Sales Amount] / [Measures].[Average Rate]))
                                * [Measures].[Average Rate - Reporting Currency Rates]);
            END SCOPE;
        END SCOPE;
    END SCOPE;
END SCOPE;

What I’m doing here is using a single set of nested scoped assignments to perform the calculation at the leaf level of the Date, Source Currency and Reporting Currency dimensions. With two different measure groups containing exchange rates there’s no need for the use of LinkMember – I just use two different measures for each step in the conversion – and I make sure I don’t bother doing any conversions where exchange rate values don’t exist.

When I ran my test query, I got exactly the same results but the query ran in around 0.5 seconds on a cold cache – 18 times faster than on the original version of the cube! It just shows you what a few tweaks to your cube design and MDX can achieve, doesn’t it? It’s a shame that MS didn’t invest a bit more time on the ‘Add Business Intelligence’ wizard to improve it – it would probably have improved the quality of many implementations no end. In the future, with PowerPivot and BISM being focused so much on ‘self-service’ BI, I think something similar to the wizard is a ‘must have’ to help users create complex DAX calculations, and I hope MS realise that functionality like this can be key to the overall success of a product.

Written by Chris Webb

January 12, 2011 at 8:03 am

Posted in Analysis Services, MDX

Tagged with

73 Responses

Subscribe to comments with RSS.

  1. [...] of time taken to process the cube and its size on disk. I used the same technique in my recent post on improving the performance of currency conversion, and indeed it’s a technique that I have used [...]

  2. this is interesting i only have a one-2-many currency conversion. how would this work in that scenario.

    james taylor

    January 25, 2011 at 9:17 am

    • It must be along the same lines but i can’t quite work out how to optimise the one-2-many script the bi wizard creates. They are pretty similar and im guessing i don’t need to create a second measure group.

      Any help would be greatly apprecited.

      James Taylor

      March 30, 2011 at 3:53 pm

  3. Excellent point Chris, I am currently putting together a demo cube for an International Coffee franchise who require currency rate conversions in their reports. Thanks!

    H.

    Harry Miller

    February 2, 2011 at 10:21 am

  4. If you had multiple measures that you wanted to apply the conversion to, could you use a single calculation for it?
    ([Measures].currentmember / [Measures].[Average Rate]))
    * [Measures].[Average Rate - Reporting Currency Rates])

    Scott

    March 15, 2011 at 3:08 pm

  5. Chris, would it be possible to post a sample of the above.

    Not sure what you did on setting up your cube against the Adventure Work DW.

    Yours,

    Ken

    Ken

    May 10, 2011 at 4:00 pm

    • I’m not sure I’ve got the sample any more… sorry. But I didn’t do anything complicated for the original cube: I just used the existing SSAS Adventure Works database and created a new cube as detailed above, using the existing Source Currency and Date dimensions.

      Chris Webb

      May 10, 2011 at 11:00 pm

  6. Hi Chris

    Thanks for posting this, I will to optimize my cube with this.

    In addition to understanding the currency conversion I have encountered a problem that I hope you can help me resolve.

    When working with finance measures and the balance statement the balance (Assets and liabilities) needs to be calculated to ultimo exchange rate.

    In my solution, I have a Finance Fact table, and a fact table containing the currency exchange rates per day. the exchange rate table contains three currencies: USD, EUR, and GBP.

    The measure “Amount To Date” is calculated as
    Sum(Periodstodate([Date]. [Date Hierarchy]. [date], [Date]. [Date Hierarchy]. Currentmember),[Measure].[Amount])

    This measure sums up all the postings on the GL Accounts which makes the balance for a specific period.
    Now I would like to convert this measure with the ultimo exchange rate(the exchange rate existing on the last day of a specific selection, and I’m really having problems with this.

    I need to make a many-to-many currency conversion.

    Here’s my logic so far: I have outcommented the Leaves([date])
    this is done to make sure it doesn’t calculate per Date, since I want to calculate only the exchange rate existing on the last day of the selected period.

    and I have made a measure that should find the last exchange rate within the selected period:
    sum(LastPeriod([Date].[Date Hierarchy].[Date], [Date].[Date Hierarchy].Currentmember),Measures.[Exchange rate]

    //

    // Currency conversion wizard generated script.
    // Currency conversion generated on: 18 October 2011 14:09:23
    // by user: Anders
    // Currency conversion type: ManyToMany
    // 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.

    // This is the Many to One section
    // All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension
    Scope ( { Measures.[Amount]} );
    Scope( //Leaves([Date]) , — I have removed this.
    [Reporting Currency].[EUR],
    Leaves([Currency]));

    // Convert Local value into Pivot currency for selected Measures that must be
    //converted with Measure rate [Average Rate]
    Scope( { Measures.[Amount]} );

    This = [Reporting Currency].[Local] / sum(LastPeriod([Date].[Date Hierarchy].[Date], [Date].[Date Hierarchy].Currentmember),Measures.[Exchange rate]);

    End Scope;

    End Scope;

    // This is the One to Many section
    // All currency conversion formulas are calculated for
    //the non pivot currency and at leaf of the time dimension
    Scope( //Leaves([Date]) , — I have removed this.
    Except([Reporting Currency].[Currency Code].[Currency Code].Members,
    {[Reporting Currency].[Currency Code].[Currency Code].[EUR],
    [Reporting Currency].[Currency Code].[Currency Code].[Local]}));

    // This section overrides the local values with the Converted
    // value for each selected measures needing to be converted with Measure rate [Average Rate]…
    // LinkMember is used to reference the currency from the
    // source currency dimension in the rate cube.
    Scope( { Measures.[Amount]} );
    This = [Reporting Currency].[Currency Code].[EUR] * sum(LastPeriod([Date].[Date Hierarchy].[Date], [Date].[Date Hierarchy].Currentmember),Measures.[Exchange rate]), LinkMember([Reporting Currency].[Currency Code].CurrentMember, [Currency].[Currency Code])) ;
    End Scope;

    End Scope; // Leaves of time, all reporting currencies but local and pivot currency
    End Scope; // Measures
    // End of the currency conversion wizard generated script
    //

    I hope you can help me, I can provide more information if this is not enough.

    Thanks

    Anders

    schou

    October 18, 2011 at 7:42 pm

  7. Error in the first post:
    The measure “Amount To Date” is calculated like this:
    Sum(Periodstodate([Date]. [Date Hierarchy]. [date], [(ALL)]. [Date Hierarchy]. Currentmember),[Measure].[Amount])

    schou

    October 19, 2011 at 7:01 am

    • Are you using Enterprise Edition? If so then the easiest thing to do would be to use the LastNonEmpty aggregate function for your Exchange Rate measure rather than using MDX – it will be much faster.

      Apart from that, can you tell me what exactly isn’t working?

      Chris Webb

      October 19, 2011 at 11:02 pm

      • Hi Chris

        first of all, thanks for your quick response to my question.

        unfortunetly it is not a Enterprise Edition, it is a standard edition SQL 2008 R2.

        What isn’t working.

        Asume this:
        Amount = 5000 USD in 2010
        Amount = 7000 USD in 2011

        then Amount To Date is
        Amount To Date = 5000 USD in 2010
        Amount To Date = 12000 USD in 2011

        Exchange Rate in 2010: 1 EUR = 1.3 USD
        Exchange Rate in 2011: 1 EUR = 1.5 USD

        First of all when converting to EUR the Amount To Date is wrong when I select the Date.Year 2011
        it shows (5000 / 1,3) + (7000 / 1,5) = 8512,8 EUR

        it should show (5000+7000) / 1,5) = 8000 EUR

        The problem is that it doesn’t use the last Exchange rate available within the daterange i have selected.

        let me know if you need more info

        thanks

        Anders

        schou

        October 20, 2011 at 6:23 am

      • This could be a solve order issue. Can you move the definition of your Amount to Date calculated measure to the very end of the MDX Script? It sounds like you have it before your currency conversion calculation, which means it will be evaluated first.

        Chris Webb

        October 20, 2011 at 4:55 pm

  8. Hi Again Chris

    The Amount To Date measure was before the currency conversion however when I moved it to be calculated after the Currency Conversion it still shows the same values

    schou

    October 20, 2011 at 5:29 pm

    • I would like to try with , SOLVE_ORDER however I do not know how to apply this to the currency conversion scope. I only know how to apply it ot a calculated measure

      schou

      October 20, 2011 at 5:37 pm

      • If moving the position of the calculation in the script doesn’t change the result, then it isn’t a solve order problem. It might be easier to take this offline – can you send me a copy of your BIDS solution? You can find my contact details at http://www.crossjoin.co.uk

        Chris Webb

        October 24, 2011 at 8:26 am

  9. Chris,

    as always thanks for the great post. i did have a question on the except clause and the value that you are excluding, [Reporting Currency].[Source Currency Code].&[2147483647]. is this the unknown member, the usd member, or something else.

    i was able to get this to work, but i ended up reversing some of the calculations because how i interpretted the local currency and reporting currency as well as the values being stored in the local currency.
    my formula looks like this,
    scope ({[Measures].[M1]};
    scope ([Date].[Date].[Date].members);
    scope ([Local Currency].[Currency Code].[Currency Code].members);
    scope (except
    ([Reporting Currency].[Reporting Currency].[Reporting Currency].members
    ,{{[Reporting Currency].[Reporting Currency].&[-2]}}));– -2 works –local amount
    this = iif([Measures].[Average Rate - Reporting Currency Rates] = 0
    ,NULL
    ,iif([Measures].[Average Rate]=0
    ,NULL
    ,([Measures].[M1] * [Measures].[Average Rate])
    * [Measures].[Average Rate - Reporting Currency Rates]));
    end scope;
    end scope;
    end scope;

    my parens are also different as i got errors with closing the parans correctly.

    Robert

    February 20, 2012 at 9:35 pm

    • Hi Robert,

      Off the top of my head it’s the ‘local currency’ member that I’m excluding, so you can see values in their original currency. Looks like you’re doing the same thing in your code too.

      Chris

      Chris Webb

      February 20, 2012 at 10:15 pm

  10. okay, thanks that helps.

    any insight on the difference in the parens(). i am leaving performing ((M1*AR)/(AR-RCR) all in the closing iif.

    -robert

    Robert

    February 20, 2012 at 11:23 pm

    • Do you mean you’re still getting errors? I can see a missing closing round bracket at the end of this line:
      scope ({[Measures].[M1]};

      Chris Webb

      February 21, 2012 at 10:07 am

      • I am not getting errors, that was a typo on my part..
        here is where the difference is between what you have done and what i ended up doing.

        here is my code in the main iif block..the key is the “))” parens at the end.
        this = iif([Measures].[Average Rate - Reporting Currency Rates] = 0
        ,NULL
        ,iif([Measures].[Average Rate]=0
        ,NULL
        ,([Measures].[M1] * [Measures].[Average Rate])
        * [Measures].[Average Rate - Reporting Currency Rates]));

        here is the code that you have and it seems that you are closing off the calculation before i am.. if you notice you only have a single paren at the end “)”.
        THIS = IIF([Measures].[Average Rate - Reporting Currency Rates]=0
        , NULL
        ,IIF([Measures].[Average Rate]=0
        , NULL
        ,([Measures].[Sales Amount] / [Measures].[Average Rate]))
        * [Measures].[Average Rate - Reporting Currency Rates]);

        As usual your code compiles and runs, i am just a little short on what is happening with the parens.
        my interpretation is this…
        1.) “This” is scoped to the [Sales Amount] and this is being divided by the [Average Rate]
        2.) I can only assume that “* [Average Rate - RCR]” is then applied to the just modified “This” ([Sales Amount]).

        if my description is accurate then i believe we are using the dimensions in different context. in order for e to get my code to work properly, i needed to do the following, [Sales Amount] * [Average Rate], the average rate in this case is just the base currency to USD. I then take the above and divide by the Reporting Currency.

        the end result is the same either dividing or multiplying first. my main question is centered around the difference in the use of the parens “(” and how “This” is interpretted.

        thanks,
        robert

        Robert

        February 21, 2012 at 2:28 pm

      • Hi Robert,

        I don’t think there’s any substantial difference between our calculations – the difference in where the brackets close doesn’t make any difference to the calculations as far as I can see. The brackets here are controlling the order that the calculation is being done, and have nothing to do with how we’re using dimensions.

        Chris

        Chris Webb

        February 21, 2012 at 2:41 pm

  11. okay, thanks.

    in the end as always thanks for the great post and i was able to get this to work.

    i used this along with the ME for the 1 to many conversion work.

    Robert

    February 21, 2012 at 3:00 pm

  12. Chris,
    have you run the results above without having the reporting or billing currency present. i believe a catch for the all member to at least translate to the usd or the default would be necessary?

    -robert

    Robert

    February 28, 2012 at 6:41 pm

    • Chris,

      just wanted to post, that one does not need to do anything different to the general structure discussed. it was a user error on operatoin order.

      -robert

      Robert

      February 29, 2012 at 4:28 am

      • Ah, ok. I was going to say that since there shouldn’t be an All Member on any of the currency dimensions (they should have IsAggregatable set to False) then no extra code is needed.

        Chris Webb

        February 29, 2012 at 7:28 am

  13. It is very usefull thanks.
    I have a small question.

    In your approach you use [Measures].[Sales Amount] in your script.

    THIS = IIF([Measures].[Average Rate - Reporting Currency Rates]=0
    , NULL
    ,IIF([Measures].[Average Rate]=0
    , NULL
    ,([Measures].[Sales Amount] / [Measures].[Average Rate]))
    * [Measures].[Average Rate - Reporting Currency Rates]);

    But wizard script doesnt have MeasureName in the script.

    Scope( { Measures.[Sales Amount]} );
    THIS = [Reporting Currency].[Source Currency Code].[USD] * (Measures.[Average Rate], LinkMember([Reporting Currency].[Source Currency Code].CurrentMember, [Source Currency].[Source Currency Code])) ;
    End Scope;

    The question is. In Wizard script we can use more that one MeasureName. But in your script we have to create SCOPEs for every meaure.

    Is it possible to make your script multi meauser friendy.
    i want to use
    Scope( { Measures.[Sales Amount],Measures.[Sales Amount With Tax],Measures.[Discount Amount] } );
    in the first SCOPE.
    Is it possible?
    Thank you.

    cemuney

    September 6, 2012 at 10:06 am

    • Yes, it should be possible – you just need to include all the measures in the scope statement (in the way you do in your post) and then use Measures.Currentmember inside the actual calculation.

      Chris Webb

      September 6, 2012 at 1:53 pm

      • oops.
        ok it works thank you very much.

        cemuney

        September 6, 2012 at 2:00 pm

  14. Sorry there becomes another problem.
    I have to create ROLES for users who will see only the SalesQuantities not the Amounts.
    I create a new Role and apply Dimension security and Add [XXX\ALogin] for this Roles.

    Measures
    Measures
    Allowed

    Measures

    { [Measures].[SalesQuantity],[Measures].[ExchangeRate] }

    There is no problem if i dont Add Currency Conversion Script below.

    //Currency Conversion
    Scope (
    { [Measures].[CompanySales],[Measures].[CompanySalesWithTax]
    });
    SCOPE (Leaves([Date]),Except([DimCurrency].[CurrencyCode].[CurrencyCode].Members, [DimCurrency].[CurrencyCode].[CurrencyCode].[USD]));
    This = [Measures].CurrentMember /ValidMeasure([Measures].[ExchangeRate]);
    END SCOPE;
    END SCOPE;

    [XXX\ALogin] only see Sales Quantities. But After adding the Currency Conversion Script Excel show error

    [MdxScript(V3) (1170, 13) The ‘[CompanySales]‘ member was not found in the cube when the string, [Measures].[CompanySales], was parsed.]

    When i check the Profiles Full Message is:

    MdxScript(V3) (1170, 13) The ‘[CompanySales]‘ member was not found in the cube when the string, [Measures].[CompanySales], was parsed. Leaves function for attributes with mixed granularity in different measure groups is not supported. MdxScript(V3) (1174, 2) Leaves function for attributes with mixed granularity in different measure groups is not supported. The END SCOPE statement does not match the opening SCOPE statement. MdxScript(V3) (1177, 2) The END SCOPE statement does not match the opening SCOPE statement. The END SCOPE statement does not match the opening SCOPE statement. MdxScript(V3) (1178, 1) The END SCOPE statement does not match the opening SCOPE statement.

    I understand that [XXX\ALogin] dont have right to see [CompanySales] mesaures.

    * But How will i use Currency Conversion and MeasureBased Security together.

    Thanks
    Cem Üney

    cemuney

    September 12, 2012 at 9:46 am

    • The first thing to do is to replace Leaves([Date]) with a reference to the lowest level of the key attribute of the Date dimension. So instead of

      SCOPE (Leaves([Date]),Except([DimCurrency].[CurrencyCode].[CurrencyCode].Members, [DimCurrency].[CurrencyCode].[CurrencyCode].[USD]));

      you’d say

      SCOPE ([Date].[Date].[Date].members,Except([DimCurrency].[CurrencyCode].[CurrencyCode].Members, [DimCurrency].[CurrencyCode].[CurrencyCode].[USD]));

      (I’m assuming that the key attribute of your Date dimension is also called Date)

      Chris Webb

      September 12, 2012 at 10:01 am

      • ok thank you i changed. now error is.
        MdxScript(V3) (1169, 7) The ‘[CompanySales]‘ member was not found in the cube when the string, [Measures].[CompanySales], was parsed. The END SCOPE statement does not match the opening SCOPE statement. MdxScript(V3) (1174, 1) The END SCOPE statement does not match the opening SCOPE statement.

        cemuney

        September 12, 2012 at 10:13 am

      • So is [CompanySales] not accessible by the role? If so, then this post will help: http://cwebbbi.wordpress.com/2008/01/22/dimension-security-tips-tricks-and-problems/

        Chris Webb

        September 12, 2012 at 10:15 am

      • Yes [CompanySales] and [CompanySalesWithTax] is not accessible by the Role.
        i changed the script. Now every login can connect the cube without error.
        I hope there wont be any performance problem.
        Thank you very much again.

        CREATE SET CURRENTCUBE.[SET4Currency]
        AS iif(ISERROR(STRTOMEMBER(“[Measures].[CompanySales]“)), {}
        , {Measures.[CompanySales],Measures.[CompanySalesWithTax] } );

        //Currency Conversion
        Scope (SET4Currency);
        SCOPE ([Date].[Day].[Day].members,Except([DimCurrency].[CurrencyCode].[CurrencyCode].Members, [DimCurrency].[CurrencyCode].[CurrencyCode].[TRY]));
        This = IIF(ValidMeasure([Measures].[ExchangeRate]) = null,null,
        [Measures].CurrentMember /ValidMeasure([Measures].[ExchangeRate]));
        END SCOPE;
        END SCOPE;

        cemuney

        September 12, 2012 at 12:02 pm

  15. %DimensionPermission%
    %CubeDimensionID%Measures%/CubeDimensionID%
    %Description%Measures%/Description%
    %Read%Allowed%/Read%
    %AttributePermissions%
    %AttributePermission%
    %AttributeID%Measures%/AttributeID%
    %AllowedSet%
    { [Measures].[SalesQuantity],[Measures].[ExchangeRate] }
    %/AllowedSet%
    %/AttributePermission%
    %/AttributePermissions%
    %/DimensionPermission%

    cemuney

    September 12, 2012 at 9:50 am

  16. Nice work Chris,

    In your example, you scope a single measure (SCOPE({[Measures].[Sales Amount]});)

    Is there a way to scope all measures that are formatted as currency?

    Regards, Martijn

    Martijn Sandbergen

    October 25, 2012 at 7:45 am

    • There’s a comment from earlier this year (in reply to cemuney) above that deals with how to scope on multiple measures. Unfortunately there’s no way of getting a set of measures that are formatting a particular way, so you’ll have to list them all manually.

      Chris Webb

      October 25, 2012 at 9:53 am

  17. Hi Chris,
    Thanks for this and other blogs which have helped us overcome many issues in creating cubes for the first time.The requirement is that we need to report on measures independently by three currencies Settlement & Original & Reporting and show Settlement & Original Currency values in Reporting. In effect there would be two source currencies in your example and the scope statement works for either Settlement or Original but not both, without the scope the link to currencyrate breaks down when reporting in just reporting currency.
    Any help would be great
    Thanks Andrew

    Andrew

    February 1, 2013 at 2:53 pm

    • Hi Andrew,

      This sounds like a question that’s too complex to answer in a comment. If you can drop me an email with more detail that would be a better idea – you can find my contact details at http://www.crossjoin.co.uk

      Chris

      Chris Webb

      February 1, 2013 at 4:39 pm

      • Hi Chris,
        Many thanks for your reply & yes I think we will have to evalaute the design here further .
        Andrew

        Andrew

        February 5, 2013 at 10:56 am

  18. Hello Chris. Your solution is great! Thank you. I am just wondering if i would like to define default currency what I should do. I tried to change default member of Reporting Currency dimension to particular currency, however it does not work. Have you any idea? Many thanks.

    Chris

    May 24, 2013 at 3:10 pm

    • That’s strange, it should work. Which client tool are you using?

      Chris Webb

      May 24, 2013 at 3:16 pm

      • I’m using Excel 2010 and Office Web Components browser. Maybe I did something wrong. I will double check and then I’ll get back to you. Thank you.

        Chris

        May 24, 2013 at 3:23 pm

  19. […] of that calculation; in our previous example that means you’d get 48 for the Grand Total instead. Currency conversion and weighted averages are common examples of calculations where this needs to happen. How can you […]

    • Superb post! :) I was struggling with Many to Many Currency conversion. Very informative.

      Shruti Shrinivas

      June 27, 2013 at 1:00 pm

  20. the implementation of this works but i am finding it has performance issues that is related to the surface area of the calculation. If i add the dimensions i am looking at using the exists function the performance issue is resolved. this is impractical though as i am going to have to add every attribute hierarchy within the scope statement clause to make sure performance is ok. is there anything else i can do to get around this?

    wayne

    February 24, 2014 at 9:35 am

    • If you have performance issues, and you are on Enterprise Edition, it might be worth trying to implement currency conversion using measure expressions instead rather than MDX – that should give you the best performance.

      Chris Webb

      February 24, 2014 at 9:39 am

      • thanks for the fast response Chris. It doesn’t seem to accept the expression
        (measures.ToGBP / measures.FromGBP) * measures.result

        it doesn’t seem to like the division operator. Also i have a question regarding expressions should i get it to work. will it total correctly?

        wayne

        February 24, 2014 at 10:11 am

      • Unfortunately a measure expression can only consist of a single division or multiplication operation. What you would need to do is pre-calculate all of the possible ToGBP/FromGBP values in a new table (which hopefully would not be too large), then use a measure expression like measures.rate * measures.result.

        Chris Webb

        February 24, 2014 at 10:13 am

      • thanks Chris, done this and it is almost instant. thanks for the direction.

        wayne

        February 24, 2014 at 12:03 pm

  21. Hi Chris,

    This is indeed an elegant solution. I am struggling with the previous year amount calculation.
    we have two types of exchange rates i.e. Reported and operational. For reported rates the we have actual rates for specific time period lets say for Jan-2013 the exchange rate of Euro was 0.8 and in Jan-2014 the exchange rate is 0.85 but in case of operated rates we take the current rates for current period as well as the previous period i.e. 0.85 for both measures current amount and previous year amount.

    If I create a measure in fact table Previous year and then apply the exchange rates in ssas it works fine. but if I create a calculated member Previous Year, and then apply the same logic it doesn’t give any conversions, for your reference please see folowing code:

    CREATE MEMBER CURRENTCUBE.[Measures].[Pre Year] // calculated member
    AS IIF([Transaction Date].[Date Hierarchy].CurrentMember.level.ordinal = 0,
    null,
    (ParallelPeriod([Transaction Date].[Date Hierarchy].[Year],
    1,
    [Transaction Date].[Date Hierarchy].CurrentMember),[Measures].[Transaction Amount Pre]
    )
    ),
    VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Sale’ ;

    SCOPE({[Measures].[Transaction Amount]}); // fact measure
    SCOPE([Transaction Date].[Date Hierarchy].[Date].MEMBERS);
    SCOPE([Transaction Currency].[Currency Key].[Currency Key].MEMBERS);
    SCOPE(EXCEPT([Reporting Currency].[Currency Key].[Currency Key].MEMBERS
    ,{{[Reporting Currency].[Currency Key].&[2147483647]}}));

    THIS = IIF([Measures].[Actual - Reporting Rates]=0
    , NULL
    ,IIF([Measures].[Actual] = 0
    , NULL
    , ([Measures].[Transaction Amount]/[Measures].[Actual]))
    * [Measures].[Actual - Reporting Rates]);

    END SCOPE;
    END SCOPE;
    END SCOPE;
    END SCOPE;

    SCOPE({[Measures].[Previous Year]}); // fact measure
    SCOPE([Transaction Date].[Date Hierarchy].[Date].MEMBERS);
    SCOPE([Transaction Currency].[Currency Key].[Currency Key].MEMBERS);
    SCOPE(EXCEPT([Reporting Currency].[Currency Key].[Currency Key].MEMBERS
    ,{{[Reporting Currency].[Currency Key].&[2147483647]}}));

    THIS = IIF([Measures].[Prior Year - Reporting Rates]=0
    , NULL
    ,IIF([Measures].[Prior Year] = 0
    , NULL
    , ([Measures].[Previous Year]/[Measures].[Prior Year]))
    * [Measures].[Prior Year - Reporting Rates] );

    END SCOPE;
    END SCOPE;
    END SCOPE;
    END SCOPE;

    SCOPE({[Measures].[Pre Year]}); // conversion on calculated member
    SCOPE([Transaction Date].[Date Hierarchy].[Date].MEMBERS);
    SCOPE([Transaction Currency].[Currency Key].[Currency Key].MEMBERS);
    SCOPE(EXCEPT([Reporting Currency].[Currency Key].[Currency Key].MEMBERS
    ,{{[Reporting Currency].[Currency Key].&[2147483647]}}));

    THIS = IIF([Measures].[Prior Year - Reporting Rates]=0
    , NULL
    ,IIF([Measures].[Prior Year] = 0
    , NULL
    , ([Measures].[Pre Year]/[Measures].[Prior Year]))
    * [Measures].[Prior Year - Reporting Rates] );

    END SCOPE;
    END SCOPE;
    END SCOPE;
    END SCOPE;

    the last conversion doesn’t work. Can you please help me out.

    Cheers
    Harris

    Harris

    May 2, 2014 at 2:29 pm

  22. Hi Chris

    I am facing another problem i.e. currency conversion with multiple dates. In my fact table there are two dates OrderDate and Invoicedate with amount as measure. Now the business wants to see the amount converted to reporting currency with the exchange rates of respective dates. i.e. if the order date is selected the amount should be converted using exchange rate of order date and when the invoice date is selected the amount should be converted using exchange rate of invoice date.

    How can I modify your solution to accomplish this requirement?

    Thanks for your help.

    Regards

    Harris

    Harris

    May 14, 2014 at 12:20 pm

    • This is going to be difficult, because there’s no easy way of knowing which date is selected – in MDX there is always a selection. I discussed a similar problem here: http://cwebbbi.wordpress.com/2013/05/23/using-scoped-assignments-to-show-calendar-and-financial-year-to-date-values-in-the-same-calculated-measure/ but honestly, I would investigate changing your requirements so that you have some other mechanism to switch between the two methods of calculating the exchange rates.

      Chris Webb

      May 14, 2014 at 9:59 pm

      • Hi All,

        Some time ago I read this article http://sqlmag.com/sql-server-analysis-services/many-many-currency-conversions-microsofts-sql-server-analysis-services
        I am not sure but I think that it can be helpful.
        The best option in my opinion is to combine Chris’s solution with the approach described in this artictle.

        Regards,
        Kris

        Kris

        May 15, 2014 at 7:24 am

      • Thanks for your reply Chris. I have decided to change my model a bit and instead of role playing dimensions I have decided to use single date dimension with date scenarios. In this way it will be easy to report on all the different dates based on scenario selected. Yes, it will increase the size of the fact table but this model is very flexible even for future. If business needs to incorporate other dates, which I am quite sure they will, it will be easy to extend the scenario dimension and same model will still be fine.

        Harris

        May 15, 2014 at 8:59 am

      • Hi Chris

        We have simplified the requirements. Now All the currency conversion are based on single date i.e Create date so if user selects receipt date with the amount it should convert all the amounts based on currency rate of creation date.

        If I use your code in this scenario it gives correct results when the create date dimension is selected but when I select other date dimension it gives the conversion only for the dates where create date is available.

        e.g. An order was received on 2014-01-01 and was created on 2014-01-10, when receipt date is selected the result is null for 2014-01-01 where as it should return the amount converted with the conversion rate of 2014-01-10 i.e. date on which it was created.

        Then I have combined the article http://sqlmag.com/sql-server-analysis-services/many-many-currency-conversions-microsofts-sql-server-analysis-services with your solution and use Exchange Create Date for the conversion it gives me results as expected. But there is another problem here it works well with the fact measures but when I apply the conversion on calculated measure it takes so much time to show the results. I have a measure Prior Year Amount which is calculated based on local currency amounts and then the currency conversion is applied on Prior Year Amount it takes couple of minutes to show the results which is not acceptable for the business.

        p.s. The Prior Year Amount should not be calculated based on converted current amount because business wants to apply current year rate on prior year amount so eliminate the currency effect.

        Any thoughts.

        Regards

        Harris

        Harris

        May 20, 2014 at 10:09 am

      • Please ignore my reply about the scenario dimension solution as it is rejected by business as they want to see the results based on two different dates i.e. Orders created in certain periods were processed in which periods. With scenario dimension and single date dimension I can not report this.

        Harris

        May 20, 2014 at 10:16 am

      • I think this problem is too complicated to be solved in the comments of a blog post, I’m afraid – I’m sure there is a solution, but it would take a couple of hours and need direct access to your cube.

        Chris Webb

        May 21, 2014 at 12:14 am

      • HI Chris

        What you think is the better way to look at this issue. Shall we arrange an interactive remote session where I can explain you what I am trying to do and what would be the expected outcome.

        Regards
        Harris

        Harris

        May 21, 2014 at 8:09 am

      • We could certainly schedule a remote session, but this would have to be paid for. You can find my contact details at http://www.crossjoin.co.uk – drop me an email and we can discuss rates, availability etc.

        Chris Webb

        May 21, 2014 at 7:49 pm

      • Hi Chris

        I will first discuss it with my manager as there is a process to hire services.

        If I get a positive reply I will certainly contact you.

        Regards
        Harris

        Harris

        May 21, 2014 at 8:43 pm

  23. HI Chris,

    Trying to implement this in a real world scenario. I’ve succesfully implemented this in a demo situation..Yeahhh but now I’m trying to implement this in a real world situation and the measure is returning nothing in case i don’t use the currency and the reporting currency in my query.

    Any ideas?

    Greetz,

    Hennie

    Hennie de Nooijer

    September 29, 2014 at 6:38 pm

    • To be clear: the measure is returning a null value if you don’t use the currency dimension and the reporting currency dimension in your query?

      Chris Webb

      September 29, 2014 at 7:55 pm

      • Don’t see nulls but just nothing. If i remove the scope script i have values.

        Can’t find errors…

        During testing i noticed that for certain periods, when including time dimension. Thnx.
        Hennie

        Hennie

        September 29, 2014 at 9:32 pm

      • It’s probably a problem with your scope statement, but it’s hard to say what the problem is exactly without knowing more about your cube and seeing the code.

        Chris Webb

        September 29, 2014 at 10:24 pm

      • The code:

        Scope ({[Measures].[Org Invoice Amount]});
        Scope([Date].[PK_Date].[PK_Date].MEMBERS);
        Scope([Currency].[Currency Description].[Currency Description].MEMBERS);
        Scope(Except([Reporting Currency].[Currency Description].[Currency Description].Members,
        {{[Reporting Currency].[Currency Description].&[2147483647]}}));

        THIS = IIF([Measures].[Rate - Reporting Exchange Rates] = 0, NULL, IIF([Measures].[Rate] = 0, NULL, ([Measures].[Org Invoice Amount] *[Measures].[Rate]))/[Measures].[Rate - Reporting Exchange Rates]);

        End Scope;
        End Scope;
        End Scope;
        End Scope;

        Thnx again…

        If you’re interested I could send you the project?

        My first guess was building the currencylogic into the customer solution but that doesn’t work, unfortunately. I think I’m going to work the other way around and building the cube from scratch with the Invoice fact and add the currency logic, then add the dimensions of the customer to the cube….. Hopefully that will give me a clue what the problem is……

        Thanx again..

        Hennie

        Hennie de Nooijer

        September 30, 2014 at 8:27 am

      • Built the cube with the datasource, datasourceview and dimensions of the customer and included the SCOPE script and i’s working. So it must be something in the cube.

        Hennie de Nooijer

        September 30, 2014 at 11:50 am

      • Have you set up the many-to-many relationships correctly?

        Chris Webb

        September 30, 2014 at 10:26 pm

      • Do you mean Many – to – many conversion or Many – to – many relationship?

        I don’t have many to many relationship

        See my blog:

        http://bifuture.blogspot.nl/2014/09/ssas-multicurrency-problem-part-i.html

        http://bifuture.blogspot.nl/2014/09/ssas-multicurrency-problem-part-ii.html

        Currently writing part III where I’m introducing the suggestion of you described in the blogpost above.

        Gr,
        Hennie

        Hennie de Nooijer

        October 1, 2014 at 9:23 am

      • Sorry, I wasn’t thinking – you don’t need m2m relationships anywhere here. What I would recommend is replacing

        THIS = IIF([Measures].[Rate - Reporting Exchange Rates] = 0, NULL, IIF([Measures].[Rate] = 0, NULL, ([Measures].[Org Invoice Amount] *[Measures].[Rate]))/[Measures].[Rate - Reporting Exchange Rates]);

        with

        THIS = IIF([Measures].[Rate - Reporting Exchange Rates] = 0, NULL, 1);

        to see whether it’s the lack of values for this measure that is causing problems. You should also test Measures.Rate in the same way.

        Chris Webb

        October 1, 2014 at 8:03 pm

  24. HI Chris,

    It seems there is something wrong with the Currencyrates. Reading a currencyrate tabel from Application and in 2014 the data is not entered anymore. I’m investigating this further and if I find some technical problems, I’ll let you know.

    Thank you for your time….

    It’s a data problem….grmbll..;-)

    Greetz,
    Hennie

    Hennie de Nooijer

    October 2, 2014 at 9:41 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: