Japan 2011 Earthquake Analysis with Excel 2010 and PowerPivot

We have been pretty much glued to the TV and Internet reading the news on the Japan catastrophe.   The Earthquakes, the Tsunamis, the Deaths and the Nuclear Plants.   It is all so very sad and when we watch things like this on the news, it is hard to understand the true numbers when they are being presented verbally and randomly. 

I decided to take the Earthquake data, as I wrote a blog the other day using Google Earth, to look at where the quakes where occurring.   That was interesting, but it didn’t tell me a whole lot.  It explained it visually, but I need to see the numbers to understand the magnitude of the event.

image

We got together as a team and designed a simple solution that could give us the answers we wanted.  We used an ATOM feed from the USGS site and imported it into Microsoft Access.  I used the import feature in Access to add the ATOM feed and imported the data into a temporary database.  I had to do this so I could then run a query that would allow me to import it into a main table I could use for the reporting so I could remove duplicates.

Microsoft Access

Then we used Microsoft Excel and Microsoft PowerPivot to create a mashed up collection of data that allowed me to pivot or filter through the information.   I wanted to use PowerPivot to access the Access database so I could quickly refresh my database and then refresh the PowerPivot model so I had new data.   I used PowerPivot because I wanted slicing functionality like an OLAP cube so I could basically ask questions.

image

Once we got the PowerPivot model completed, then we could build out some reports inside Microsoft Excel.  When we got it in Excel, we developed some very interesting views of the data unlike what has been presented on the TV and Internet.   Then the impact of the earthquake hit us straight in the head.  The number of earthquakes are unbelievable and basically the whole country is in a state of shaking…

image

We are in the process of bringing in other data sources like Financials, Social Media and Radiation information to our PowerPivot model this week.  Our social media pull grabbed over 3+ million records yesterday.  Every day, we will update the PowerPivot model and upload it to this web site for your use.  I will be refreshing the solution once or twice a day.   Please email us with other insight you may find.

Check out http://blog.extendedresults.com/2011/03/15/japan-earthquake-analysisinsight-into-the-numbers-that-make-up-the-catastrophe/ for the analysis

Thanks

Patrick

Special thanks to my team:  Chris, Dan and Eric

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s