Visualizing SQLSaturday Data–Session Details

Recently I downloaded the session submissions report from the SQLSaturday Admin site, it was time to work on the schedule. But first I must upload it into my SQLSat480 database. I like having a database year over year and I just love to play with data. So I ran some queries to give me information about my submissions. While relaying these details to my husband I declared “I need to put this into PowerBI”.  When he got home from work he was able to see what it was I had created and said “so what did that take you like half an hour”, “Pffft!” I said, “like 10 minutes”. On the first time through I connected directly to my database, in this sample I am going to import the data into excel.

Step 1 Download Data

Download the Submitted Sessions report from the reporting menu on the SQLSaturday admin site. Don’t worry about opening the data to fix anything and trust me I know what kind of silly things SSRS does to the layout of a report in excel. Save the file locally as Excel. (Image 1 below)

Image 01

Image 01

Step 2 Launch PowerBI & Get Data

Launch the PowerBI desktop application and select Get Data.  Select File on the left and choose Excel from the middle options, then click Connect (Image 2 below). Now use the standard window popup to navigate to the file you saved in step 1.

Image 02

Image 02

Step 3 Preview Data

In the Navigator window, select your file on the left, a preview will load in the right hand pane. (For privacy I will not show you the data preview) Now select [EDIT] at the bottom.

Step 4 Editing Data

From here we will walk through several steps to get your data in order to use in a dashboard.

  1. From the HOME menu ribbon select Remove Top Rows drop down chooser and Remove Top rows. You will get a pop up Remove Top Rows asking you how many rows you’d like to remove enter 1 and select [ok]. This will remove the tile row from your data. (Image 3 below)
  2. Now select Use First Row As Headers from the Home menu ribbon. This will promote the first row of the data set to being the header row.
  3. Remove columns you don’t need by selecting the column and using the Remove Columns in the HOME menu ribbon. If you want to remove more than one column select the Choose Columns in the Home menu ribbon. A pop-up will give you a complete list of the columns and you can easily uncheck them there. I will be removing Email, Column12 (artifact of SSRS formatting),Status, Phone, Country (mine are all the same), Address1, Address2, Phone.
  4. I know I want to make a map later, since I am in the US the Region column is actually State. Scroll over to Region and rename the column to State. Select the column and right click, rename.
  5. We are done editing our data, select Close & Apply from the HOME menu ribbon.
  6. After a few seconds your data fields will appear on the right and you’ll be ready to build your dashboard. (Image 4 below)
Image 03

Image 03

Image 04

Image 04

Step 5 Adding an Image

You want to make your dashboard pretty so why not add your SQLSaturday Logo? From the HOME menu ribbon and select Image. Use your pop-up to navigate to the location of your image. Resize as desired.  As a newer feature you can add images and even newer is the ability to overlap items. Remember how annoying that is in SSRS? (Results in Image 6 Below)

Step 6 Funnel of Tracks

Select the funnel from the visualizations (1), select track in the field list (2) and drag track to the values box (3). (Image 5 below) Now we need to customize this visualization.  Select the paint brush to edit. (Image 6 below) I recommend giving each of the tracks a different color. Since Tracks are determined by the organizer the data maybe similar so you might want to use the same colors for more than one data point. You should also update the title Count of Tracks by Track sounds silly. Now we have a lovely display of session distribution by track.

Image 05

Image 05

Image 06

Image 06

 Step 7 Sessions by level

Let’s add a Doughnut chart to display the breakdown of sessions by difficulty level. Select doughnut chart from the visualizations, select level from the fields list and drag fields to the values (just like above). Again you will want to change the title and update the colors. Now you have a visual of sessions by level and it’s interactive. Click on one of the pieces and watch what happens to your funnel graph.

Step 8 Sessions by MVPs

For this select pie chart from visualizations, select MVP and drag MVP to Values. Now you can update the colors and title.

Step 9 Sessions by State

Just for fun I wanted to “see” where all of my submissions were coming from. Select Map from the visualizations, select State from the fields chooser and drag Session Title to Values. You will want to update the colors and title like before. Now you have an interactive dashboard showing you everything you wanted to know about your submitted sessions. You can easily use different visualizations than the ones I picked, I am sure no one really likes a pie chart. The fun thing is that for the most part once you set up the visualization you can easily select it in the dashboard and change it to something else. When I showed this to my non-IT father he was really excited at how the data/displays change when you click on things. Can you imagine if you could make something useful for everyone?  (Image 7 below)

Image 07

Image 07

 Step 10 Final Product

After finishing my dashboard I didn’t like it so I made a few more adjustments. (Image 8 below) I displayed this at our hands-on PowerBI meeting for the NashBI ( TWITTER  |  WEB ) user group in December. It was brought to my attention that this was “ugly”. So here is my challenge to you, if you think it is ugly go play with PowerBI desktop and make something prettier, if you think it is lovely I think you should create one too. I find PowerBI so much fun and easy to work with that I could easily sit for hours and make stuff up.

Image 08

Image 08

 

#TSQL2SDAY – Data Modeling Gone Wrong

