JOIN_BY_SQL and multiple SQL

[system]

1. Multiple SQL statements for each context
2. Multiple SQL statements for each measure

These settings will be your problem, they do what they say on the tin :).

Join by SQL only works with Oracle. It means that the statements are joined / merged at the database level, rather than by the Webi Intelligence tool. This should improve performance.

---

Mak 1 (BOB member since 2005-01-06)

[system]

pls see at end

---

GBS74 (BOB member since 2007-01-23)

[system]

2. Multiple SQL statements for each measure

I would uncheck this option, as you will always get 2 SQL statements even if the measures come from the same fact.

then why it is not working… why it is not producing one SQL instead of two

It will always produce two SQL statments. The important bit is if there are common dimensions involved the statements should be joined in the query panel. Join by SQL means the joining will be done by the database instead of the Web Intelligence tool, however, visually you should see the same, i.e. two SQL statments, joined, in the query panel.

---

Mak 1 (BOB member since 2005-01-06)

[system]

you mean to say if we select JOIN_BY_SQL option to YES, merge will pushdown to database but there will be no difference in display , it will display as two separate SQLs ( in above case) ?

another question is what will be implications of disabling option "MULTIPLE SQL STATEMENTS FOR EACH MEASURE.

regards

---

GBS74 (BOB member since 2007-01-23)

[system]

you mean to say if we select JOIN_BY_SQL option to YES, merge will pushdown to database but there will be no difference in display , it will display as two separate SQLs ( in above case) ?

Yes and they need to be joined statements.

another question is what will be implications of disabling option "MULTIPLE SQL STATEMENTS FOR EACH MEASURE

if you have two measures selected from one fact table, they will appear in one SQL statement, rather than two. Why not have a play around and find out :).

---

Mak 1 (BOB member since 2005-01-06)

[system]

pls see ar end

---

GBS74 (BOB member since 2007-01-23)

[system]

A) Disable: multiple SQL statements for measures!
B) Enable: multiple SQL staements for contexts!

C) JOIN_by_SQL will kick in if you are only using conformed/common dimensions and all the involved facts use SQL aggregate function (any proper measures should use a SQL aggregate function IMHO), setting projection is not sufficient. And if JOIN_by_SQL kicks in you will only see ONE… BIG SQL statement, which outer joins both data cubes (one data cube for each fact).

---

Andreas (BOB member since 2002-06-20)

[system]

thanks Andreas , I have applied exactly what you have mentioned in universe. measures are aggregated as well. but still 2 SQL appearing , where as I am expecting one with outer join.

is there anything else I need to set, in order to see single sql.

many regards

---

GBS74 (BOB member since 2007-01-23)

[system]

Are you by chance using DESKI? DESKI does not support JOIN_BY_SQL!

Which version? XI 3.x or SAP BI BusObjects 4?
Which exact patch level?
Post both SQL statements, please.

---

Andreas (BOB member since 2002-06-20)

[system]

And if JOIN_by_SQL kicks in you will only see ONE… BIG SQL statement, which outer joins both data cubes (one data cube for each fact).

Interesting, I didn’t realise this. I may have misled you earlier, apologies.
What version of Oracle and connectivity are you using?

---

Mak 1 (BOB member since 2005-01-06)

[system]

please look at last msg

---

GBS74 (BOB member since 2007-01-23)

[system]

@Andreas

Are you by chance using DESKI? DESKI does not support JOIN_BY_SQL!

Hi ,

I am not using DESKI , I am using web intelligence

regards

---

GBS74 (BOB member since 2007-01-23)

[system]

measures are aggregated as well.

SALE.REVENUE 

and

COMMISSION.TOT_COMM

Have no SQL aggregation functions defined, contrary to what you previously said, therein lies your problem.

This also explains this behaviour:-

Re: Multiple SQL statements for each measure

I have enabled and disabled number of time , but does not see any impact on query. Results of experiments :

1. multiple measure from same fact table : only one sql statement appears. It does not metter ,if multiple SQL statements for each measure is enabled or disabled
2. multiple measure from same 2 different fact table : two sql statement appears. It does not metter ,if multiple SQL statements for each measure is enabled or disabled.

