Avoing repeating SQL appearing/being generated

[former_member272336]

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

[Former Member · Accepted solution]

Hello Philip,

Did you try defining the conext between

Context1: Fact,product and CC

Contecx2: Fact,Currency,CC

See if that helps

Regards,

Laxminarayana

[former_member272336 · Accepted solution]

Hi Laxminarayana

I I didn't want to use contexts for this particular problem loop due to fact universe based on a multi-star schema and many context already - would need to double the number of contexts.

Would be good if switch/parameter somewhere in universe where could say don't generate repeating SQL.

Thanks

[Former Member · Accepted solution]

Hello Philip,

Still trying to understand how do you see repeating sql. Could you please ellobarte a bit more

Regards,

Laxminarayana

[former_member272336 · Accepted solution]

Hi,

Have defined in where clause of multiple objects

where currency_converting_from = currency.cur_code.

If user selects >1 of these objects the where clause appaers once for each object selected.

Thanks

[Former Member · Accepted solution]

Hi Philip,

You can create predefined condition and check the option "Apply for universe" or "Apply for class" depending on your requirement.

If any object used from the specific class/universe where the above predefined condition is placed, then automatically where clause will be added to sql without manually adding the predefined condition.

Hope this helps .

thanks,

Niranjan

[Former Member · Accepted solution]

Hi Philip,

Could you please provide the sql that you are getting .

Thanks,

Pramod.

[former_member272336 · Accepted solution]

Hi 

Niranjan

Thanks this is helpful.

Hi Promod sql was getting was

where currency_converting_from = currency.cur_code

and currency_converting_from = currency.cur_code

currency_converting_from = currency.cur_code

if selecetd 3 objects which had this in where clause.

Thanks