Creating QuickSight Dashboards Using Amazon Redshift and Athena
In this article, we will create QuickSight dashboards using both AWS Athena and Redshift. We will use sample data and go through all the processes, from the data source choice to creating dashboards.
Amazon QuickSight Account
We are going to create the QuickSight account first. Once we are located at QuickSight’s main dashboard, we will be directed to QuickSight’s account creation page. We should first choose the type of membership. I will be working on the Standard edition. We can see the Signup link on the page where we have been directed. After clicking on the Signup button, we can define the parameters as below.
We should choose the region accordingly (eu-central-1 for this project) and determine the account name. Once done, we should also populate the email address with a suitable one.
After these are done, we can choose the data sources we desire. I have chosen the below ones.
Then, we can create our QuickSight account. We will have a 30-day trial time for the Standard edition. We should definitely have Auto-Renewal off since we don’t want to be charged after the initial 30 days. We can define it in the Settings.
After creating the account, we can choose Datasets and the dataset as desired.
We have a variety of data sources as below. We are going to use Athena and Redshift for this article.
AWS Athena
We have to choose the Athena as the dataset. Even though Athena might have lots of data sources, I will go through the most common one, the Glue Data Catalog. Once we choose Athena as the source, we will be prompted to a screen where we should choose the data source name.
We have to paste the name of our Glue database here. That should be the database where our main data is located. After writing down the database name and clicking on Create data source, QuickSight will list the tables located in that database.
We can choose the desired table we want to retrieve the data. After choosing that too, we can click on Select. Once everything is done, we can now create our dashboard on QuickSight.
Amazon Redshift
We have to first choose the dataset as Redshift while creating our QuickSight account. Then, we will be prompted to the below screen.
Data source name -> We can give a custom name to our Redshift data source
Instance ID -> The Redshift cluster name
Database name -> While creating our Redshift cluster, we have to decide on our main database name. The default one will be dev.
Username and Password -> While creating our Redshift cluster, we should also define our username and password parameters. We can populate those fields with the ones we defined during the creation.
After clicking on Create data source, we will be prompted to the below screen.
We should select the schema where our main data is located and the table name at the end. We can create tables inside the Redshift cluster from many sources like S3 and Glue Data Catalog.
After clicking on Select, we can create the QuickSight dashboard according to our use case.
Amazon QuickSight Dashboard
After handling all, we can take a look at the dashboard page. I am going to use this JSON data as an example. We are going to see the Visualize section on the left-hand side. Our table columns (attributes) are listed there. We can select our desired field (column) to visualize. We can also choose multiple fields depending on the use case.
We can see below the visual types. We can choose the desired one. Amazon also provides us with a feature named AutoGraph. It automatically creates a suitable graph for the attribute we chose. You can see it in the top left corner.
We can also filter our visuals by choosing the Filter section on the left. We can decide the column to filter and choose the filter type accordingly. There are a variety of aggregation types. The below filter helps us retrieve only the data that has a price less than or equal to 20 pounds, as an example.
If you take a look at the top right corner, you will see the below buttons.
We can save our final dashboard manually or turn the Autosave on. Once it is turned on, we will be automatically able to see the resulting dashboard under the Analyses section on the main QuickSight page.
We can also add this dashboard to our favorites to be able to see it directly when we open the main page. We can also insert many more data sources from the Datasets section (such as S3).
Once we are all done and if we want to download our resulting dashboard, we can click on Export and choose PDF. Our dashboard will be downloaded to our local machine. You might see an example dashboard as a PDF file.
