Jul 22 2009
Context Versus Alias Overview
In an attempt to complete the blog posts related to my GBN Conference presentation from 2008 before the upcoming conference in 2009 kicks off I bring you Context Versus Alias: The Smackdown Cage Match. 😆 People often post on BOB as if contexts and aliases are competing for the same job, and that is certainly not true. Each technique has an appropriate use, and I will cover them (although not in much depth) in this post. I won’t be showing the process for creating contexts or aliases in this post as it would end up being too long.
This post is related to slides 13 through 17 from my 2008 conference presentation.
What is an Alias?
The alias technique has been around far longer than Business Objects has. Whenever a SQL script needs to access the same table more than once an alias is used. One of the easiest examples is an employee table that contains a key to the employee’s manager. In order to return a list of employees and who they work for I might write SQL code something like this:
SELECT emp.name
, mgr.name
FROM employees emp
, employees mgr
WHERE emp.mgr_id = mgr.emp_id
There is only one table in the code: the employees table. However, in order to get the employee name and the manager name, I need to join the table back to itself. In order to accomplish that, I have to give each side of the relationship a unique name. That is the SQL alias for each table, and the same technique is used in building universes.
Aliases are more typical in a universe environment with lookup tables. For example, in the screen shot shown below (taken from my presentation) the COUNTRY table has two unique purposes. In one case it is used to show where a Resort is located. In the other case it contains the Region for each customer. An alias is indicated by the fact that the COUNTRY table is always on the “one” side of a one-to-many join. This issue (and solution) can be found in the Island Resorts universe.
To summarize so far: an alias is used when the same table is used more than once, and is typically indicated by that table always being on the “one” side of a one-to-many join.
What is a Context?
A context, on the other hand, is a concept that probably originated within a universe. If a SQL developer decides that he or she needs two scripts to get the job done, then that’s what they write. But a universe is supposed to be able to generate SQL for a non-developer. So how can it have the intelligence to know when to split something into multiple SQL passes? That is the role of a context.
Here is another screen shot borrowed from my presentation.
In the diagram shown above, the two end points are CUSTOMER and SERVICE. There are two ways to figure out which service a customer is related to. The first relationship goes through the reservations tables and is used to show which services a customer has requested. The second relationship goes through the sales tables and is used to bill the customer for services used. These relationships are equally important.
A context is indicated in this case because there is a loop. (Please note, however, that contexts also solve fan and chasm traps which do not involve loops.) A loop in a universe is found when a sequence of joins can be selected that start and end at the same table. In this case, I could map from the CUSTOMER table through the INVOICE_LINE table, the SERVICE table, back through the reservations tables and end up back where I started. That’s a loop. If the loop is not resolved in some way, Desktop Intelligence and Web Intelligence will issue an “incompatible combination of objects” message rather than run the invalid SQL.
An alias was indicated by tables on the “one” side of every join. Contexts are indicated by tables appearing only on the “many” side of a one-to-many join. In this loop, those are the RESERVATION_LINE and INVOICE_LINE tables. Those tables will ultimately drive my context creation process.
To summarize contexts: they are used to resolve loops (and other SQL traps) and are indicated by tables always appearing on the “many” side of a one-to-many join.
Context Versus Alias
I have shown examples of where each technique would be appropriate. Is it possible to use an alias to solve a context issue, or a context to solve an alias challenge? To be honest, yes. 🙂 But you would end up creating more problems than you solve. Here’s a summary of the two features:
- Alias Features
- Aliases are simple to create
- Objects for each alias can be created, for example Resort Country and Customer Country from my example given above
- Once created no further maintenance is required
- Aliases eliminate (break) loops
- Context Features
- Contexts are more complex to create
- Every join must exist in at least one context; if not, it becomes an isolated join
- Loops are not eliminated; two (or more) paths are identified and used but the loop remains
- Contexts can also resolve fan or chasm traps
I have seen more than one post on BOB where people say things like, “… my client doesn’t allow contexts…” or “… won’t use contexts because they are too complicated…” and I cringe. 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.
Conclusion
Aliases and contexts are not substitutes for each other: each has a unique purpose and a place in universe design. I tried to show that in my brief overview slides in my presentation, and hopefully have added some value by including more detail in this post. I have other posts on contexts and aliases and other universe design issues published, and a few more in draft form that I hope to finish as time allows. The complete presentation can be downloaded from my presentations page, linked below.
Related Links
- Conference presentations page
- Universe Designer Essentials and Beyond conference presentation
Very well written !!
and very informative.
Thank you
Very well framed Dave!
I have a query. I need to know how we can connect 2 or more star schemas consisting of fact tables.
For replicating a business functionality in the universes we designed multiple star schemas with fact and dimesion tables.Here in all schemas we have some common tables also those are used as Aliases.
Now to interrelate all these star schemas should i use context or aliases in fact table or can there be any way out.
Thanks
@Ashish:
—————
If you have common dimension tables (a.k.a. conformed dimensions) for your various fact tables, then you should use universe contexts and not alias tables! This way you can reports various facts against the same dimension table (conformed dimension).
Hi. I am new in BO and having a problem recognizing loops and understanding the problem loops represent in BO.
To me the example of the loop above is the representation of the two separate M:M relationships between Customer and Service. The Invoices and Reservations are just details of these relationships.
The “select * from C, R1, R2, S where C.cid = R1.cid and R1.sid = S.sid and C.cid = R2.cid and R2.sid = S.sid” seems reasonable to me; it allows to return all cases when a service was reserved by a customer and was invoiced to that customer. Why is this a problem?
I see that the case you presented also has two Chasm Traps in it that needs to be resolved anyway, but I am trying to understand loops for now….
Could you please give more examples of the loops.
Will it be a loop if the relationships are (C 1:M R1 1:M S) and (C 1:M R2 1:M S)?
Will it be a loop if the relationships R1 and R2 use different alternative keys in S?
Thanks.
Hi, Vlad, and welcome to my blog.
The primary issue with unresolved loops is that Business Objects will generate SQL that takes every available path at the same time. Thus, you will get SQL that uses both paths (service + invoice).
It’s easier to explain with the original COUNTRY example. Each customer has a country. Each resort has a country. There is a loop (not shown in this post) from Country to Region to Customer to … to Resort and back to Country again. If that loop is not resolved, the entire path is used for every query. The result? You will only get rows where a customer visits a resort in the same country where they live, as otherwise the join path will fail.
Dear Dave, I’ve a question:
How do i identify when to use a Alias and when to use a context.
As per your blog above
“An alias was indicated by tables on the “one†side of every join. Contexts are indicated by tables appearing only on the “many†side of a one-to-many join./”
so as per the above statement….in any M:1:M OR M:M condition i need to use both(mandatorily) to resolve the issue completely. Is it true?
But if i break a loop using Aliases then why do i need a context?
(I know you’ll ask me now .. what if i need data from tables wherein one table is at the begining and the other is at the end of the joining path?)
Shortcut join can resolve this(I guess)
but that would form a loop again? For which again we’ll need a context.
As we need to tell BO that i need this context(joining path) to get my data?
This in short means we always need both (aliases and contexts) to resolve a M:M cardinality completely??
Please Clarify.
Thanks a bunch.
Shiva
Many to One to Many is a Chasm trap, which is easily resolved with two contexts, one for each side of the relationship.
“Many to Many” logical relationships are generally implemented with an extra table so you end up with 1:M:1. The “M” table becomes the “fact” table in this case and requires a context.
No aliases are required in either of those situations.
Shortcut joins are not a loop resolution mechanism. They are used to skip tables for performance reasons only. Shortcuts should not be added to a universe until everything else is verified and working. 😎
Thanks Dave,
Thanks for the reply. I’m still going through your blogs… currently i’m on JOIN_BY_SQL….. really interesting.
I’m just done with my office work. 😀
Your blogs are not only interesting but the way you’ve narrated them they become really catchy.
When can we see your next post on chasm trap… and also i want to know which joins i should select in a context?
Thanks again. 🙂
Regards,
Shiva
Dave:
Do you have any good example to Understand the Aggregate Awareness and Aggregate Navigation, this will be helpfull.
Thxs,
Suresh, I have not really covered the aggregate awareness feature so far. I did some presentations years ago on that topic but haven’t had a need to use it much recently as we’re making use of Teradata’s AJI (Aggregate Join Index) feature as a replacement. At some point I may get some time to try to cover this feature. Thanks for the suggestion.
Hi Dave,
I read alot of your posts and they are really awesome and your posts really helped me in some tricky situations. i have one issue presently while designing the universe i have two fact tables say A and B and i have 5 conformed dimension tables and additional 3 dimension tables which are joined to B. i have a senario where i need to use a measure from B via A at present we have a join between two fact tables and there is only one measure in B. I created two contexts for A and B at present for now performance is degraded because i have a join between two fact tables(both are huge) how can i get the measure from B via A without involving the join between the two fact tables?
HI,
Is it possible to create restrictions against alias tables in the Universe? I am having a lot of issues with this. Any help would be very appreciated.
thank you.
Hi, I’m not sure what you mean by “restrictions.” If you mean pre-defined conditions, then certainly you can base these off of alias tables just like you can off of regular tables.
what do you mean by “Every join must exist in at least one context; if not, it becomes an isolated join”?
see, if I have created context for some joins in the universe then, do I need to include the remaining joins within the same universe structure to any of the context i.e. can I keep some joins not to include in the context and some joins in the context.
example:
join 1-5: included in one context C1
join 5-10: includes in another context c2
join 11-16: not included in any context
All the above joins are in same universe. Is it possible??
Once you have implemented contexts in your universe, in 3.x and earlier, every join must appear in a context or it is identified as an isolated join during an integrity check.
Possible? Yes. In 4.x it’s allowed. In 3.x it’s allowed but will show up as an error.
Hi Dave,
I am trying to understand alias vs contexts in resolving loops.
In the Example given by Dave in http://www.dagira.com/2009/07/22/context-versus-alias-overview/, can you please tell me why did he not break the loops using alias for Customer table in the contexts example? Customer table is also a look up table and in this case since the loop is caused by a single look up table why can we resolve example 2 with alias? Please explain? How is it different fro first example. This is the most famous example given in the Universe designer too and I always get confused with this contexts example.
Louis
1) I have 4 tables which have many to many relations between the tables? How you will fetch correct results in the report?
i)how many min no.of objects reqd to form a loop to which he answered 4(Is this right)
ii)if there is a loop of dimension(D1)-fact(F1)-dimension(D2)-fact(F2), then what would be
a>No of contexts possible in this scenario
b>do we need alias in this case.if at all then waht and where would they be required ?
Fix the data model and then write your report. 🙂 Seriously, a many-to-many relationship is not valid for reporting without placing a restriction on one side of the relationship or the other. It implies a data model issue.
You can have a loop with only a single table. 🙂 In your second scenario, it appears that the standard rule of “one context per fact” would hold true, so you would have two contexts. You would not want aliases of the dimension tables as that would lead to duplicated objects.
Hi dave,
As you said when ever we access the same table more than once need to create an alias, so to do the same task why cant we use self join
You can use a self-join (perhaps with a prompt) if you only want to use the table for one purpose at a time. But if you want to use the same table more than once within the same query – for a different purpose – then you need to use aliases.
It all depends on how you want to treat customers. If you have only one customer, who can both rent or buy a car, then you need a context. If you have separate customers, one called “Rental Customer” and one called “Sales Customer” then an alias could be inserted instead. But keep in mind if you alias the customer, you also have to alias every table that comes after customer (Region, Country) as well.
Hi dave, your blog is really very helpful . I have doubt on solving loop,why do we create alias of one end why not of many end? my second and last question is after breaking loop using alias ,from where we need to create object alias or original table?
Hi, jay, it’s because of how the tables are used. If something is on the “one” side of all of the joins, it’s typically being treated as a lookup table. Lookup tables typically have multiple purposes, so aliases work great. If something is on the many side of joins, it’s more often in the middle of things, and creating an alias would not solve the loop directly but simply move it to another place.
When creating aliases to solve a loop, objects are going to be created from the alias and no longer from the original source table (which typically isn’t joined to anything any more).
Hi Dave,
I have searched many forums regarding why loop will get less number of joins. But i disn’t get the correct answer. It would be better if you write a post with clear example about loop and how number of records are reduced. And if my requirement is to get the result by joining A to B, B to C and C to A then why i have a constraint in universe to join A to B, B to C and C to A.
Reddy, the “classic” example is from the original Motors training database from Business Objects. A Showroom has a country. A Customer has a country. A Showroom sells a car to a customer. Now if you only use one instance of the country table, then the only way the entire set of joins will be true is when a customer buys a car from a showroom that is located in the same country as the customer. If a customer from England buys a car from Germany that transaction won’t show up. Make sense?
Hi Dave,
Have a question on one of the comments you have given above which is :-
It all depends on how you want to treat customers. If you have only one customer, who can both rent or buy a car, then you need a context. If you have separate customers, one called “Rental Customer” and one called “Sales Customer” then an alias could be inserted instead. But keep in mind if you alias the customer, you also have to alias every table that comes after customer (Region, Country) as well.
You said, if we create alias, we need to create alias for every table that comes after Customer. Does this mean we have to create alias for all following dimension objects.
I am still not very clear when to use alias and Contexts. Do you have any postwhich I can read for better understanding.
Thanks
Think about it this way. In the core (middle) of the Motors universe are two tables: Sales Invoices and Rental Transactions. On one side is the customer, and on the other side is the car. The path from Customer to Rental to Car to Sales and back to Customer forms the loop.
Customer is also joined to Region and then to Country. If you alias Customer as I suggested, then the loop just moves further out – to Region. If you alias Region, then the loop terminates at Country. So if you alias Customer, the only way to truly break the loop is to alias every table on the “left side” of Customer as well. That’s what I was trying to say.
My question is regarding object redundancy.In universe, we create tables and also aliases of tables(to avoid context issue). In this way many objects are created(duplicate objects for example one object using one column of the table and another object using the same column of the alias). So, is there any way so that this object duplication can be avoided.
If you use aliases to “avoid the context issue” then you’re going to have to live with that choice. 🙂 Aliases are not a substitute or alternative to contexts; there is a reason to use one over the other. When aliases are the appropriate choice, you generally want to have separate objects.