Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Weather in Business Intelligence

We are obsessed by the weather in this country, but I can’t say I have ever actually used it in a business intelligence solution.  I know that Iceland (the frozen food people not the country) have a model that identifies an ideal BBQ day i.e. not too hot or cold, no rain, low winds and so on, preceded by several days of similar conditions.  Other types of retailers also quote weather to account for abnormal sales of clothes etc.

However unless you work for the Met Office you are unlikely to have the raw data you need already in your databases.  I am sure the Met Office would be delighted to share their data with you but like the Ordnance survey this can be pretty expensive, considering we have already paid for this through taxes.  So here’s how to get the data for you to try for free.

Fortunately the US Government have taken a liberal interpretation of “the land of the free”  and so you can look up the current weather for pretty much any airport in the world in hourly intervals back for 24 hours. There is no web service for this but there is an ftp site from the American National weather service , so you could easily write an SSIS task to pull in the data you need, airport by airport. 

You will then need to map the geography you have (Say your stores) to their nearest airport in the list.  I might suggest that this would be a good place to get into the new geo-spatial stuff in SQL Server 2008.  If you have the latitude/longitude of your locations you can use min([mylocation].STDistance([airportlocation]) for each location to get the nearest airport.  Of course you’ll need airport locations and you can get the entire worlds airport locations (for free!) here

Of course there is going to be a bit of work to get this going, and it might initially be hard to justify that until you have done some analysis. The simplest and cheapest approach to doing the actual analysis is going to be the data mining add-ins for excel that you can get here for SQL Server 2005 and here for SQL Server 2008.  If you’re not sure about data mining generally please check these blogcasts on TechNet spotlight.

So now you can see how anything from school attendance, sales of sun cream , doctor’s appointments etc. might be affected by the weather.