Dave’s Adventures in Business Intelligence

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