BO4 - IDT tool - multiple paths showing when view SQL after convert .unv

[former_member272336]

Hi,

We have a .unv and can run a query fine against the .unv as all required joins exist within a context

2 objects of format

select sum(sales.measure1), sum(sales.measure2)

from

fact,

currency,

currency_convertor trade_conversion_rates,

sales,

currency_convertor.cost_conversion_rates,

product

where the joins.

In IDT, shows as 2 sqls multiple paths but all joins exist in one context in IDT.

I have 2 questions

1) - What is the setting which causes multiple paths in IDT

2) -  Why the different behaviour.

Thansk

[Former Member · Accepted solution]

Hello Philip,

Please check in Business Layer(.blx), under query options "Multiple SQL statements for each measure"
If it is checked , then you will get multiple sql statemets for each meaure object in the report.
By default it is checked.

Thanks,
Pramod

[former_member272336 · Accepted solution]

Hi Pramod,

Thanks for suggestion.

However, this option is unchecked yet still get the multiple paths showing when view the SQL.

Any other ideas?

Thanks

[Former Member · Accepted solution]

Hi Philip,

Multiple paths means , two differnt sqls in the view sql pane?

is this a unv to .unx converted universe?

Please provide the sql for the query?

Thanks,

Pramod.

[former_member272336 · Accepted solution]

Hi Pramod,

Sory about delay - been away.

Yes - this is a unv conveted to a unx and only 1 sql in the unv is generated

Other queies fine.

Only thing different about these queries to the other queries which are  that the objeects selected have a where clause in them.

Query 1

select (sales.trade_value) * trade_rates.conversion_rate

from sales, currency, currency_conversion trade_rates, product

where sales.product_id= product.dimension_key

and sales.curency_id = currency.dimension_key

and sales.curency_code = trade_rates.currency_code

and product.on_sale_date between trade_rates.stat_date and trade_rates.cost_rates

Query 2

select (sales.cost_value) * cost_rates.conversion_rate

from sales, currency, currency_conversion cost_rates, product

where sales.product_id= product.dimension_key

and sales.curency_id = currency.dimension_key

and sales.curency_code = cost_rates.currency_code

and product.on_sale_date between cost_rates.stat_date and cost_rates.cost_rates

Unv query which is ok

select (sales.cost_value) * cost_rates.conversion_rate, (sales.trade_value) * trade_rates.conversion_rate,

from sales, currency, currency_conversion trade_rates,

currency_conversion cost_rates, product

where sales.product_id= product.dimension_key

and sales.curency_id = currency.dimension_key

and product.on_sale_date between trade_rates.start_date and trade_rates.end_date

and product.on_sale_date between cost_rates.start_date and cost_rates.end_date

and sales.curency_code = cost_rates.currency_code

and sales.curency_code = trade_rates.currency_code

All joins pat of 1 context apat

from the follwoing two joins

part of the sales_cost_value column

sales.curency_code = cost_rates.currency_code

part of the sales_trade_value column

and sales.curency_code = trade_rates.currency_code

Thanks

[Former Member · Accepted solution]

Hi Philip,

Could you please include the below tow jois into your context and test the issue.

sales.curency_code = cost_rates.currency_code
and sales.curency_code = trade_rates.currency_code

As per my understanding these two joins are not in the context ,which is causing multi sql paths(If my understanding is  correct). Please do test .

Hope it works.

Thanks,
Pramod.

[former_member272336 · Accepted solution]

Hi Pramod,

Thanks for response.

If include the joins as selected can have 1 sql but problem with this is that it causes a loop

sales-product-conversion rates

sales-currency-conversion_rates

This is why was putting the joins you mention in the where clause - this approach works O.K in the unv but just not in the unx.

Thanks

[Former Member · Accepted solution]

Hi Philip,

I am really not sure what is the exact differnce between the unx and unv universe as you are saying the  same universe structure working fine in .unv.

If that is the case it may be an issue with the IDT.

->one more this as you are saying including  the two joins causing loop, is it possible to create an alias table to resolve the loop.

I am not sure this will helps you are not.

Thanks,

Pramod.

[former_member272336 · Accepted solution]

Hi Pramod,

In our scneario an alias woulnd't work.

I have solved this partciular problem by changing the underlying database structure - added another dimension to the fact table.

Many Thanks for your suggestions.

Philip

[Former Member · Accepted solution]

Thanks for sharing the info Philip

Regards,

Pramod.

[Former Member]

Hi,

I know its kind of old post, but i have similar problem. I have an IDT universe based on single table. I created some custom measures based on the objects in the tables. When i create a query using custom measures along with measures from the table, i am getting two sqls. Can anyone help me how to get them into one sql?

Thanks,

Raj.