Board index MS SQL Server

Creating an MDX Calculated Member for Dynamic Exchange Rates

Creating an MDX Calculated Member for Dynamic Exchange Rates

Post by Nei » Sat, 22 Mar 2003 01:20:15



I have a fact table with a numeric value stored in the currency that
the related transaction was made in, for example:

Fact Table
          Period  Amount    Currency
          ------  ------    --------
  row1    Jan 1    98.76    USD  
  row2    Jan 1   123.45    EUR
  row3    Jan 1   234.56    CAD  
  row4    Jan 1   345.67    GBP

I need to be able to represent these values in their GBP equivalent,
based on a table that stores a set of rates for all the above
currencies at a point in time:

Rate Table
          Rate Type  Rate Desc    Curr   Rate Value
          ---------  ---------    ----   ----------
  row1    Monthly    Jan 2003      GBP        1.000
  row2    Monthly    Jan 2003      USD        1.332
  row3    Monthly    Jan 2003      EUR        1.456
  row4    Monthly    Jan 2003      CAD        2.180
  row5    Annual     Jan 2003      GBP        1.000
  row6    Annual     Jan 2003      USD        1.321
  row7    Annual     Jan 2003      EUR        1.324
  row8    Annual     Jan 2003      CAD        2.210

My objective is to not store the combinations, but rather have the
user select the Rate Type as a dimension, the Rate Desc as a
dimension, display the currency and have as a calculated member the
Amount measure on the fact table divided by the relevant Rate Value
from the Rate dimension.

I have set up a calculated member on the measures dimension to do this
but the MDX query is not valid, I presume because I am trying to use a
dimensional attribute in a numeric calculation with a measure..?

Alternatively, I created a Rate cube and then joined this to the
original cube (as a virtual cube) but cannot prevent the aggergation
of the Rate Value, i.e. GBP rate value of 1.000 is summed
unnecessarily.

Does anyone have any thoughts on what I might be doing wrong/hints for
solving this dilemma using MDX?

All comments gratefully received.

 
 
 
Top

Creating an MDX Calculated Member for Dynamic Exchange Rates

Post by Eddy » Sat, 22 Mar 2003 05:41:27


Hello Neil,
just add a column 'GBP' to your fact table.
This will help you to prevent further issues if you want
to see the totals for a year.

HTH
Eddy

Quote:>-----Original Message-----
>I have a fact table with a numeric value stored in the
currency that
>the related transaction was made in, for example:

>Fact Table
>          Period  Amount    Currency
>          ------  ------    --------
>  row1    Jan 1    98.76    USD  
>  row2    Jan 1   123.45    EUR
>  row3    Jan 1   234.56    CAD  
>  row4    Jan 1   345.67    GBP

>I need to be able to represent these values in their GBP
equivalent,
>based on a table that stores a set of rates for all the
above
>currencies at a point in time:

>Rate Table
>          Rate Type  Rate Desc    Curr   Rate Value
>          ---------  ---------    ----   ----------
>  row1    Monthly    Jan 2003      GBP        1.000
>  row2    Monthly    Jan 2003      USD        1.332
>  row3    Monthly    Jan 2003      EUR        1.456
>  row4    Monthly    Jan 2003      CAD        2.180
>  row5    Annual     Jan 2003      GBP        1.000
>  row6    Annual     Jan 2003      USD        1.321
>  row7    Annual     Jan 2003      EUR        1.324
>  row8    Annual     Jan 2003      CAD        2.210

>My objective is to not store the combinations, but rather
have the
>user select the Rate Type as a dimension, the Rate Desc
as a
>dimension, display the currency and have as a calculated
member the
>Amount measure on the fact table divided by the relevant
Rate Value
>from the Rate dimension.

>I have set up a calculated member on the measures

dimension to do this

- Show quoted text -

Quote:>but the MDX query is not valid, I presume because I am
trying to use a
>dimensional attribute in a numeric calculation with a
measure..?

>Alternatively, I created a Rate cube and then joined this
to the
>original cube (as a virtual cube) but cannot prevent the
aggergation
>of the Rate Value, i.e. GBP rate value of 1.000 is summed
>unnecessarily.

>Does anyone have any thoughts on what I might be doing
wrong/hints for
>solving this dilemma using MDX?

>All comments gratefully received.
>.


 
 
 
Top

Creating an MDX Calculated Member for Dynamic Exchange Rates

Post by Nei » Sat, 22 Mar 2003 20:10:54


Thanks Eddy but I can't store a pre-defined value in the fact because
I don't know what it will be until the Rate Type is chosen, therefore
storing it would require calculating the permutations of all potential
values, e.g.

166 currencies x 12 months x 'n' transaction records

would increase the fact table by a factor of 1,992 just for one rate
type and I need to store at least four rate types!

 
 
 
Top

Creating an MDX Calculated Member for Dynamic Exchange Rates

Post by Tom Cheste » Sun, 23 Mar 2003 00:22:15


Neil, the problem is solvable with your existing fact table structure. The
"textbook" approach is the virtual cube design you outline. You need to
write a bunch of MDX to stitch it all together. Here's an article that may
help a little; there's a currency conversion example towards the end...

www.tomchester.net/ArticlesDownloads/PASSNews/CalculatedCells.html

