{"id":1164,"date":"2015-12-11T13:23:01","date_gmt":"2015-12-11T19:23:01","guid":{"rendered":"https:\/\/clarkcreations.net\/blog\/?p=1164"},"modified":"2015-12-11T13:23:01","modified_gmt":"2015-12-11T19:23:01","slug":"visualizing-sqlsaturday-data-session-details","status":"publish","type":"post","link":"https:\/\/clarkcreations.net\/blog\/visualizing-sqlsaturday-data-session-details\/","title":{"rendered":"Visualizing SQLSaturday Data\u2013Session Details"},"content":{"rendered":"

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 \u201cI need to put this into PowerBI\u201d.\u00a0 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\u00a0time through\u00a0I connected directly to my database, in this sample I am going to import the data\u00a0into excel.<\/p>\n

Step 1 Download Data<\/h3>\n

Download the Submitted Sessions report from the reporting menu on the SQLSaturday admin site. Don\u2019t 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\u00a0as Excel. (Image 1 below)<\/p>\n

\"Image<\/a>

Image 01<\/p><\/div>\n

Step 2 Launch PowerBI & Get Data<\/h3>\n

Launch the PowerBI desktop <\/a>application and select Get Data.\u00a0 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.<\/p>\n

\"Image<\/a>

Image 02<\/p><\/div>\n

Step 3 Preview Data<\/h3>\n

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

Step 4 Editing Data<\/h3>\n

From here we will walk through several steps to get your data in order to use in a dashboard.<\/p>\n

    \n
  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\u2019d like to remove enter 1 and select [ok]. This will remove the tile row from your data. (Image 3 below)<\/li>\n
  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.<\/li>\n
  3. Remove columns you don\u2019t 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.<\/li>\n
  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.<\/li>\n
  5. We are done editing our data, select Close & Apply from the HOME menu ribbon.<\/li>\n
  6. After a few seconds your data fields will appear on the right and you\u2019ll be ready to build your dashboard. (Image 4 below)<\/li>\n<\/ol>\n
    \"Image<\/a>

    Image 03<\/p><\/div>\n

    \"Image<\/a>

    Image 04<\/p><\/div>\n

    Step 5 Adding an Image<\/h3>\n

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

    Step 6 Funnel of Tracks<\/h3>\n

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

    \"Image<\/a>

    Image 05<\/p><\/div>\n

    \"Image<\/a>

    Image 06<\/p><\/div>\n

    \u00a0Step 7 Sessions by level<\/h3>\n

    Let\u2019s 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\u2019s interactive. Click on one of the pieces and watch what happens to your funnel graph.<\/p>\n

    Step 8 Sessions by MVPs<\/h3>\n

    For this select pie chart from visualizations, select MVP and drag MVP to Values. Now you can update the colors and title.<\/p>\n

    Step 9 Sessions by State<\/h3>\n

    Just for fun I wanted to \u201csee\u201d 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?\u00a0\u00a0(Image 7 below)<\/p>\n

    \"Image<\/a>

    Image 07<\/p><\/div>\n

    \u00a0Step 10 Final Product<\/h3>\n

    After finishing my dashboard I didn’t like it so I made a few more adjustments. (Image 8 below) I\u00a0displayed this at our hands-on PowerBI meeting for the NashBI<\/a> ( TWITTER<\/a>\u00a0 |\u00a0 WEB<\/a> )\u00a0user 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\u00a0is 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.<\/p>\n

    \"Image<\/a>

    Image 08<\/p><\/div>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    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 \u201cI need to put this into PowerBI\u201d. 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 . . . → Read More: Visualizing SQLSaturday Data\u2013Session Details<\/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":[286,288,270,285,273,289,149],"tags":[],"class_list":["post-1164","post","type-post","status-publish","format-standard","hentry","category-business-intelligence","category-inside-sqlsaturday","category-instructions","category-nashbi","category-power-bi","category-reporting","category-ssrs","odd"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/posts\/1164","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=1164"}],"version-history":[{"count":0,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/posts\/1164\/revisions"}],"wp:attachment":[{"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/media?parent=1164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/categories?post=1164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/tags?post=1164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}