JOIN_BY_SQL and its Performance Consideration.
What does JOIN_BY_SQL do?
JOIN_BY_SQL is a one of the parameter in Universe Parameters. Its included to define how to handle multple SQL. When multiple JOIN_BY_SQL is set to YES
Business Objects will combine the the results of SQL’s if database permits it, When JOIN_BY_SQL is set to NO Business Objects will not combine the SQL and NO is the default value of JOIN_BY_SQL.
When do we get Multiple Queries and effect of JOIN_BY_SQL on it
While designig a Universe, I am sure you must have encountered join path problems and various traps like chasm traps, fan trap to solve this we have to use alias or contexts and separate query for measures. In situation like this, Business Objects generates mutple queries and synchronizes the queries in a single cube. However this can result in slow running report as each query can bring lots of record (e.g. 2M) and combining them into a single cube can be performance intensive task resulting slow running report.
To avoid this we can use JOIN_BY_SQL which uses JOIN_BY_SQL and push the sunchronization of resultsets on to database side instead of BO side. This can
help significantly to imporve performance as database servers are more powerful and pushing synchronizatin on DB side will reduce the load of BO servers resulting imrpoved report performance.
JOIN_BY_SQL uses FULL OUTER JOIN to combine the reultsets as
e.g.
These two query
SELECT DISTINCT
Article_lookup.Family_name,
sum(Article_lookup.Sale_price)
FROM
Article_lookup
GROUP BY
Article_lookup.Family_name
SELECT DISTINCT
Article_lookup.Family_name,
sum(product_promotion_facts.promotion_cost)
FROM
Article_lookup,
product_promotion_facts
WHERE
( Article_lookup.Article_id=product_promotion_facts.Article_id )
GROUP BY
Article_lookup.Family_name
Will look like as follows after enabling JOIN_BY_SQL
SELECT
NVL( F__1.Axis__1,F__2.Axis__1 ),
F__1.M__262,
F__2.M__194
FROM
(
SELECT
Article_lookup.Family_name AS Axis__1,
sum(product_promotion_facts.promotion_cost) AS M__262
FROM
Article_lookup,
product_promotion_facts
WHERE
( Article_lookup.Article_id=product_promotion_facts.Article_id )
GROUP BY
Article_lookup.Family_name
)
F__1
FULL OUTER JOIN
(
SELECT
Article_lookup.Family_name AS Axis__1,
sum(Article_lookup.Sale_price) AS M__194
FROM
Article_lookup
GROUP BY
Article_lookup.Family_name
)
F__2
ON ( F__1.Axis__1=F__2.Axis__1 )
well, after reading this dont just go back and turn on the JOIN_BY_SQL. There are cases when JOIN_BY_SQL might degrade your performance than improving it. You should evaluate it for any of your report which is slow performing and which has multiple queries and then turn on the JOIN_BY_SQL and then evaluate the performance.
How to set up JOIN_BY_SQL
JOIN_BY_SQL can is Universe parameter. open the Universe Options dialog and click on Parameters tab and you can find it there, if its not there just add it
JOIN_BY_SQL = YES/NO
I would like to know your questions and experience on JOIN_BY_SQL topic.
Subscribe to Email to receive all the FREE updates and Business Objects Interview Questions and Answers FREE!!
Related posts:
{ 1 comment… read it below or add one }
nice post Kuldeep.