Aug 03 2007
Designer SQL Traps
Designer is one of my favorite parts of the Business Objects product suite. I enjoy creating universes and solving the challenges that occur during that process. If you have worked with Designer yourself then you probably know that there are several different types of challenges that occur fairly frequently… so frequently, in fact, that we have names for them. They are Chasm Traps and Fan Traps. I plan to describe each of those traps in this post. I will provide various different solutions (some standard, some not so much) for each in future blog posts.
What is a Chasm Trap
Simply put: A chasm occurs when a series of joins crosses a many >- one -< many relationship. For example:
A >- B -< C
If I were to write a query that spanned all three of those tables the data from table A and C would be duplicated and measure values would be inflated. The inflation factor can be determined by counting the number of rows from the left that match a particular key and multiplying by the number of rows on the right. For example, a chasm with 3 rows from the left and 2 rows from the right will have 6 total result rows. Any measures taken from this set of data will be inflated. Table A rows will be doubled (from the 2 rows in table C) and table C rows will be tripled (due to the 3 rows from table A).
If you’re having trouble visualizing this at the moment, don’t worry. I will have some specific data to talk about when I provide solutions to this issue.
What is a Fan Trap?
Again, to put this simply: A fan trap occurs when joins “fan out” over multiple one -< many relationships in a row. For example:
A -< B -< C
A fan trap is not quite as severe as a chasm trap. In fact there are many fan traps that can occur in a universe design that can be ignored as long as you control which types of objects you use. However, if you are not careful then it is possible to return inflated measure values from a fan trap just like in a chasm trap.
How do you recognize these situations? How do you fix them? Stay tuned, as I plan to cover each trap in detail along with various solutions in coming blog posts.
Dave,
I’d be interested to see some information on report formatting when crossing multiple contexts. A common request I come across is for information (both dimensions and measures) to be displayed in one block, when they come from different contexts. For example, say you have
Projects -
Hi, Marg, unfortunately your comment got cut-off. Wordpress doesn’t like characters like < and > in comments as it thinks you might be trying to post some inappropriate links. Unfortunately I don’t know of a way to fix that without breaking the ability to post legitimate links. What you can do is avoid those characters in your comments, or – like I did in this case – replace them with the html equivalent of & lt ; for less than and & gt ; for greater than. Simply put all 4 of those characters & lt ; together and it will turn out as <. My apologies for the confusion.
I do certainly intend to cover contexts in some detail as they are an essential tool for solving both fan and chasm traps. Hopefully I will provide your answer even if I don’t know the exact question.
Thanks Dave.
My question was along the lines of how to present information at the report level when you cross contexts – particularly when a lot of dimension information is required.
It’s not often possible to convert the dimensions to details.
I understand the logic behind using contexts particularly with chasm traps – I just find it hard to write neat looking reports. For some reason my users like reports that look like Excel
Hi Dave,
Tried searching for more content on Chasm and Fan Traps but didnt find any? Just wanted to know if there is any more content about the traps in detail.
AVRIL, this has been on my list to update for a while, but time constraints have prevented me from finishing. I have written some articles that are posted on the Integra Solutions web site, including one called “ZEN and the ART of Universe Design” as well as some older conference presentations that would be a start if you have not seen those.
Unfortunately, our site appears to be down at the moment, and I am not sure why.
Hi Dave,
Thanks for letting me know about“ZEN and the ART of Universe Designâ€. It is good for a start as i am new to Business Objects. And while making universe the core problem for me has been resolving loops. I really get confused about when to use aliases and when to use context when there are so many tables (atleast 10+) and its just not one lookup where i can use alias and contexts for others. This has landed me making mistakes. I would really appreciate any valuable inputs from you as i need some help to get these basics very clear.
Regards,
Avril.