Advanced 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.
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.
Administration