The examples won't map exactly to your situation, because the currency is a
property of a dimension, not in fact table. Hopefully you'll get pointed in
the right direction though. (Don't infer one way or the other that calc
cells should be used.)


    www.tomchester.net


Quote:> I have a fact table with a numeric value stored in the currency that
> the related transaction was made in, for example:

> Fact Table
>           Period  Amount    Currency
>           ------  ------    --------
>   row1    Jan 1    98.76    USD
>   row2    Jan 1   123.45    EUR
>   row3    Jan 1   234.56    CAD
>   row4    Jan 1   345.67    GBP

> I need to be able to represent these values in their GBP equivalent,
> based on a table that stores a set of rates for all the above
> currencies at a point in time:

> Rate Table
>           Rate Type  Rate Desc    Curr   Rate Value
>           ---------  ---------    ----   ----------
>   row1    Monthly    Jan 2003      GBP        1.000
>   row2    Monthly    Jan 2003      USD        1.332
>   row3    Monthly    Jan 2003      EUR        1.456
>   row4    Monthly    Jan 2003      CAD        2.180
>   row5    Annual     Jan 2003      GBP        1.000
>   row6    Annual     Jan 2003      USD        1.321
>   row7    Annual     Jan 2003      EUR        1.324
>   row8    Annual     Jan 2003      CAD        2.210

> My objective is to not store the combinations, but rather have the
> user select the Rate Type as a dimension, the Rate Desc as a
> dimension, display the currency and have as a calculated member the
> Amount measure on the fact table divided by the relevant Rate Value
> from the Rate dimension.

> I have set up a calculated member on the measures dimension to do this
> but the MDX query is not valid, I presume because I am trying to use a
> dimensional attribute in a numeric calculation with a measure..?

> Alternatively, I created a Rate cube and then joined this to the
> original cube (as a virtual cube) but cannot prevent the aggergation
> of the Rate Value, i.e. GBP rate value of 1.000 is summed
> unnecessarily.

> Does anyone have any thoughts on what I might be doing wrong/hints for
> solving this dilemma using MDX?

> All comments gratefully received.

 
 
 
Top

1. Creating an MDX Calculated Member as an Average over Time Dimension

Hello:

I'm currently working on a retail cube solution, and I'm stuck with the
Average Stock indicator.

The indicator works thus:

a) The following applies for the Store N1, Product N 1,
for the first three days of a given week:

On Monday, Stock = 4
On Tuesday, Stock = 6
On Wednesday, Stock = 8

For the three days, the Average Stock for Store N1, Product N 1,
= 6 = (4+6+8) / Distinct Number of Days = 18 / 3

b) Now, the following applies for the Store N2, Product N 1,
for the first three days of a given week:

On Monday, Stock = 6
On Tuesday, Stock = 8
On Wednesday, Stock = 13

For the three days, the Average Stock for Store N2, Product N 1,
= 9 = (6+8+13) / Distinct Number of Days = 27 / 3

c) Finallly, considering both Store N1 and Store N2, for Product N 1,

On Monday, Stock = 10
On Tuesday, Stock = 14
On Wednesday, Stock = 21

So, for the three days, the Average for Store N1 and Store N2, Product
N 1,
= 15 = (10+14+21) / Distinct Number of Days = 45 / 3

As you can see, this is a rather complex indicator, that for any given
period (a specific day, week, month, etc) is the SUM() of its individual
members values, but across a number of days, you have to divide by the
disticnt
number of days.

In fact, the formula

[Average Stock] = Iif([Time].CurrentMember.Children.Count = 0 ,
[Measures].[Stock],
[Measures].[Stock]/[Measures].[Distinct Days])

does gives the required results, having the primitive Measures defined as:

[Stock]= SUM("dbo"."FT"."Stock")
[Distinct Days] = Distinct(Count("dbo"."FT"."Date"))

The problem with this approach is that if you try to display this data
on EXCEL (with Pivot Table Service) and you select a subset of values of
the
[Time] dimension (like Monday and Wednesday, excluding Tuesday), the
[Distinct Days] indicator gets undefined and so does the [Average Stock]
calulated Member.

On the other hand, you can try the following formula:

[Average Stock] = AVG(
{
Descendants([Time].CurrentMember,
[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,
LEAVES)
[Measures].[Stock]
)

It works exactly as the "Distinct Days" approach, but if you select a
subset of values
of the [Time] dimension (like Monday and Wednesday, excluding Tuesday), the
[Average Stock] member isn't undefined, but it doesn't reflect de selected
days data;
it reflects the data of the three days, ignoring the days excluded.

Both approaches behave exactly the same, wether you select a subset of
days, weeks,
months, years or whatever hierarchy levels you define for the [Time]
dimension.
One goes undefined, the other ignores your selection.

If anyone has any clue whatsoever at some way to solve this problem,
please e-mail
me back through the newsgroup or through my e-mail account.

Thanks in advance,

Sebastian Armas.

2. DTS export functionality

3. Copy scripts

4. Running dll from SqlAgent job

5. Major MDX problems with Calculated member (this is a good test for MDX)

6. MDX - Member properties in a calculated member

7. Creating a calculated member using dimension members

8. OLAP 2000 - Calculated member with a calculated parent member

9. Calculated members vs MDX

10. MDX for calculated member


All times are UTC
Board index