DW design question - Dimension for currency

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,
BigalexxHi Bigalexx,
Thanks for your post.
This question appears to be consulting in nature. We would also like to
introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/defaul...AdvisoryService
Outside of the US/Canada:
http://support.microsoft.com/defaul...ernational.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Bigalexx,
>From your question I am guessing you are a beginner....
I have a beginners page on my web site www.peternolan.com with links on
the books to read to learn data modeling for BI systems......if you
are struggling with the multi-country, multi-currency pieces of a model
you would greatly improve your skills by reading some of ralphs books
and looking at the databases on the CD in the back.....
My personal home page is intended as a resource for beginners...so if
you (or others here) have opinions on what else is useful for beginners
I'd be happy for feedback and to put things onto my list of things to
publish...
Good luck!!
Peter
www.peternolan.com