Unlock Actionable Insights from Amazon Redshift Data

Unlock Actionable Insights from Amazon Redshift Data

Do you want to extract actionable insights from your data in a data warehouse? Amazon Redshift allows you to deploy large-scale analytics data in a matter of minutes and start to analyze your data right away. Amazon Redshift is a fast, fully managed, petabyte-scalable data warehouse that makes it simple and cost-effective to analyze all the data in your data warehouse and data lake. You can start with a few hundred gigabytes of data and scale to a petabyte. Regardless of the size of the data set, Amazon Redshift offers fast query performance using sophisticated query optimization, columnar storage on high-performance local disks, and massively parallel query execution.

In this blog article, we will see how to deploy data in an Amazon Redshift data warehouse, connect your database cluster, and visualize your data with Bold BI. This makes it easy to use your data to gain new insights for your business.

Deploying data in Amazon Redshift

Let’s see how to set up an Amazon Redshift cluster and load data. If you are aware of this process already, you can skip this and move to next section titled, Connecting Amazon Redshift database cluster.

Preparing an Amazon Redshift cluster

To create an Amazon Redshift cluster from the AWS Management Console, log in to your existing AWS account or create a new account here.

In the cluster creation page, configure the cluster by choosing the instance type, specifying the number of nodes, and filling the cluster details with a username, password, and port number. Secure your cluster using AWS IAM and set it up for access.

Amazon Redshift cluster configuration window
Amazon Redshift cluster configuration window

Once you’ve set up your cluster, load your data into it.

Loading data into a cluster

Open the Amazon Redshift console. Choose Editor on the navigation menu, then connect to a database in your cluster. Specify the database that you used when you created the cluster or use the default database dev.

Connect to database window in Amazon Redshift
Connect to database window in Amazon Redshift

Create tables under the public schema using the query editor.

Query editor in Amazon Redshift
Query editor in Amazon Redshift

Now, load data to your cluster from Amazon S3 or DynamoDB after defining a schema and creating the tables. Insert your data using the insert query.

You can download data files of the sample tickit database. Load the individual files to a tickit folder in your Amazon S3 bucket in your AWS region. You can load large data sets into Amazon Redshift by using the copy query.

In the copy command, include the IAM role, bucket name, and an AWS Region. You can copy the data from Amazon S3 bucket into your cluster only when Amazon S3 and the cluster are created in the same region.

Copy data from Amazon S3
Copy data from Amazon S3

Ensure that your data was copied to the table by using the select query.

Query editor window in Amazon Redshift
Query editor window in Amazon Redshift

Refer to the getting started guide of Amazon Redshift for more details on how to create a cluster and IAM role, and load data into cluster from Amazon S3.

Set the Publicly accessible setting to yes under the Network and security property of the cluster to allow instances and devices outside the VPC to connect to your database through the cluster endpoint.

Property window of Amazon Redshift cluster
Property window of Amazon Redshift cluster

Getting cluster connection details

Get your cluster connection details from the properties of the cluster.

Connection details of cluster
Connection details of cluster

Now, the Amazon Redshift database cluster is ready to connect with Bold BI.

Let’s see how to connect the Amazon Redshift cluster and visualize the Tickit database data using a Bold BI dashboard through the Amazon Redshift data connection.

Connecting an Amazon Redshift database cluster

To create an Amazon Redshift data source from the designer, click the Data Sources button in the configuration panel and select Amazon Redshift in the connection panel. Now the connection window will open, as shown in the following image.

Amazon Redshift connection window
Amazon Redshift connection window

Specify the name and description as you wish. Provide the server name, port, and database name of your cluster.

In our example, the endpoint is XXXX-redshiftcluster.XXXX.ap-south-1.redshift.amazonaws.com:5439/dev

Server name: XXXX-redshiftcluster.XXXX.ap-south-1.redshift.amazonaws.com

Port: 5439

Database: dev

Enter the username and password of your cluster.

Amazon Redshift database details in connection panel
Amazon Redshift database details in connection panel

Click Preview & Connect to connect with the configurations set. Drag the table you created and save the Amazon Redshift data source.

Data source query editor window
Data source query editor window

Once the data source is created, add the required widgets to your dashboard and connect data to them to visualize the metrics.

Visualizing your data

Let’s visualize the ticket sales data using a Bold BI dashboard through the Amazon Redshift data connection. Refer to the image of the Ticket Management Dashboard which can be viewed in this demo.

Ticket Management Dashboard
Ticket Management Dashboard

This dashboard focuses on tracking ticket sales performance through the following metrics:

  • Available tickets
  • Total events
  • Total buyers
  • Sales amount
  • Paid amount by month
  • Tickets sold by category across states
  • User count by category
  • Ticket details
  • Tickets sold by date
  • Top 5 events based on tickets sold

Conclusion

We hope this blog article guides you in preparing Amazon Redshift clusters in AWS and deploying data in your database cluster. You now also know how to visualize data through a ticket management dashboard using Bold BI. If you have any questions, please post them in the comments section below. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in to submit your support question. Bold BI dashboards now come with a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold BI for yourself.

Start your free 15-day trial today

Leave a Reply

Live Chat Icon For mobile
Live Chat Icon