on 09-08-2014 8:28 PM
Hi, which one is best method to resolve the loops Context, Aliases or Shortcut join.
will anyone please explain?
Thanks,
Imran
Thanks Sreenivas and Niraj, I really appreciate you both gave great idea. My concern is on Shortcut join. As Sreenivas has said that
Loops can be resolved using Alias or Context.
Yes I agree, we do resolve loops using Aliases or Context. We can resolve loops using shortcut join too. Is it good idea using shortcut join instead of using Alias or Context to resolve the loops?
Thanks,
Imran
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi
we do resolve loops using Aliases or Context. We can resolve loops using shortcut join too. Is it good idea using shortcut join instead of using Alias or Context to resolve the loops?
It’s depends on your requirements..
while resolve the loops used shortcut join and get the expected results then it’s good, if you get different result then expected , you need to choose different method.
Alias:
The alias is identical to the base table with a different name. The data in the alias is exactly the same as the original table, but the different name "tricks" SQL into accepting that you are using two different tables.
For example: we have only one table Employee table, have fields EMP ID, EMP Name, and Manger Name.
Now we need to list the EMP name and Manger Name’s.....how you get the list EMP name and Manger Name? Usine Shorcut join?
Here you need to go for alias for employess table, SQL look’s like
SELECT emp.name, mgr.name
FROM employees emp, employees mgr
WHERE emp.mgr_id = mgr.emp_id
Context:
Contexts groups related Joins. A context is a collection of joins which provide a valid query path.The most common use of contexts is to resolve loops in
the data foundation when the loop cannot be resolved by creating an alias table.
Another use of contexts is when multiple fact tables share a dimension table. In this case, a context is created for each fact table
As said by Dave ,
You would not use a hammer to put in a screw because a screw-driver is “too complex” so why would you use the wrong tool to resolve an issue in a universe? It doesn’t make sense.
Find the below link for more info.
Dave’s Adventures in Business Intelligence » Everything About Shortcut Joins
Note: PLEASE mark blog as helpful & answered
Hi
A loop is a set of joins that defines a closed path through a set of tables in a schema
The rows that are returned from the query are the intersection of the results for each path, so fewer rows are returned than expected.
Loops can be resolved using Alias or Context
Alias: An alias table is a reference to a standard or derived table in the data foundation. It is an identical duplicate of the original table (except for column filters), but has a different name.
Context: A context is a collection of joins which provide a valid query path
Shortcut Join: join provides an alternative path between two tables, bypassing intermediate tables, leading to the same results, regardless of direction, Optimizes query time by cutting long join path as short as possible. Short cut join does not create loop and cannot be placed in any context.
So based on the your requirement you have to choose correct approach.
Find the below links for more info.
http://www.dagira.com/2009/07/22/context-versus-alias-overview/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Imran,
Contexts and Aliases have their own purpose and are not substitutes for each other.
Refer the below article:
http://www.dagira.com/2009/07/22/context-versus-alias-overview/
Hope this helps:
Regards,
Niraj.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
70 | |
26 | |
9 | |
9 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.