Contraflo

Fan Traps

Wed, 15 Sep 2010 17:29:01 +0100

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

Sun, 07 Mar 2010 20:41:32 +0000

"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

Thu, 04 Feb 2010 17:19:36 +0000

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

Baby Smash! - baby proof computer fun

Sun, 01 Nov 2009 20:53:45 +0000

Babysmash! is a program by Scott Hanselman that allows you to have fun with your little one on the computer, the main feature being they cant break anything.

The idea is really simple. Babysmash! locks out all the usual keys and shortcuts on your computer, only allowing Ctrl-Alt-O to bring up the options or Alt-F4 to quit the application.

Any other keypress displays a smiley letter or shape on the screen, with either some laughter or talking describing the letters and shapes, although the talking is very computerised.

Babysmash! is totally free and simple to install, the only catch is that it's Windows only. I'd also recommend that you let them loose on a spare, preferably old keyboard, as once they get started, the emphasis is on the word Smash!

You can find Babysmash on Scott's site: http://www.hanselman.com/babysmash/

SQL - A bit about Nested Joins

Sun, 11 Oct 2009 16:44:10 +0100

I have maintained/re-written legacy code in every stage of my career to date and I cant see that stopping in the future.

Quite a few times I have found stored procedures and views, with badly formatted nested joins and absolutly no comments.
If you have never come across nested joins before, keep reading and I will try to cover a basic example below and how to de-mystify it.

Lets look at nicely formatted and VERY basic example of a nested join (real examples can be alot more complicated):

SELECT
   *
FROM
   tb1 
   LEFT JOIN tb2
        ON tb1.c1 = tb2.c1 
   RIGHT JOIN tb3 
        LEFT JOIN tb4 
                ON tb3.c1 = tb4.c1 
        ON tb1.c1 = tb3.c1

The first thing I do when I see a nested join is add parenthesis, which makes the above look like this:

SELECT
   *
FROM
   tb1 
   LEFT JOIN tb2
        ON tb1.c1 = tb2.c1 
   RIGHT JOIN (tb3 
        LEFT JOIN tb4 
                ON tb3.c1 = tb4.c1) AS tb3 
        ON tb1.c1 = tb3.c1

From my point of view, the whole thing makes a lot more sense now, although to be honest the nested join just looks like a derrived table now with a missing 'select *'... which would look like:

SELECT
   *
FROM
   tb1 
   LEFT JOIN tb2
        ON tb1.c1 = tb2.c1 
   RIGHT JOIN (
        SELECT *
        FROM tb3
        LEFT JOIN tb4 
                ON tb3.c1 = tb4.c1) AS tb3 
        ON tb1.c1 = tb3.c1

It might just be me, but the above looks infinitely more readable than the first example, even with the extra code?
Having said that, without a WHERE clause the derived table isnt really doing much. Lets get rid of the nested join/derived table and the extra code and see what it looks like:

SELECT
   *
FROM
   tb1 
   LEFT JOIN tb2
        ON tb1.c1 = tb2.c1 
   RIGHT JOIN tb3
        ON tb1.c1 = tb3.c1
   LEFT JOIN tb4 
        ON tb3.c1 = tb4.c1

Much better! Now the final step is to remove that silly RIGHT JOIN (Silly? read my take RIGHT JOINS):

SELECT
   *
FROM
   tb3 
   LEFT JOIN tb4
        ON tb3.c1 = tb4.c1 
   LEFT JOIN tb1
        ON tb3.c1 = tb1.c1
   LEFT JOIN tb2 
        ON tb1.c1 = tb2.c1

Perfect! (even if I do say so myself) The final result is a nicely formatted sql select, that most sql programmers should be able to quickly read and understand.

As this example shows, theres really no such thing as a nested join, either your using a derived table (which is pretty pointless without a where clause) or you are just writing your SQL in a different way.

I hope this helps a little, however as always get in contact if you have any questions/comments.

UPDATE: I have added a more detailed example sql script on the Downloads page.

Spotify - A world of music. Instant, simple and free.

Thu, 27 Aug 2009 20:29:53 +0100

In my constant search to get more from the Internet for free (and without breaking any copyright laws etc.) I have come across a little gem.

Spotify is a music streaming application for your computer, but what makes it very clever is that the streaming is virtually instant.

Using the application is like using iTunes except you have an absolutely massive (3.8 million tracks according to wikipedia) Library of songs at your disposal.
Searching for the songs you want is quick and straight forward, you simply type in any part of the artists name or track title and a list of various matches appear and can be played instantly.

The playlist feature is fantastic and really adds to the applications useability. Just like iTunes you can add any of the tracks into whatever playlists you want, mixing artists as you see fit.
Whats more is that these playlists can be shared using sites such as http://www.spotyshare.com/ and you can even invite other users to add/remove songs from your playlists by marking them as collaborative.

