Subscribe to Blog feed
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:
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.
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.
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.
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.
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
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!
Dan English's BI Blog 
Karla Landrum said
This is fantastic! I’d be interested in viewing this by PASS’ Fiscal Year (July 1 to June 30). I assume easily doable just by changing/adding some specific dates in?
denglishbi said
I went ahead and updated the post and decided to pull in a full date table setting up the fiscal calendar and updated the Power View worksheet. Enjoy:)
Jamiet said
Hi Dan,
the download link didn’t work. Can you fix?
cheers
Jamie
denglishbi said
I updated the links, so now it opens up in Office Excel Web App. From there you can download I guess. Not seeing a quick way to provide a direct download link, I was trying, but it must just be temporary.
Jamiet said
Something weird going on. I downloaded the .xlsx, open in Excel2013, and in the area where the Power View report was there is simply an image that is an illustration of a Power View report (and I know its an image cos when you select it “Picture Tools” appears at the top).
I’m getting more and more exasperated with the whole Power View in Excel story. It should be brain dead simple to view this stuff, and it isn’t!
denglishbi said
I downloaded the file as well and tested it out. I think you downloaded it correctly because you are seeing at least the image. If you try and download the file while viewing in browser you will get the following message “Features that can’t show in the browser and interactive reports will be removed from the downloaded copy.” So if you do the download that way it will drop the Power View report. If you download it directly from my Public|Blog folder (http://sdrv.ms/MSA1me) then you will get the complete file. Now once you open it in Excel 2013 you will possibly just see an image. Go into your add-ins and disable the Power View add-in and click OK. Then go back into the add-ins and enable the Power View one again. Once you do this and then click on the SQLSat Dash sheet it will say “Working on opening Power View sheet…”. It should then load the model and report and as long as you have an Internet connection the map as well. It is a Preview version so it won’t be 100% stable:)
Jamiet said
Nope, that didn’t work for me. I downloaded from the folder, disabled/enabled Power View add-in…I’m still seeing the image.
Really not impressed at all, even if IS a preview.
denglishbi said
You make me laugh Jamie:) After you disable it and click ok you have to go back into the add-ins to enable it again. Then once you click on the Power View sheet it should open it up without any issues. Maybe we are running into a localization issue, I programmed it to only work in U.S.:D (j/k). Not sure why it is not working for you. Sorry man.
Jamiet said
Yep, did all that. I’m giving up – I’ll wait for RTM 🙂
Scott Stauffer said
Ya beat me to it getting this out. It’s great having the mapping layer and Power View in Excel 2013. Incidentally, your book should be in my hands today or tomorrow. Looking forward to learning some new stuff on Power View.
Scott Stauffer said
Incidentally, I downloaded the file, and the Canadian Cities weren’t “jumping out” when I filtered on Canada. I therefore clicked on the map to select it and on the left in the “Power View Fields” I chose dragged the fields with the globes beside them into the “LOCATIONS” region. And then I had to accept the Warning regarding the information would be send to Bing Maps on the InterWebz. Now I see more locations when I filter down… 🙂
denglishbi said
Great to hear. Definitely leave feedback on the book and let us know what you think. Leave a review on Amazon as well:) Thanks and hope you enjoy the book and Power View!
Jen Underwood said
I love this demo – wonderful job!
Power View in Excel 2013 Demo Files | Ms SQL Girl said
[…] the data from 2 different sources, Dan English’s SQL Saturday workbook and Karla Lundrum’s report from PASS HQ, this demo shows how to combine them […]
Power View for Geographical Analysis with Excel 213 | Ms SQL Girl said
[…] the data from 2 different sources, Dan English’s SQL Saturday workbook and Karla Lundrum’s report from PASS HQ, this demo shows how to combine them […]