Skip to Content

Avoing repeating SQL appearing/being generated

Hi,

We have the following requirement

Have large fact tables with financail measures and currency they were in and product they wer for.

We need to be able dynmically report these values in any currency.

Appropriate rate determined by getting rate applicaable at date product created (created_date attribute on product dimension.)

e.g report tool ask user for desired currency and gets converted via currency conversion table
of format
cur_converting_from
cur_converting_to
eff_date
exp_date
conversion_rate

Fact table has product_id + currency_id surrogate keys

e.g

Currency_id
Product_id
financial_value

Currency table has two currencies at moment sterling + euro but needs to be capable to expand to other currencies.
Currenly have following joins

fact to product
fact to currency
product to currency_convertor
currency to currency_convertor

i.e. loop

Only way I can think of avoiding loop is
removing the currency to currency_convertor join and putting it as where condition in the fin measures objects.

However, if more than one such object selected it appears multiple times in the SQL.

Is there any way to stop such identical sql appearing when view the SQL?


Thoughts on other/better ways to acahive this?

Thnaks

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 04, 2012 at 08:53 PM

    Hello Philip,

    Did you try defining the conext between

    Context1: Fact,product and CC

    Contecx2: Fact,Currency,CC

    See if that helps

    Regards,

    Laxminarayana

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.