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 explore the following topics:
- Deploying data in Amazon Redshift
- Connecting an Amazon Redshift database cluster
- Visualizing your data
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.
Steps:
- Preparing an Amazon Redshift cluster
- Loading data into a cluster
- Getting cluster connection details
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.
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.
Create tables under the public schema using the query editor.
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.
Ensure that your data was copied to the table by using the select query.
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.
Getting cluster connection details
Get your cluster connection details from the properties of the 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.
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.
Click Preview & Connect to connect with the configurations set. Drag the table you created and save the Amazon Redshift data source.
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 Sales Activity Tracker Dashboard.
This Sales Activity Tracker 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
To learn more about the metrics and KPIs used in this dashboard example, refer to the Sales Activity Tracker Dashboard demo.
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 on this blog, please feel free to post them in the following comment section. To get started with Bold BI, please request a free 30-minute demo with our experts to discuss creating dashboards and any other features you would like to learn more about. 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.
Originally published at https://www.boldbi.com on January 7, 2020.