Here we are again, time for TSQL2sDay. This is actually the 72nd one, that is long enough to finance and pay off a car these days. This month our gracious host is none other than Mickey Stuewe ( BLOG | TWITTER ) and she’s asked us to tell a story about Data Modeling Gone Wrong. I have to say that I really no nothing about a data model going wrong. Now hold on I am not done yet. You are wondering WTH?! is this girl still sick? Has she lost her marbles? I’ve never had these issues because:

Reasons Applications Egos

 

Reasons

First of all, you have to . . . → Read More: #TSQL2SDAY – Data Modeling Gone Wrong

Power BI–Visualizing SQLSaturday Data

Inside SQLSaturday

This morning I was having a discussion with my husband about our SQLSaturday location. He is extremely concerned that moving the event further away from downtown Nashville would hurt attendance. I tried discussing it with him but he finally said “I’d like to see the data so we could know where everyone is coming from.” I said well I have all the data give me a minute. 10 minutes later I am Skyping him asking how to share with him the 2 Power BI maps I have made.

How did we get here? I ran the last 2 Nashville SQLSaturday events. Because I took a big leap in . . . → Read More: Power BI–Visualizing SQLSaturday Data

Starting over with Power BI

I had decided it was time to write a new presentation and happily chose Power BI, being that it’s the new hotness and looks to be the future of Business Intelligence. My timing couldn’t have been better with the general release going live recently it seems like now is the time to get going. I have titled this starting over because I had been using Power BI to create maps and other fun things inside Excel; it seems fitting since the changes to Power BI are coming in fast and furious. So I am here to tell you what it took to get back on track with this new release.

. . . → Read More: Starting over with Power BI

#SQLSatIndy, Shaving Yak and Power BI

You are probably wondering how these things are all related. I love summer, when there isn’t a signed SOW I can find a million things to do outdoors. I love being out in the sun. This leaves me very unmotivated to be indoors doing things, like writing new presentations. Hence the problem.

The Indianapolis SQLSaturday is near and dear to our hearts. First we love Hope Foley [ BLOG | TWITTER ], she’s one kick ass woman and she’ really an inspiration. Next Indy is only an hour away from our family and they were the first user group I ever attended. So it’s no surprise that we both submitted . . . → Read More: #SQLSatIndy, Shaving Yak and Power BI

5 Simple things I forget about SSRS – Multi Select Parameters

For some reason whenever I get to working on a report I am really irritated at how I “forget” that SSRS doesn’t handle multi-select parameters at all. I am irritated at myself for pushing this horrid fact from my brain, my reluctance to remember or maybe that the painful truth is a coping mechanism or I am just in denial–maybe it’s been fixed.. Then I am reminded how Microsoft failed us by not making this an actual feature. I feel the writing has been on the wall for a while now SSRS is a fading technology. I do however know that there will be SSRS servers running for years to . . . → Read More: 5 Simple things I forget about SSRS – Multi Select Parameters

Inside SQLSaturday – Romancing your Sponsors

Romancing your Sponsors

To be honest, this seems like a no brainer, but I have noticed that in some locations the sponsors are just “there” so to speak. So these are my tips and a lot of them I have gathered from the sponsors over the last year or so.

Make sure your sponsor area gets good foot traffic–this is how they meet your attendees. Don’t build rooms as tracks that would keep folks in 1 room or one end of a building the entire day. Have lunch set up early and allow the sponsors and lunch speakers to go through so they will be ready for the lunch break. . . . → Read More: Inside SQLSaturday – Romancing your Sponsors

5 Simple things I always forget about SSRS – Custom Color Palette

There are spells where I am inside SSRS daily and then there are some stretches of time where I wrapped up doing other things and don’t see SSRS for months on end. I always seem “surprised” when I forget some of SSRS’s behaviors. I am sure sometimes I am just pushing the bad out of my head and other times it might be that I just don’t use it often enough to keep that info close to the top of my stack. So I decided that I should start a small blog series as a gentle reminder to myself and maybe along the way it will help someone else.

Custom . . . → Read More: 5 Simple things I always forget about SSRS – Custom Color Palette

Nashville’s BI User Group – 1st Meeting

WOW!

So that is about the only word I can use to sum up how I feel about last night’s meeting.

We had about 45 of #BIHappyHour in the hotel lobby, it was a little cramped but we were not moving into the open spaces very well. In the future will will do a better job of herding folks into more open space. It warmed my heart to see so many people chatting and introducing themselves. Our food was delivered, special thanks to my Dad and my husband for going to pick it up! I thought the food was awesome and I didn’t get to eat any of it until . . . → Read More: Nashville’s BI User Group – 1st Meeting

Inside SQLSaturday – PayPal

PayPal

This year PASS changed the way money is handled for a SQLSaturday. In the past, they had a PayPal account and all the money was collected there. You would then request disbursement from them, with a limit of like 3 or something and 30 days after the event they’d allow you to pull out any remaining amount. Now, you must provide your own PayPal Account. But what does that mean to you? These details are things that happened to me and may not be a concern for you, if you’ve mastered PayPal.

Verify your account This was a newly-created PayPal account and we were unaware that you must verify . . . → Read More: Inside SQLSaturday – PayPal