Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for August 6th, 2012

SQL Saturday Event Analysis using Power View and Office 2013

Posted by denglishbi on August 6, 2012

Updated (8/7/2012): Added screenshot at end of post of which includes data broken out by fiscal calendar (starts in July) and link to that file as well.

Updated (8/9/2012): Added some information at the bottom of the post in regards to downloading issues and more information available in the comments section.

Last week I saw a post by Sam Vanga called SQL Saturday US Map.  Needless to say I found it interesting and a great idea.  That made me think a bit and I was like hmm… why not take that same data and use it with the new Excel 2013 with PowerPivot, Power View, and maybe even a little Office 365!  I had to leverage the Microsoft stack of course:)

So I went out to the SQL Saturday site and copied down all of the event details. Needless to say a little scrubbing was required because the data was a bit incomplete to get at some of the location information.  After a little while I felt fairly good with the data and loaded the information into PowerPivot for Excel 2013.  I added a few more columns around the date information to generate a calendar and to do some sorting.  Once that was done I went ahead and started to put together a little dashboard page.  I ended up with the following:

image

We can see that the SQL Saturday events over the past couple of years have really grown in popularity and after 2010 they went global as well.

image

We can zoom in on the map and get a close up as well.  Last year we had the SQL Saturday #99 in Eden Prairie, MN.

image

I even added the ability to drill down from the cities to the zip code level and also some drill down on the column chart for the counts as well.

image

image

Some pretty cool stuff.

We can even upload this into the Office 365 preview and share this information as well which support PowerPivot and Power View.

image

You might notice that the colors for the themes don’t match up quite yet, but other than that it works out well and you can interact with the Power View visualizations and reports!  In the above screenshot in Office 365 I have the tile where the map resides filtered to 2012.  You can see that the map really opens up now for the entire globe compared to the 2010 and 2011 views.  I can only assume that next year it will grow even more:)

The data that I grabbed for this was from the SQL Saturday events page here (just copy paste and a bit of cleanup).

If you would like to download the Excel 2013 file I created you can grab it from here – SQLData.xlsx

Fiscal Year Layout

I received a request from Karla Landrum the PASS Community Evangelist to see the data setup using the PASS fiscal calendar which starts in July versus the regular calendar.  Well I decided to add in a full date table and refresh the output.  Here is the result and the new file can be downloaded here – SQLDataFiscal.xlsx

image

If you are experiencing any issues with the downloads take a look at the comments section of this post.  I did some testing and found some issues if you try to directly download the file from the browser and a workaround if the SQLSat Dash does not load for you.  You can download the file(s) from my Public|Blog SkyDrive folder.

Enjoy!

Posted in PowerPivot, Reporting Services | Tagged: , , , | 15 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 93 other followers