During tables linking we will have loops which will result in duplicate records in report, so to overcome we use Context and Alias.
To the extent I know both serve the same purpose but what is the difference between the two and which one is more effective.
One thing I am aware is alias creates more tables but all tables are of logical structure so is alias more useful that context?
This is kind of like asking, what's the more useful tool: a wrench or a screwdriver? It depends on the task at hand.
You are correct that aliases create additional logical tables. Sometimes that's the desired approach, but not always.
One way I approach the question is to first determine whether there are multiple logical dimensions for a single physical dimension.
For example, consider a fact table with two date keys: transaction_dt_key, completed_dt_key. Both of these are associated with a date_key field in a date_dim table. You would, of course, create a loop if you were to join both fact fields to the date dim table. In this case, an alias is appropriate -- you would alias the dim table, join the fact keys to the original and alias table, then create a new object associated with the alias table.
Another way to look at this example is that the Transaction Date and Completed Date are two different things. Therefore, it is appropriate to have them represented by two different objects, and it follows that this would be accomplished by an alias.
In this respect, the design in the universe will more closely match the logical design of the data mart rather than its physical design.
Contexts, on the other hand, are useful when the same dimension table is associated with multiple fact tables.
Consider this example: the model has
customer_dim
store_dim
sales_fact
returns_fact
Both fact tables have a customer_id and store_id field. Joining all keys would create a loop. In this case, a context would be appropriate -- one context to include sales_fact and the two dims, and the other context to include returns_fact and the two dims.