---

Mak 1 (BOB member since 2005-01-06)

[system]

Sum(SALE.REVENUE)

Sum(COMMISSION.TOT_COMM)

You now have SQL aggregation applied, now try everything you were playing around with again.
Ultimately you will want to set the settings as both Andreas and I suggested. i.e. Andreas comment:-

A) Disable: multiple SQL statements for measures!
B) Enable: multiple SQL staements for contexts!

---

Mak 1 (BOB member since 2005-01-06)

[system]

not working

regards

---

GBS74 (BOB member since 2007-01-23)

[system]

Post the generated SQL code, maaahn

---

Andreas (BOB member since 2002-06-20)

[system]

I have compiled all my previous reply and initial post into one, to avoid understanding gap( perhaps I have not explained problem in right way)

Environment:
I am using connection for oracle 11 .
Universe design tool version is 14.0.4.738.
Web intelligence :SAP Businessobject Launch Pad 14.0.4

Universe:
I have two fact tables SALE and COMMISSION, and Dimension :CUSTOMER is a common dimension joined to both fact table.

• Fact :SALE is connected to other two dimension (d1 and d2 ),
• Fact :COMMISSION connected to one other dimension d3.
  ( both fact are different grain level)

I have defined two context one for each fact table.

Following settings are in universe parameter

• Multiple SQL statements for each context - ENABLED
• Multiple SQL statements for each measure - DISABLED
• JOIN_BY_SQL to ‘Yes’

Measure Object SALE.REVENUE
Type : Number
Qualification : Measure
Projection function : Sum

Measure Object COMMISSION.TOT_COMM
Type : Number
Qualification : Measure
Projection function : Sum

(as you see object are aggregated as projection function SUM is used)

SQL: when you choose four object
(two from customer dimension :- Customer_name and Address, one from SALE fact Revenue, one from COMMISSION fact tot_commission) in webi report . In query you can see following two SQL. Data displayed in report is aggregated and correct.

Following two SQL appeared in query :-

Qry_1:-

SELECT  DIM_CUSTOMER.CUSTOMER_NAME, DIM_CUSTOMER.ADDRESS, SALE.REVENUE 
FROM DIM_CUSTOMER, SALE 
WHERE ( SALE.CUSTOMER_SKEY=DIM_CUSTOMER.CUSTOMER_SKEY ) 

Qry_2:

SELECT DIM_CUSTOMER.CUSTOMER_NAME, DIM_CUSTOMER.ADDRESS, COMMISSION.TOT_COMM 
FROM DIM_CUSTOMER, COMMISSION 
WHERE ( DIM_CUSTOMER.CUSTOMER_SKEY=COMMISSION.CUSTOMER_SKEY ) 

Problem &
what I want to achieve :-
SQL generated is not reflecting differently, two issues are there:

1. SQL generated does not show SUM function ( eg. sum(sale.revenue))
2. Want to see one complete SQL instead of two.

Result produced are correct , it look like aggregation of facts and merging results from two SQL are happening at business objects tier level that is why it is displaying two SQL.
As a solution I have set parameter JOIN_BY_SQL to ‘Yes’. But still I am not getting right results.

[Edited: applied various formatting options. Thanks, Andreas.]

---

GBS74 (BOB member since 2007-01-23)

[system]

So many posts have been edited, I can’t follow what’s being going on in this post anymore…

But - you might have a projection fucntion for your measure, but are you sure you also have the word SUM in the object code? You need both.

Debbie

---

Debbie (BOB member since 2005-03-01)

[MarkP]

The problem is exactly what Debbie has said - you do not have your objects defined as SUM(SALES.REVENUE) do you? Your SQL suggests that the object is just defined as SALES.REVENUE - the projection has nothing to do with SQL generation but is about the behaviour of the measure object at report level.

[system]

Thanks guy for your patience, Finally I got my answer.

Thanks to all.

---

GBS74 (BOB member since 2007-01-23)