Fan Traps
posted by Mark @ 5:29pm, Wednesday 15 September 2010.
Introduction
As my previous article alluded, much of the information found on the Web about Chasm Traps and Fan Traps is in-complete, very specific, or simply too brief.
In this article on Fan Traps I hope to provide more generic examples and specifically provide supporting SQL scripts that you can play with and try to resolve yourself.
An understanding of Dimensions and Measure is useful at this point but not required.
So what is a Fan Trap?
Don’t worry if this part doesn’t make sense at first, that’s what the examples are for.
A Fan Trap is so called because the tables and their relationships ‘Fan Out’.
The most common example of this is 3 tables joined from left to right with 2 one-to-many joins between them, something like this (shoddy) diagram:
The diagram shows a Range table, an Item table and a weekly sales table. Each Range has many Items and each Item may have sold in 1 or more calendar weeks.
At first you may be asking “what’s wrong with those table relationships?”, and the answer is nothing. There is nothing wrong with the relationships in the diagram, and this is why it’s called a trap as there may be nothing obviously wrong.
To understand why the relationships are a Fan Trap, we need to look at how the data in those tables might be used to calculate aggregate figures. For example a problem occurs when we attempt to include the number of items per Range with the total sales of said items in a single report/result set.
Example (scripts available from the downloads area)
Assume the Tables have the following data and structures:
| Range | Rangedesc |
| 1 | Toys |
| 2 | Books |
| 3 | DVDs |
| Item | Itemdesc | Range |
| 1 | Ball | 1 |
| 2 | Teddy Picnic | 2 |
| 3 | Lion King | 3 |
| 4 | Bat | 1 |
| 5 | Pets Book | 2 |
| 6 | Shrek | 3 |
| Wk | Item | Sales |
| 200901 | 1 | 10 |
| 200901 | 2 | 20 |
| 200901 | 3 | 30 |
| 200902 | 4 | 10 |
| 200902 | 5 | 20 |
| 200902 | 6 | 30 |
| 200903 | 1 | 10 |
| 200903 | 2 | 20 |
| 200903 | 3 | 30 |
| 200904 | 4 | 10 |
| 200904 | 5 | 20 |
| 200904 | 6 | 30 |
The SQL to find out how many items are in each range could look like this:
SELECT
r.Rangeno,
r.Rangedesc,
COUNT(Item) AS ItemCount
FROM
Range AS r
INNER JOIN Item AS i
ON r.Rangeno = i.Rangeno
GROUP BY
r.Rangeno,
r.Rangedesc
The Result set looking like this:
| Rangeo | Rangedesc | ItemCount |
| 1 | Toys | 2 |
| 2 | Books | 2 |
| 3 | DVDs | 2 |
The SQL to find out total sales per Item could look like this:
SELECT
i.Item,
i.Itemdesc,
SUM(s.Sales) AS TotalSales
FROM
Item AS i
INNER JOIN WeeklySales AS s
ON i.item = s.Item
GROUP BY
i.item,
i.Itemdesc
The Result Set:
| Item | Itemdesc | TotalSales |
| 1 | Ball | 20 |
| 2 | Teddy Picnic | 40 |
| 3 | Lion King | 60 |
| 4 | Bat | 20 |
| 5 | Pets Book | 40 |
| 6 | Shrek | 60 |
So far you will have noticed that everything is correct, none of our figures are negatively impacted.
Now lets say that a Manager wants to see each Range, the number of Items in a Range and the Total Sales of all Items in a Range.
We could use the below SQL statement to achieve this.
SELECT
r.Rangeno,
r.Rangedesc,
COUNT(Item) AS ItemCount,
SUM(s.Sales) AS TotalSales
FROM
Range AS r
INNER JOIN Item AS i
ON r.Rangeno = i.Rangeno
INNER JOIN WeeklySales AS s
ON i.item = s.Item
GROUP BY
r.Rangeno,
r.Rangedesc
The Result Set:
| Rangeno | Rangedesc | ItemCount | TotalSales |
| 1 | Toys | 4 | 40 |
| 2 | Books | 4 | 80 |
| 3 | DVDs | 4 | 120 |
The above Result Set is incorrect. Although the SQL is completely valid and successfully executes returning results, the results are incorrect.
As we identified from our previous query, the ItemCount for our 3 Ranges should be 2, not 4 as shown above. This is caused by the Fan Trap.
As you may have spotted from the SQL statement, the issue is that we are aggregating the Item table and the WeeklySales table, however both are at different aggregate levels to begin with.
Simple solution is to use COUNT(DISTINCT(Item)). Another solution would be to perform separate aggregate queries and join the Result Sets together:
SELECT
r.Rangeno,
r.Rangedesc,
i.ItemCount,
s.TotalSales
FROM
FAN_Range AS r
INNER JOIN (
SELECT
i.Rangeno,
COUNT(i.Item) AS ItemCount
FROM
FAN_Item AS i
GROUP BY
i.Rangeno
) AS i
ON r.Rangeno = i.Rangeno
INNER JOIN (
SELECT
i.Rangeno,
SUM(s.Sales) AS TotalSales
FROM
FAN_Item AS i
INNER JOIN FAN_WeeklySales AS s
ON i.Item = s.Item
GROUP BY
i.Rangeno
) AS s
ON r.Rangeno = s.Rangeno
Both are easy if you are hand coding SQL for reports and data requests, in-fact I suspect many professionals would know the data and automatically write the correct queries working around the Fan Trap issues without even thinking about it.
Why you need to know about Fan Traps?
Although you may well already work around Fan Traps in your hand coded SQL, its still worth while having an understanding of them so that you can identify them faster, and actually start trying to avoid them.
The examples here are very simplistic, but as queries get complicated joining various tables, knowledge of Fan Traps can help you avoid becoming a victim of them even when on first run the query appears to perform as expected.
Perhaps a bigger issue is that most off the shelf Reporting Tools (Business Objects etc.) generate SQL statements based on data models (eg. BO Universes), and therefore need to be told how to manage Fan Traps.
Without a good understanding of Fan Traps a data model could be designed that passes preliminary testing, but later gets caught out providing erroneous figures.
Solutions
As shown above, if you are in complete control of the SQL Query the solution is straight forward.
Data model solutions are a little tricky as the solution can depend entirely on your data model and the reporting software used.
The key is to be consistent in your approach to aggregates, ensuring you define aggregates for all measures.
Also where possible define aggregate priorities. In some cases an aggregate could be calculated in multiple ways, define them and the priority.
Unfortunately it would be impossible to list solutions to every scenario here, but the key is if you have an understanding of Fan Traps you can solve them or work around them in your given situation.
Conclusion
Hopefully by now you have a better understanding of Fan Traps, and are better equipped to deal with them.
If you have any questions, about the Article, please leave a comment and I will try to get back to you, or alternatively, get in contact via the Contact page.
Remember to check out the resource links from the Fan and Chasm Traps Article.
PCI-Express Scaling 1.x vs 2.x
posted by Mark @ 8:41pm, Sunday 7 March 2010.
"Will my motherboard only supporting PCI-E 1.1 be a limiting factor, and if so by how much?"
Thanks to a faulty driver from NVidia (196.75) overheating my graphics card (9800GTX) I am now in the market for a new one.
As many of my friends and colleagues know I like to spend time making sure I am getting the best I can for my budget. What this means is I will spend hours scouring the internet and magazines for reviews, articles and specifications, eventually making a choice.
This time round I do not have the luxary of time on my side, however it hasnt stopped me from doing a good bit of research, and one of the questions I had was will my motherboard only supporting PCI-E 1.1 be a limiting factor, and if so by how much?
By far and away the best article I could find on this was one from techPowerUp:
AMD Radeon HD 5870 PCI-Express Scaling
The short answer is that the performance difference between a PCI-E 1.x 16x port and a PCI-E 2.x 16x port would work out at about 2%, and as PCI-E is backwards and forwards compatible for me its not an issue.
The article is definitely worth a read, especially if you have PCI-E 2.0 and even more so if your looking at a Crossfire setup.
Fan Traps and Chasm Traps
posted by Mark @ 5:19pm, Thursday 4 February 2010.
Introduction
Ok, hopefully anyone working in the realm of MIS and BI should have an understanding of what a Fan Trap and Chasm Trap is, unfortunately I suspect that there are a lot of professionals who don’t... or do they?
There are various sources (specifically on the net) that seem to provide only very brief explanations, often leading to an ambiguous understanding based on a very specific scenario.
In my experience the areas that make the most reference to Relationship Traps are ER Modelling and Business Objects Universe Design.
This means that if you have never designed a Business Objects Universes or had any formal training in ER Modelling you may never have come across the terms, except perhaps in passing.
What’s so important about them?
In ER and Data Modelling, you need to be aware of Relationship traps primarily so that you can avoid them in your design in the first place.
I suspect that most professionals in that sort of role already resolve the Traps at design, without actually recognising them as either Fan or Chasm Traps, especially the Chasm Traps (for reasons that will become obvious in my Chasm Trap article).
In Business Objects Universe design, not being aware of the traps could lead to significant statistical errors in the reports based on them, especially if those responsible for the report creation assume the Universes are correct.
Overall, in any situation where data is extracted from a Relational database to gather meaningful information, not being aware of the Traps can cause significant issues.
I work with Relational Databases and have never heard of them?
I suspect that anyone that hand codes SQL to extract information from a Relational Database is probably already aware of the pitfalls of the traps, and how to work around them... you just don’t know them by that name.
This may sound odd, but Fan Traps and Chasm Traps are just putting a name to a problem. That problem existed before it got given a name, so you may well have experienced and addressed, and continue to address the problem yourself, without actually giving it a name.
So what next and why do I need to know?
As I pointed out above, you may already be aware of the Traps and be resolving them as you come across them, however ideally you would know and understand them for what they are and actively look to resolve them while designing databases and producing meaningful information from that data.
You can read my Article on Fan Traps here: Fan Traps
You can read my Article on Chasm Traps here: coming soon
Below is a list of sites I found 'useful' information on the traps from:
- Presentation by Dave Rathbun (Very Business Objects oriented, but with many useful examples.)
- Wikipedia Article on Relation Modeling
- Article by Maloy Manna (Useful info although lacking in complete examples.)
- ER Modelling Lecture Slides by Dr Gordon Russell (Quiet brief and not as clear as it could be.)
- Hubpages articles by BusinessObjects (Unfortunately a very brief definition.)
