Tuesday 20 March 2012

Advanced Join Property


Advance join property in universe is present , when you click a join between any two tables and click on Advance button.
You need to have ANSI92, set to “Yes”, to enable this feature.
So, the question is, what exactly this feature do?
First of all let’s look what is the execution order of  a SQL statement:
When a query is submitted to the database, it is executed in the following order:
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
So if I use my conditions in FROM Clause instead of WHERE Clause, it will be calculated first and can help in performance improvement.
Take for example in my universe:










If I select c_name object from customer table and sale_value from sales table and use c_name as a filter, the query will be: 










It can be seen that the filter condition is coming in WHERE clause.

The Advanced Join Property allows to the conditions to be used in “FROM” clause.
Again as I said previously, you need to have ANSI92=”Yes”  in your universe parameters for this to work.









Now we will set the advanced join conditions.
Click on the join between the two tables:





The drop down option says it what kind of behavior you want from objects selected in query filter.
Lets say I selected “Selected objects in FROM” in drop down and checked on C Name dimension object.



Now I will create the query again:







See how the filter which was coming in WHERE clause is coming in FROM clause.
I went through number of forums that tells how conditions in FROM clause helps in query performance compared to WHERE clause.
Although both types (i.e. filters in FROM clause and filters in where clause) generate the same results but there are scenarios specially when you use Left Outer joins in your query, that can lead to difference in results, which needs to be taken into consideration while using this option in your universe.



5 comments:

  1. Hi

    Greetings!!! Nice explanation...But i have one doubt is that why do you define as customer.c_name = sales.c_id...because both variables are different data type where one is character and another one is number?..

    Thanks in advance

    ReplyDelete
    Replies
    1. Nice catch ganesan...Its by mistake...The focus was on filter condition , so I missed the join thing....

      Delete
  2. Where can we find this option in IDT 4.1,as I am not seeing it in Joins window.

    ReplyDelete
    Replies
    1. Did you find it? Been looking all over....

      Delete