none
Currency conversions : Many-Many RRS feed

  • Question

  • I've set up a Many-Many Currency conversion using the BI wizard, and I have an issue in with the Pivot Currency conversion.

     

    Example,

     

    Currency Fact layout

     

    SourceCCYId

    DestinationCCYId

    RateType

    Month

    Rate

     

    The Link is through the SourceCCYId, with defaults on the Month (Defaulted to current month) and Rate Type (defaulted to Bank of england rates).

     

    The SourceCCY Dimension is flat with 4 base currencies, EUR, CAN, USD and GBP,  the requirement is to report in USD and GBP, with GBP being used as the pivot currency.

     

    The Currency Measure group is set up with an aggregation type of NONE (LastNonEmpty just caused it to hand when running the query).

     

    I ran the BI wizard to generate the Scope and conversion method with a Pivot of GBP, and Conversion of 1 GBP->n USD

     

    When i select the source CCY on the Rows, and Reporting CCY on the Columns, I get the USD being calculated correctly but the GBP is calculuated on the Aggregate of the rates for all 4 rates that run against that BASE 

     

    Eg Rates

     

    Can->Can = 1

    Can->Eur = 1.52

    Can->GBP = 2.23

    Can->USD = 1.16

     

    Aggregate used in the GBP calcualtion = 5.91

     

    In the main cube I selected the reporting currency and SourceCCY to give a grid

     

    Reporting CCY

    Local     USD  GBP

    CAN      1        0.86  5.91

    EUR      1        1.31  3.87

    GBP      1        1.92  5.09

     

    If I add in the CAD and EUR to the reporting currency the calculation is correct for everything except the GBP->GBP

     

    Reporting CCY

              CAN    Local    USD   GBP

    CAN     1           1       0.86   0.44

    GBP    2.23       1       1.92   0.37

    USD    1.16       1         1      5.09

     

    The Only thing i can see is that the Currency Fact table contains all the Possible conversions from source to Destination

     

    Eg

     

    USD->CAD

    USD->EUR

    USD->GBP

    USD->USD

     

    CAD->CAD

    CAD->EUR

    CAD->GBP

    CAD->USD

     

    EUR->CAD

    EUR->EUR

    EUR->GBP

    EUR->USD

     

    GBP->CAD

    GBP->EUR

    GBP->GBP

    GBP->USD

     

    I think this may be the source of the problem but cannot figure out why its aggregating the rate despite having the AggregationFunction = NONE.

     

    The question is do i need to filter the Currency Fact table in order to remove the GBP rates ->CAD/EUR rates, and the USD->CAD/EUR rates?

     

    We're running SSAS 2005, build 9.00.3042 (both 32 and 64 bit).

     

    Thanks in advance.

     

     

     

    Friday, October 24, 2008 8:55 AM

Answers

  •  

    Problem solved, I did have to filter the Currency Fact table so that it only had the Pivot currency (GBP in my case) in the DestinationCurrencyId Column.

     

     

    Friday, October 24, 2008 10:30 AM