# Currency conversions : Many-Many

• ### 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->EUR

USD->GBP

USD->USD

EUR->EUR

EUR->GBP

EUR->USD

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).

Friday, October 24, 2008 8:55 AM

•

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