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 ‘14 by moving the date I was constantly looking at registration data. Once the event was over I have the task of supplying the sponsors with their scanned list, since I had to download and save them, I started thinking “What if I put this in a database so then I could play with the data?” You see I am that kind of data person that can’t sleep when I get my hands on new data, I am constantly thinking about what I can do with it. “How can I make it useful and what does it want to tell me?” So I have the last 2 years of data in databases that I sometimes just tinker with, you know until today when it might just prove to be useful.

So here we go, step by step in making some of this data useful and pretty for you.

1. Download the data

If you haven’t already downloaded the data you’ll need to login to the SQLSaturday admin site and hit the reports menu. (below)



For this visualization you’ll want to grab the Checked In Attendees and Event Registrations. I personally didn’t see that great of a difference in the two but it does lead you to wonder other things like “does location from venue show a trend in lower attendance?”

2. Import your data into a database

Now you could connect Power BI directly to the Excel sheets but I prefer to have the ability to look around and use the data for other things. Plus this data will more than likely need a little clean up, I find this easier to do in a database.

3. Review your data

Your data needs to be clean. And unfortunately, even though this is an event for IT professionals, you will find some wise guy types something in the Postal Code (ZIP) that you don’t want. Being in the US we know ZIPs are 5 digits. Based on the data I was able to download (prior to the SQLSaturday site upgrade) the Checked IN report doesn’t have a REGID or Postal Code but Registrations does. So you do need both lists. You’ll want to clean up any oddities.

REGID    PostalCode

131094     23692

130722    Nashville

129681    37211-5119

130339    42104-7632

127937     37027

130219    37027-2852

127872    Indiana

129318    V1Y8G7

131133    GA. 30039

Here I just look for postal codes with a length greater than 5. As you can see I need to fix a few. I also found a slew of items with –1 in the Region and several folks in Texas have a TE region.  How you limit the data to display is up to you. I have decided that I will clean up the data and only show the registrations in TN; this allows the map to be zoomed at the state level and is truly showing what is important for this exercise.

4. Write your query

There are always alternate ways to accomplish tasks, but for this quick exercise I am going to just connect to my local database and run a couple of queries. Here I have a query for Registrations and one for Attendees. Since the Checked In report is missing REGID and PostalCode I join to the registrations. I could easily write an update and insert this data if I thought I would be using it for other things.

SELECT COUNT(REGID) AS 'Number of Registrations', PostalCode

  FROM [SQLSat363].[dbo].[EventRegistrations]

  WHERE Region = 'TE' –WHY TE is the region I will never know, TN is the official state abbreviation.

  GROUP BY PostalCode

SELECT COUNT(*) AS 'Number of Attendees', E.PostalCode

    FROM  [SQLSat363].[dbo].[CheckedIN] AS C

          JOIN [SQLSat363].[dbo].[EventRegistrations] AS E on C.email = E.email

    WHERE E.Region = 'TE'

    GROUP BY E.PostalCode


5. Power BI

Launch the Power BI Designer (desktop application).  If this is your first time using Power BI Designer or you’ve left the “Show this page on startup” checked. You’ll see a screen similar to this (below).



6. Get your data

Select Get Data & SQL Server, your pop up will change to (below)


Fill in your Server, if you expand SQL Statement, Database becomes required. Paste your query into the box and select ok. After a few seconds you’ll now see a preview of the data you are requesting (below). Since we don’t need to edit the query we can select load and continue.


You need to repeat this process to retrieve the other dataset. You can select “Get Data” from the HOME tab in the ribbon (below). I only did both queries to visually see if there was a difference between attendee and registration, but realistically you get what you need from either query.



7. Create the visualization

By selecting the Map under visualizations a blank map will appear on the report canvas and the data fields will become available. (below) You can drag and drop the your query fields from the FIELDS list into the appropriate area.


Drag your measure “Number of Attendees” to Values and “PostalCode” to Location.


To format your visualizations select the paint brush (below). You have options to turn on a legend (I don’t recommend), you can edit the title and even change the color under Data Colors. All categories expand when you click on the carat. I updated the colors to represent the SQLSaturday logo colors.


Repeat to add another map.

8. Add an extra

For fun you can add a line chart visualization to the report canvas. Drag and drop “PostalCode” to the Axis and your measures “Number of Attendees” & “Number of Registrations” to Values.



I hope you found this as fun and as useful as we did.

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


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


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

Nashville’s newest Pass Chapter


If you have known me for very long you know that Kerry and I have been talking about starting a Business Intelligence user group here in Nashville for about a year. At first we were just going to do some additional meetings under the SQL user group’s umbrella. We thought that working as a lager group could be better for both groups. However we finally discovered that BI was huge and there was a lot of interest in it. Just looking at MeetUp there are 3-4 data related groups.

We are pleased to announce the launch of a new PASS chapter here in Nashville. The Nashville Business Intelligence . . . → Read More: Nashville’s newest Pass Chapter

WIT Summit 2014

Recapping the Women in Technology Luncheon


As a member of the PASS Women in Technology virtual Chapter I had been on several planning meetings throughout the past year about the luncheon. I had known for some time that they were hoping to secure Kimberly Bryant [ TWITTER | Linkedin ] as our guest speaker.

If you are unaware Kimberly started the organization Black Girls Code [ TWITTER ]. She “hopes to provide young and pre-teen girls of color opportunities to learn in-demand skills in technology and computer programming at a time when they are naturally thinking about what they want to be when they grow up.”

To be perfectly . . . → Read More: WIT Summit 2014