Hi everybody,

I struggle with a DW design issue that I hope somebody can provide a
solution for?

I have a traditional Star Schema, and are building an OLP cube on top. The
development is taken place using SQL Server 2005.

For the purpose of this question, the layout is the following:

Dimension tables:

Time: Transaction date, week, month, etc.

Product: Product Category, Product Line, Product.

Customer: Customer, Customer Segment, etc.

Fact table:

Sales: Transaction date, Customer, Product, Price, Currency, price in
currency, Exchange rate.

Requirement:

I am looking for sales by product by customer/product etc. (This is not a
problem).  However, each of the sales transactions is made in a particular
currency. For example is sales transaction 1 in Euro and transaction 2 is in
British Pounds. Both transactions are for Product A. On top of the existing
dimensions, I need to see each of the sales transactions in a given currency
and exchange rate (for example Actual or budgeted exchange rate).

One scenario could be to see the value of sales by product (Product A) in
Euro. In this case will transaction 2 that was made in GBP have to be
converted to EUR, so I can see total sales for product A in Euro.

Solution so far:

So far, have I have added the combination of exchange rate and currency to
the fact table. For example, the transaction in currency "GBP" and "EUR" for
both the exchange rate "Actual" and "Budget2005". This gives 4 entries for
each sales transaction in the fact table. This does NOT work... Selecting on
the dimension tables, for example on the product lead to the value of the 4
transactions to be added together. :-( Where in fact I only want the unique
transaction based on dimension and chosen Currency and Exchange rate.

I have considered making another dimension for Currency and Exchange rate,
but it is not possible to create a relationship to the fact table as the
values Currency and Exchange rate can not make a unique relation to the fact
table.

So far my best bet is only to have one unique entry of the sales transaction
in the original currency in the Fact table. But how do I then make it
possible to see the sales transaction by relevant dimension in the chosen
Currency and Exchange rate?

Look forward to hearing from you.

Best Regards,

Bigalexx