QuickSight Dashboards

Create a Custom Query

  1. Launch into QuickSight – https://us-east-1.quicksight.aws.amazon.com/sn/start.
  2. Choose Manage data from the top right.
  3. Choose New Data Set.
  4. Create a new Athena Data Source.
  5. Select the socialanalyticsblog database and the tweet_sentiments table.
  6. Then choose Edit/Preview Data. quicksight table
  7. Under Table, choose Switch to custom SQL tool: quicksight sql
  8. Give the query a name (such as SocialAnalyticsBlogQuery)
  9. Put in this query:

    SELECT  s.*,
            e.entity,
            e.type,
            e.score,
            t.lang as language,
            coordinates.coordinates[1] AS lon,
            coordinates.coordinates[2] AS lat ,
            place.name,
            place.country,
            t.timestamp_ms / 1000 AS timestamp_in_seconds,
            regexp_replace(source,
            '\<.+?\>', '') AS src
    FROM socialanalyticsblog.tweets t
    JOIN socialanalyticsblog.tweet_sentiments s
        ON (s.tweetid = t.id)
    JOIN socialanalyticsblog.tweet_entities e
        ON (e.tweetid = t.id)
    1. Then choose Finish.
    2. This saves the query and lets you see sampled data.
    3. Switch the datatype for the timestamp_in_seconds to be a date: typechange
    4. Then choose Save and Visualize.

    Create a Dashboard

    Now we’ll step through creating a dashboard.

    1. Start by making the first visual in the top-left quadrant of the display. quicksight
    2. Drag and drop type and tweetid from the field list onto the visual.
    3. Select the double arrow drop down next to Field Well. quicksight
    4. Move the tweetid to the value.
    5. And then choose it to perform Count Distinct: quicksight
    6. Now switch it to a pie chart under visualization types. quicksight

    Now let’s add another visual.

    1. Choose Add (near the top left corner of the page) : Add Visual.
    2. Resize it and move it next to your first pie chart.
    3. Now, on the left, drag over sentiment and timestamp_in_seconds.
    4. Under the field wells, or the chart itself, you can zoom in/out of the time. Let’s zoom into hours: quicksight

    5. Suppose on the timeline, we only want to see positive/negative/mixed sentiments. The Neutral line, at least for my Twitter terms, is causing the rest not to be seen easily. quicksight

    6. Just click the Neutral line and in the box that appears choose to Exclude Neutral. quicksight quicksight

    You can build multiple dashboards, zoom in and out of them, and see the data in different ways. Use the remaining time in this lab to try to build the dashboard below: quicksight