Measures, and One-to-Many Table Relationships

[system]

Here’s a problem I commonly have when designing a universe. I am not sure what design strategy to use to deal with it. Consider a this simplified example:

I have two tables:

1. Country, with objects Country and Population
2. City, with objects Country, City and Population.

Both tables are linked on the country field. Of course, each country can appear many times in the City table. Now, ff a user creates a WebI query for population by City, and accidentally includes Population from the country table, he will see inaccurate results - each city will be assigned the population of its country.

How do I design my universe to limit the chances of a user including measures from the wrong table in their queries?

---

adricey (BOB member since 2008-07-09)

[system]

Hi,

It looks to me that the problem you experience is a fan trap.

What is a fan trap and how to solve it in a universe can be found for instance in the Designer’s FAQ:

FAQ: Designer

What is a fan trap, why is it a problem, and how do I resolve it? Dave Rathbun describes it well in his presentation, ZEN and the ART of Universe Design. Which is located here: http://www.dagira.com/conference-presentations/ Here is a quick synopsis of Fan Trap from his presentation: A Fan Trap is a one - many - many relationship. This would happen if you are using a Summary and a Detail table in one query. Fan Traps can cause numbers to multiply. Business Objects resolves this t…

There are of course many topics about fan trap on this board that were discussed in the past. Just use search to find them.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Hi,

Apart of the Fan trap solution, you can also look for @aggregate function to create your measure objects, which helps in selecting the correct measures based on the dimension selected in query.

Regards,
Saurabh Gupta

---

saurabhg (BOB member since 2007-04-02)

[MarkP]

@saurabhg

Hi,

Apart of the Fan trap solution, you can also look for @aggregate function to create your measure objects, which helps in selecting the correct measures based on the dimension selected in query.

Regards,
Saurabh Gupta

Why would you do something or than the standard solution? It’s been the recommended solution for years because it works.

[system]

Had no idea the issue even has its own name. Thanks much for your help. I also found a helpful video (http://www.youtube.com/watch?v=NJShChQF4Ec) about the issue.

---

adricey (BOB member since 2008-07-09)

[system]

@adricey

Had no idea the issue even has its own name

Of course it has its own name It’s a very common issue.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

I’m still a novice. I think all my issues are unique (or are my fault)

---

adricey (BOB member since 2008-07-09)