Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Visualize Your Amazon Redshift Data Using Tableau

DZone's Guide to

How to Visualize Your Amazon Redshift Data Using Tableau

The integration between Tableau and Redshift is powerful, but it requires many preliminary steps. Panoply eliminates most data engineering and database admin operations.

· Big Data Zone
Free Resource

Read about the story and evolution of the data warehouse, as well as the major challenges that these systems are currenly facing.  Brought to you in partnership with Panopoly.

Are you a data analyst? You must be familiar with both Tableau and AWS Redshift and you must know (or at least you should believe me) that integrating the Redshift columnar-based database speed with the mature, rich, and interactive Tableau creates an awesome experience.

In a previous article, I described how to create custom Google Analytics dimensions, export the data, and import it to Redshift. Don’t miss it, since this one is a continuation of it. In this short one, I will take you through the integration process, through to the eventual presentation of an aggregated graph of page view performance by the number of visitors.

Note: In this guide, we use Tableau desktop version 10.0, which you can easily download and try for free. Once downloaded and installed, you can perform the following steps to connect it with Redshift. It will also help to ensure you have your Redshift information (host name, database name, username, and password) on hand.

Connect

On the Tableau Desktop start page, under Connect, select the Amazon Redshift option.

Connect Redshift and Tableau - Select Redshift

Enter your Redshift information and click Sign In.

Connect Redshift and Tableau - Sign In

On the data source page (shown above), do the following:

Click the data source name at the top of the page, then enter a unique data source name for use in Tableau. It’s good practice to use a data source naming convention that helps other users figure out which data source Tableau is connected to.

From the Schema drop-down list, select a schema or use the text box to search for a schema by name.

Under Table, select a table or use the text box to search for a table by name.

Drag the table to the canvas, then click the sheet tab to start your analysis.

Connect Redshift and Tableau - click the sheet tab

Now that Tableau is connected to our Redshift database, we can move onto visualizing it.

Visualize

From the Tableau Desktop menu bar, select Data and then select a table from the list of data source tables at the bottom of the popup menu. For our example, we wanted to create reports and charts based on the clientid table (as shown below).

Connect Redshift and Tableau - select a table

Next, on the menu bar, select Dashboard New Dashboard.

Connect Redshift and Tableau - select dashboard

A graph is displayed, returning the number of page views by Timestamp, PagePath,and Clientid:

Connect Redshift and Tableau - dashboard view

As shown in the steps below, from this point on, we can experiment with Tableau’s simple UI and filters to visualize the data based on our specific business requirements:

On the menu bar, select Worksheet > New worksheet.

Connect Redshift and Tableau - new worksheet

Next, on the menu bar, select Data > Users.

Connect Redshift and Tableau - worksheet

From the Dimensions and Measures sections in the left pane, you can adjust the worksheet, selecting the relevant columns and rows.

For example, the screenshot below shows which pages have been viewed and how many times, by date.

Connect Redshift and Tableau - pages viewed report

Alternatively, if you want to look at a specific Clientid (information not supported out-of-the-box by GA), you can create a graph similar to this one:

Connect Redshift and Tableau - graph example

Number of page views by specific page by specific clientid and by date.

Or, by making a few simple changes to the arrangement of your data, you can create a different visualization for the same example:

Connect Redshift and Tableau - alternative graph example

Number of page views by specific page by specific clientid and by timestamp.

As can you see, using Tableau Desktop offers a simple way to start visualizing your Redshift data.

Advanced Use Cases: Using Panoply

The integration between Tableau and Redshift is a powerful one. However, there are lots of preliminary steps that are required to make it happen, which can make the whole process cumbersome, lengthy, and hard to maintain. In an advanced real-life case, your database will hold more than two tables and will need to be continuously processed to be ready for visualization.

There are lots of preliminary steps that are required to make it happen, which can make the whole process cumbersome, lengthy, and hard to maintain.

This is where Panoply.io comes into play:

Connect Redshift and Tableau - Panoply connectors

Panoply.io’s visualization plugins.

Panoply.io provides a data warehouse solution, from extracting the data source right through to load and transform. Besides out-of-the-box integration with Tableau, Panoply services also integrate with other external BI tools such as Chart.io, Looker, and PowerBI.

The automated, data management solution leverages Redshift infrastructure and empowers it by eliminating its management overhead, including both the underlying infrastructure and the database layers. You can think of Panoply as an automated data engineering robot, eliminating the vast majority of data engineering and database administrative operations.

Read about the story and evolution of the data warehouse, as well as the major challenges that these systems are currenly facing.  Brought to you in partnership with Panopoly.

Topics:
amazon redshift ,tableau ,panoply ,big data

Published at DZone with permission of Alon Brody. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}