cancel
Showing results for 
Search instead for 
Did you mean: 

Loops in Universe Designer.

former_member186894
Participant
0 Kudos

Hi, which one is best method to resolve the loops Context, Aliases or Shortcut join.

will anyone please explain?

Thanks,

Imran

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member186894
Participant
0 Kudos

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

former_member4998
Active Contributor
0 Kudos

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

former_member4998
Active Contributor
0 Kudos

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/

http://www.dagira.com/tips/IDT_legacy_workflows.pdf

http://www.forumtopics.com/busobj/viewtopic.php?t=202771&view=next&sid=0e016b704140022dfcc43af39d699...

Former Member
0 Kudos

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.