Aug 03 2007

Designer SQL Traps

Categories: Fan / Chasm Trap,Universe Design Dave Rathbun @ 9:06 am

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.

13 Responses to “Designer SQL Traps”

  1. Comment by Marg August 23rd, 2007 at 4:40 am

    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 –

  2. Comment by Dave Rathbun August 23rd, 2007 at 8:29 am

    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. 🙂

  3. Comment by Marg August 24th, 2007 at 5:23 pm

    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 🙁

  4. Comment by AVRIL November 22nd, 2007 at 10:10 pm

    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.

  5. Comment by Dave Rathbun November 29th, 2007 at 1:10 am

    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. 😕

  6. Comment by AVRIL December 12th, 2007 at 9:41 pm

    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.

  7. Comment by Mikhail October 14th, 2011 at 4:41 am

    these definitions are completely opposite to http://db.grussell.org/section005.html http://en.wikipedia.org/wiki/Relational_model#cite_note-7 pls let me know what does it mean?

  8. Comment by Dave Rathbun October 14th, 2011 at 7:48 am

    The Wiki article you linked agrees with my description, however you linked to the wrong area. I believe you meant http://en.wikipedia.org/wiki/Relational_model#Modelling_issues instead. The other article you linked is incorrect.

  9. Comment by Mikhail Khludnev October 17th, 2011 at 2:11 am

    I looked into wiki again it opposite to yours. And one more reference can be easily googled http://www.google.com/search?q=Oracle+Discoverer%27s+Fan+Trap+Resolution the first result is old pdf from Oracle.
    they depict fan as Sales >- Account -< Budget and
    you draw "fan" as A -< B -< C

    Could you give me a link to your reference materials ?

    Best wishes

  10. Comment by Dave Rathbun October 17th, 2011 at 7:27 am

    My primary reference is the standard Business Objects training material. If you read the description of each kind of trap, it logically makes far more sense to define a fan trap as A -< B — A -< C if you ask me. If you want to call them something different go ahead. 🙂

  11. Comment by Shantanu April 3rd, 2012 at 12:11 pm

    Hi Dave,

    I am new to business objects and had gone through your Fan Trap article. The information on Fan trap is framed in a very easy to understand way. It was very helpful.Thanks for that !

    It would be a great help if you could provide such article on Chasm trap

    Regards,
    Shantanu

  12. Comment by Trisha April 3rd, 2014 at 8:43 am

    I have a horrible trap. I am wondering if there is a resolution to this situation. it’s a 1:Many and 1:many like a chasm, but the middle table is a dimension, and the outer two are dim and fact. the textbook resolution does not apply when a dim is in the pic rather than two measures. ugh.
    i have a user table (one rec each user id) and it joins to FactViews and then also to Territory. So, the user id has many rows in the FactViews table and it also has many Territories in the Territory table. It results in the same overinflation. 2 contexts and an alias do resolve it for a typical query in the same way the textbook example does. BUT.,
    and a big BUT. when you try to prompt/query filter….. on the Territory…… that filter is only applied to the query that involves territory. the second sql stmt generate to just get the user level info + measure DOES NOT get filtered !!! so you don’t get the right answer back to your result set. You get every user id that matches rest of query, and it doesn’t get filtered to the selected Territories.

    i cannot find any info about this situation and am having nightmares of pictures of tables… this cannot be an uncommon situation.
    Thoughts?

  13. Comment by Dave Rathbun April 8th, 2014 at 1:52 pm

    Yes, the same thing can occur in a Fan Trap with a 1:Many:Many issue, and a condition is applied to the middle table. What I have done in that case is generate a pre-defined condition that uses a sub-query to isolate the problem table.

    So in your case, you would have something that looks something like this:
    Select stuff
    from user, fact
    where user_id in
    (select user_id
    from user, territory
    where territory = your_value_here_or_prompt)

    The list of users is based on territory assignments, but is immune from the inflation because the territory table is never used in the main query.