Spotify is supported by ads (theres always a catch) in its free version, but they dont pop up to often and only last about 30 seconds, so its a little like radio (but no DJ's).
There is a Premium subscription at £9.99 per month which removes all ads and also provides access to certain content before its made available to free users.

Another great option is the Day Pass. For only 99p you can have 24hrs of ad free music. This option is perfect if you are throwing a party and want to provide the Music through your very own playlist, it also means that which ever direction the party takes, you can find the music to match the mood.

I am really impressed by the service offered by Spotify and the features it provides out of the Box and although it might not be for everyone I whole heartedly recommend you give it a look.

Steam Offer - Trackmania United Forever

Tue, 04 Aug 2009 21:23:38 +0100

Every now and again there is an offer on Steam that gets my attention.

This time round its Trackmania United Forever at 50% OFF until 10 Aug 2009. That means it drops from £19.99 to £9.99.

This is a great price for a great game, but I am not sure it will give me the extra playability over that of the FREE version (Trackmania Nations Forever) to warrant spending the cash.

Eitherway if your not sure about Untited, you should definitley get yourself a FREE copy of Nations through Steam.

For more information on what Steam is click here.

eggBlog - Guest Comments

Wed, 22 Jul 2009 13:23:08 +0100

The Blog software I have chosen to use for the articles section is called eggBlog and its pretty good especially as its on an open source licence which means I can make any changes to it that I require.

I have already made various theme changes to integrate it with this site, but until now I have not done much to change its operation.

One of the limitations it has is that comments can only be posted by registered users. I have managed to modify the code and integrate reCaptcha (my chosen Captcha solution) with the comment posting code so now if you are not logged in you can still comment on posts as a Guest you just have to complete the Captcha.

For any registered users, if you are logged in it will work as normal and you will NOT have to complete the Captcha.

If anyone would like the source code just let me know, you never know I may even get around to posting it in the Downloads section.

For more information about Captcha's there is a great Wikipedia page all about them here.

Windows 7 E UK Pre-Order 50% OFF

Tue, 21 Jul 2009 12:28:33 +0100

I know this will come a little late for some, but as the offer doesnt expire until August 9th 2009 I thought I'd mention it as it is a pretty fantastic offer.

In short Microsoft have decided to 'be nice' and offer the Home Premium and Professional editions of Windows 7 at upgrade prices, if you pre order it between July 15th 2009 and Aug 9th 2009.

Pre-Order Prices:

Windows 7 E Professional - £89.97
Windows 7 E Home Premium - £44.97

The reason for this offer is that European customers can only have the 'E' version which does not come bundled with a browser (no Internet Explorer) in an effort to comply with European Competition rules. This basically means there is no upgrade option for EU customers.

You can still download IE free of charge of course and the recommendation is to do it before you do a clean install fo Win7. You could of course take this opportunity to try one of the many free browsers like Firefox.

Dont miss out on this great offer of a cheap Full Retail version of Windows 7.

More info here: http://www.microsoft.com/uk/windows/buy/offers/pre-order.aspx

UPDATE:
The Offer is no longer available and Microsoft have now changed there stance on the Windows 7 'E' versions. As per the link above Europe will now get the same version as other countries, including Internet Explorer.
This also allows for Upgrade versions to be sold.

For those of us lucky enough to get in on it, our pre-orders still stand and we will now recieve full versions of the non 'E' version just like everyone else.

Excel - In the begining...

Fri, 05 Jun 2009 12:52:12 +0100

This is the first of many (well at least more than one) articles I'll be writing on Microsoft Excel.
I use Excel as my example because it is the most well known and well used spreadsheet applications, however most of the articles should be relevant for other spreadsheet applications such as Open Office Calc too.

When to use Excel?
Firstly I wanted to cover when and why you would use Excel. The simple answer is when you would use a calculator for lots of different calculations, such as your personal accounts or small sales invoices.
In reality Excel is capable of alot more than basic additions, subtractions, multiplications and divisions. You can use it to make straight forward decisions based on criteria, for example if you run a club and wanted to give a prize to those with at least 80% attendance, you could use Excel to look at your register and indicate which members get a prise.
Many Businesses use Excel for reporting and analysis. This is handy because Excel will recalculate formulas as you type allowing you to see what will happen if you change a parameter, a recent example of this is the change in UK VAT, quickly changing this figure in excel would allow it to recalculate the VAT for your products.

When to NOT use Excel?
I have shown some ideas of when you might want to use Excel, but when would you be best not using Excel?
For when you need to perform a basic calculation dont use Excel, you wouldnt want to open a new spreadsheet everytime you wanted to see what the changes is from an Ice Cream or at the end of a meal.
Heavy statistical analysis is arguably not best suited for Excel. Its not that Excel should'nt be used for this, just that there are other tools that are designed specifically for this.
The same go's for accounting. Excel is very capable of producing accounting figures however there are accounting packages much better suited for the task overall, they only addition here being that Excel can be great for the bits that your accounting package may not cover.

Real World Example
Thats enough of an Introduction to Excel, except to wet your appitite with a nice real world example.
This example has formulae and information to calcualte deposits on houses and what your deposit will buy you.
House Deposit

More Examples and Contact
All my Excel tools and examples can be found in the Downloads area and are completely free, but please read the terms before use.
If you have any feedback or questions feel free to signup and leave a comment on this post, or as usual use the Contact area.