{"id":1148,"date":"2015-08-26T09:45:17","date_gmt":"2015-08-26T15:45:17","guid":{"rendered":"https:\/\/clarkcreations.net\/blog\/?p=1148"},"modified":"2015-08-26T09:45:17","modified_gmt":"2015-08-26T15:45:17","slug":"power-bi-visualizing-sqlsaturday-data","status":"publish","type":"post","link":"https:\/\/clarkcreations.net\/blog\/power-bi-visualizing-sqlsaturday-data\/","title":{"rendered":"Power BI\u2013Visualizing SQLSaturday Data"},"content":{"rendered":"

Inside SQLSaturday<\/h4>\n

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 \u201cI\u2019d like to see the data so we could know where everyone is coming from.\u201d 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.<\/p>\n

How did we get here? I ran the last 2 Nashville SQLSaturday events. Because I took a big leap in \u201814 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 \u201cWhat if I put this in a database so then I could play with the data?\u201d You see I am that kind of data person that can\u2019t 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.<\/p>\n

So here we go, step by step in making some of this data useful and pretty for you.<\/p>\n

1. Download the data<\/h4>\n

If you haven\u2019t already downloaded the data you\u2019ll need to login to the SQLSaturday admin site and hit the reports menu. (below)<\/p>\n

\"one\"<\/a><\/p>\n

 <\/p>\n

For this visualization you\u2019ll want to grab the Checked In Attendees and Event Registrations. I personally didn\u2019t see that great of a difference in the two but it does lead you to wonder other things like \u201cdoes location from venue show a trend in lower attendance?\u201d<\/p>\n

2. Import your data into a database<\/h4>\n

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.<\/p>\n

3. Review your data<\/h4>\n

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\u2019t 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\u2019t have a REGID or Postal Code but Registrations does. So you do need both lists. You\u2019ll want to clean up any oddities.<\/p>\n

REGID\u00a0\u00a0\u00a0 PostalCode<\/p>\n

131094\u00a0\u00a0\u00a0\u00a0 23692<\/p>\n

130722\u00a0\u00a0\u00a0 Nashville<\/p>\n

129681\u00a0\u00a0\u00a0 37211-5119<\/p>\n

130339\u00a0\u00a0\u00a0 42104-7632<\/p>\n

127937\u00a0\u00a0\u00a0\u00a0 37027<\/p>\n

130219\u00a0\u00a0\u00a0 37027-2852<\/p>\n

127872\u00a0\u00a0\u00a0 Indiana<\/p>\n

129318\u00a0\u00a0\u00a0 V1Y8G7<\/p>\n

131133\u00a0\u00a0\u00a0 GA. 30039<\/p>\n

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 \u20131 in the Region and several folks in Texas have a TE region.\u00a0 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.<\/p>\n

4. Write your query<\/h4>\n

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.<\/p>\n

SELECT COUNT(REGID) AS 'Number of Registrations', PostalCode\r\n\r\n  FROM [SQLSat363].[dbo].[EventRegistrations]\r\n\r\n  WHERE Region = 'TE' \u2013WHY TE is the region I will never know, TN is the official state abbreviation.\r\n\r\n  GROUP BY PostalCode\r\n\r\n\r\nSELECT COUNT(*) AS 'Number of Attendees', E.PostalCode\r\n\r\n    FROM  [SQLSat363].[dbo].[CheckedIN] AS C\r\n\r\n          JOIN [SQLSat363].[dbo].[EventRegistrations] AS E on C.email = E.email\r\n\r\n    WHERE E.Region = 'TE'\r\n\r\n    GROUP BY E.PostalCode<\/pre>\n
 <\/p>\n
5. Power BI<\/h4>\n
Launch the Power BI Designer (desktop application).\u00a0 If this is your first time using Power BI Designer or you\u2019ve left the \u201cShow this page on startup\u201d checked. You\u2019ll see a screen similar to this (below).<\/p>\n
\"two\"<\/a><\/p>\n
 <\/p>\n
6. Get your data<\/h4>\n
Select Get Data & SQL Server, your pop up will change to (below)<\/p>\n
\"four\"<\/a><\/p>\n
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\u2019ll now see a preview of the data you are requesting (below). Since we don\u2019t need to edit the query we can select load and continue.<\/p>\n
\"five\"<\/a><\/p>\n
You need to repeat this process to retrieve the other dataset. You can select \u201cGet Data\u201d 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.<\/p>\n
\"nine\"<\/a><\/p>\n
 <\/p>\n
7. Create the visualization<\/h4>\n
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.<\/p>\n
\"ten\"<\/a><\/p>\n
Drag your measure \u201cNumber of Attendees\u201d to Values and \u201cPostalCode\u201d to Location.<\/p>\n
\"eight\"<\/a><\/p>\n
To format your visualizations select the paint brush (below). You have options to turn on a legend (I don\u2019t 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.<\/p>\n
\"eleven\"<\/a><\/p>\n
Repeat to add another map.<\/p>\n
8. Add an extra<\/h4>\n
For fun you can add a line chart visualization to the report canvas. Drag and drop \u201cPostalCode\u201d to the Axis and your measures \u201cNumber of Attendees\u201d & \u201cNumber of Registrations\u201d to Values.<\/p>\n
\"twelve\"<\/a><\/p>\n
9. ENJOY<\/h4>\n
I hope you found this as fun and as useful as we did.<\/p>\n","protected":false},"excerpt":{"rendered":"
Inside SQLSaturday <\/p>\n
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 \u201cI\u2019d like to see the data so we could know where everyone is coming from.\u201d 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.<\/p>\n
How did we get here? I ran the last 2 Nashville SQLSaturday events. Because I took a big leap in  . . . → Read More: Power BI\u2013Visualizing SQLSaturday Data<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[221,286,269,288,270,273,147],"tags":[231,309,291,293,305,292,290,304,245],"class_list":["post-1148","post","type-post","status-publish","format-standard","hentry","category-sqlsat","category-business-intelligence","category-how-to","category-inside-sqlsaturday","category-instructions","category-power-bi","category-sql","tag-sqlcommunity","tag-sqlsat","tag-bi","tag-ms-power-bi","tag-pass","tag-power-bi","tag-powerbi-bi","tag-sql","tag-sqlsaturday","odd"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/posts\/1148","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/comments?post=1148"}],"version-history":[{"count":0,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/posts\/1148\/revisions"}],"wp:attachment":[{"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/media?parent=1148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/categories?post=1148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/tags?post=1148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}