Hi,

We’re a finance firm and have a cube we’ve been using for some time. Problem is
all its values are in dollars and we have offices in Europe and E Asia (as well
as N America).

I built a second cube containing nothing but the necessary exchange rates. With
2 dimensions: Date and Currency.

I went ahead and built the (we’ll call it) Xrate cube because it was quick to do
and from everything I’d remembered (from some time ago), I was pretty sure that
this was the standard precondition.

Now how to combine this with the main cube? I’ve found a bunch of things on
groups.google.com including this:

http://www.tomchester.net/articlesdo...atedcells.html

Tom Chester’s link suggests a combination of Calculated Member (with a LOOKUP
inside it) in combination with calculated cells. Since this is using LOOKUP,
one presumes that this is not the Virtual Cube solution. It seems to me that
LOOKUP may be slow (slower than a Virtual Cube). But what is the Virtual Cube
solution? I made a Virtual cube out of the two cubes I have. Using the Wizard
and have gone back into Edit to try and tweak it.

The only operation I want is for any measure in Cube A (the main cube), that’s a
value, for it then to be divided by the xrate that it finds in the Xrate cube
joining only on date. I confess I haven’t yet been able to figure out how to do
this. So a range of measures from the main cube divided by the single measure
(for that day) to be found in the other Xrate cube.

The downside though with a Virtual Cube (as best I understand it) is that I’ll
have to have a separate one for each currency (we have USD; we need EUR, GPB,
USD and JPY). So this makes the idea of simply adding a dimension, Currency, to
the main cube appealing. The user would select their target currency, and,
presto, all the numbers would change (but how slowly?). The UI might be more
appealing (simpler).

So I’m looking for advice (or pointers to good documents – but good documents
for this scenario seem to be in short supply).

pat

The source system can normally supply you with an invoice/order amount both
in a local currency and a system currency(like USD or EUR). The system
currency is normally your accounting currency. And with these two amounts
you have a local currency key that you can use in a currency dimension. You
then build your fact table with sales data with this design. Next, you build
a fact table with currency rates, a currency key and a time key. Make sure
that you use the same currency dimension as in the first sales cube and the
same time dimension. Build separate cubes for the two fact tables and join
them in a virtual cube.

This design will only work if you have a local currency amount and a system
currency amount in the source system.

Regards
/Thomas

"Patrick Flaherty" <Patrick_member (AT) newsguy (DOT) com> wrote

With the first problem, that you only have USD amounts you will have to
assume that each country only sell in the local currency. From this
assumption you can construct the local currency amount in the ETL-process,
with DTS, by the help of a country, currency,date and exchange rate( aginst
the dollar) table.

Regards
/Thomas

"Thomas Ivarsson" <TI (AT) nospam (DOT) com> wrote

Hi Thomas,

thanx for the response.

OK trying to understand your post ... are you saying that I put both amounts
(to use your terminology) local and system/accounting 'side-by-side' (in the
same row) in the main facts table? (this doesn't make a lot of sense to me).

Or are you saying that I indicate, in a given row in the main cube, what the two
currencies are - say by ISO (e.g. USD, EUR). And then use a shared dimension
across the two cubes in the virtual cube to, in effect, do a lookup and convert
the single (USD [accounting]) currency that's found the in facts table in my
main (non-xrate) cube? (this would be when I want to convert from USD to EUR)?

Or are you saying something different?

Right at the moment, I'm using the MDX Sample App to construct MDX that will do
the converion via LOOKUPCUBE and Calculated Measures. I'm not sure that, in the
long run, this is the right way to go, but I'm just trying to gain some more
intuitions as to how I'm going to do this.

I do though have a small test case working:

with member [measures].[gbp] as '[measures].[portvalue adjusted] /
lookupcube("Xrates",
"([ISO].[All ISO].[USD].[gbp],[Date].[All Date].[2005].[Quarter
1].[january].[3])")'
select
{ ([Zone and Portfolio].[All Zone and Portfolio].[E],[Date].[2005].[Quarter
1].[January].[3]) } on columns,
{ [Measures].[gbp] } on 1
from aum

Aum is the 'main' cube' and Xrates the conversion cube. The problem (with this
code) that I'm next trying to solve is how to generalize this across dates.
And the problem then would be to generalize it across measures.




pat



In article <euB3SgUUFHA.2468 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Thomas Ivarsson says...

OK. To answer my last question (how to generalize across dates).

I had this:

with member [measures].[gbp] as '[measures].[portvalue adjusted] /
lookupcube("Xrates",
"([ISO].[All ISO].[USD].[gbp],[Date].[All Date].[2005].[Quarter
1].[january].[3])")'
select
{ ([Zone and Portfolio].[All Zone and Portfolio].[E],[Date].[2005].[Quarter
1].[January].[3]) } on columns,
{ [Measures].[gbp] } on 1
from aum

So to generalize across dates, I 1) made a shared Date dimension and made sure
that was what's in both cubes (aum and Xrates).

And then a modified query like the following will work:

with member [measures].[gbp] as '[measures].[portvalue adjusted] /
lookupcube("Xrates",

"([ISO].[All ISO].[USD].[gbp]," + membertostr([Date].currentmember) + " )")'
select
{crossjoin({[Zone and Portfolio].[All Zone and
Portfolio].[E]},[Date].[2005].[Quarter 1].children)} on 0,
{ [Measures].[gbp] } on 1
from aum

Now the problem is how to generalize across 1) currencies (I want eventually to
translate USD to any of EUR, GBP or JPY and 2) different measures (value).
Above I'm using 'portvalue', but there are others as well.

pat





In article <d5dt5j01u9l (AT) drn (DOT) newsguy.com>, Patrick Flaherty says...

You have a local currency amount and a system currency amount(USD) for each
record in the main fact table. With a currency dimension connected to that
fact table a user can choose their local currency amount and the system
currency amount in the cube. If you divide the local currency amount with
the system currency amount you can see the exchange rate between these
currencies. Some of my customers use two system currencies like SEK(I live
in Sweden) and EUR and this design have worked well for them. Most of the
source systems for sales have this functionality(built in exchange rates and
different currency amounts)

The exchange rate cube will add the ability to use different exchange rates
for the same transaction or record in the main fact table.

Virtual cubes or the LookUpCube-function will use almost the same MDX to
calculate amounts with different exchange rates. Something like
([Currency].[EUR],[Measure].[Month rate])*([Measure].[System Amount])

Regards
Thomas

"Patrick Flaherty" <Patrick_member (AT) newsguy (DOT) com> wrote

Well I've made a Virtual Cube out of my 'main' cube and an Xrates cube. In
addition they have shared dimensions for both Date (Time) and Currency.

The main cube contains only USD values. I'm attempting to make a Calculated
Member, GBP, in my Currency dimension to convert the source USD measure into GBP
([measures].rate below comes from the Xrate cube):

IIf( NOT [Measures].CurrentMember.Name = "rate",
[Measures].currentmember/([Measures].[Rate],[Currency].[Fromiso].&[USD].&[GBP]),
[measures].currentmember)

This is provoking the dreaded 'infinite recursion' problem.

Is there a circular reference here? (there's not that I can see).

Or am I going wrong at some other point?

thanx.

pat

In article <et8wH3gUFHA.2328 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Thomas Ivarsson says...