Visualize data in Amazon RDS for SQL Server

using Amazon QuickSight

In this tutorial, you create and visualize data in an Amazon Relational Database (Amazon RDS) MS SQL Express server using Amazon QuickSight.

Amazon RDS for SQL Server makes it easy to set up, operate, and scale SQL Server deployments in the cloud.

Amazon QuickSight is a scalable, serverless, embeddable, machine learning-powered Business Intelligence (BI) service built for the cloud. Using the Amazon RDS connector in Amazon QuickSight, organizations can seamlessly gather insights from RDS data without a single line of code.

In this tutorial, you learn how to:

Create a Microsoft SQL Server Express Edition database in Amazon RDS.
Download and connect to a Microsoft SQL Server client.
Create a sample database and tables, and load sample data to be accessed in Amazon QuickSight.
Enable the security groups on Amazon RDS for Amazon QuickSight to connect to RDS datasets.
Create an Amazon QuickSight account.
Enable Amazon QuickSight to connect to Amazon RDS, and create a dataset for visualization.
Clean up resources.

The AWS services you use in this tutorial are AWS Free Tier eligible.

About this Tutorial
Time	20 minutes
Cost	AWS Free Tier Eligible
Use Case	Analytics
Products	Amazon QuickSight, Amazon RDS for SQL Server
Audience	Developer
Level	Beginner
Last Updated	May 26, 2021

Step 1. Create an AWS Account

The resources created and used in this tutorial are AWS Free Tier eligible.

Sign-up for AWS

Already have an account? Sign-in

Step 2. Create a Microsoft SQL Server Express Edition database in Amazon RDS

Complete the following steps to connect to a Database Engine in Amazon RDS.

a. Open the Amazon RDS console and choose the Region where you want to create the Database.

b. In the Create Database section, choose Create Database.

c. On the Create database page, in the Choose a database creation method section, choose Easy Create.

d. In the Configuration section, make the following changes:

For Engine type, choose Microsoft SQL Server.

For DB instance size, choose Free tier.

For DB instance identifier, type qsdatabase.

For Master username, enter admin.

For Master password, type a unique password, and confirm password.

e. In the View default settings for Easy create drop down, leave the default settings. Then, choose Create database.

Note: It may take several minutes for the database to be created.

Step 3. Download and connect to a Microsoft SQL Server client

Complete the following steps to download Microsoft SQL Server Management Studio, and create tables to run queries against the database.

a. Open the Download Microsoft SQL Server Management Studio page, choose the link under the Download SSMS section.

b. Open the Amazon RDS console, in the left-hand navigation pane, choose Databases. Then, choose the qsdatabase.

c. On the qsdatabase page, choose Modify.

d. On the ModifyDB instance: qsdatabase page, in the Connectivity section, choose Additional Configuration. Then, choose Publicly accessible, and choose Continue.

e. On the ModifyDB instance: qsdatabase page, in the Scheduling of modifications section, choose Apply immediately. Then, choose Modify DB instance.

f. On the left-hand navigation, choose Databases. Then, choose qsdatabase.

g. On the qsdatabase page, in the Connectivity & security section, choose the VPC security groups link.

h. On the Security groups page, choose the Security group ID.

i. On the sg-default page, in the Inbound rules section, choose Edit inbound rules.

j. On the edit inbound rules page, in the Inbound rules section, choose Add rule, and make the following changes.

For Type, choose All TCP from the drop-down list.
For Source, choose My IP.

k. Then, choose Save rules.

l. Verify that the SSMS Client download has completed. Then, install and open the software.

m. In the SQL Server pop up window, enter the following details.

For Server Name, paste the qsdatabase Endpoint and Port separated by commas. Example: qdatabase.abc.us-east-1.rds.amazonaws.com,1433.

Note: To find the endpoint, open the Amazon RDS console, and choose qsdatabase. On the qsdatabase page, in the Connectivity & Security section, copy the Endpoint and Port.

For Login, type the username you entered when creating the qsdatabase.
For Password, type the password you entered when creating the qsdatabase.

n. Then, choose Connect.

Step 4. Create a sample database and tables, and load sample data

Complete the following steps to create a sample database, create and load tables that can be accessed in Amazon QuickSight.

a. Open SQL Server Management Studio, in the left-hand navigation, choose Databases. Then, right click and choose Create Database.

b. On the New database page, for Database name, type Visualize. Then, choose OK.

c. Choose Visualize, and choose New Query.

d. In the Query editor, copy and paste the following script.

Once the script is successfully run, the tables will be created and loaded with the sample data.

CREATE TABLE newhire(
empno INT PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
manager INT NULL,
hiredate DATETIME,
salary NUMERIC(7,2),
comm NUMERIC(7,2) NULL,
department INT)
begin
insert into newhire values
    (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4)
insert into newhire values
    (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3)
insert into newhire values
    (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3)
insert into newhire values
    (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3)
insert into newhire values
    (5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4)
insert into newhire values
    (6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4)
insert into newhire values
    (7,'POLK','TECH',6,'09-22-1997',25000,NULL,4)
insert into newhire values
    (8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2)
insert into newhire values
    (9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4)
insert into newhire values
    (10,'FILLMORE','MANAGER',9,'08-09-1994',56000,NULL,2)
insert into newhire values
    (11,'ADAMS','ENGINEER',10,'03-15-1996',34000,NULL,2)
insert into newhire values
    (12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4)
insert into newhire values
    (13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2)
insert into newhire values
    (14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1)
end
CREATE TABLE department(
deptno INT NOT NULL,
dname VARCHAR(14),
loc VARCHAR(13))
begin
insert into department values (1,'ACCOUNTING','ST LOUIS')
insert into department values (2,'RESEARCH','NEW YORK')
insert into department values (3,'SALES','ATLANTA')
insert into department values (4, 'OPERATIONS','SEATTLE')
end

Step 5. Make the database instance Not publicly accessible

The database no longer needs to be publicly accessible; the previous script downloaded the required scripts from the client.

Complete these steps to connect Amazon QuickSight to RDS within a VPC.

a. Open the Amazon RDS console, in the left-hand navigation, choose Databases. Then, choose the qsdatabase.

b. On the qsdatabase page, choose Modify.

c. On the ModifyDB instance:qsdatabase page, in the Connectivity section, choose Additional Configuration. Then, choose Not publicly accessible, and choose Continue.

d. On the ModifyDB instance:qsdatabase page, in the Scheduling of modifications section, choose Apply immediately. Then, choose Modify DB instance.

Step 6. Enable the RDS database instance for access to Amazon QuickSight

Follow these steps to create a security group for Amazon QuickSight to access the RDS database in a VPC.

a. Open the Amazon RDS console, in the left-hand navigation, choose Databases. Then, choose the qsdatabase.

b. On the qsdatabase page, in the Connectivity & security section, copy the VPC id.

c. Under Security, choose the VPC security groups link.

d. On the Security Groups page, choose Create security group.

e. On the Create security group page, in the Basic details section, enter the following details.

For Name, type RDS SecGP
For Description, type for QS
For VPC, choose the VPC id for your RDS instance.

f. Then, choose Create security group.

g. On the Security Groups page, copy the Security group ID.

h. On the Security Groups page, choose Create security group.

i. On the Create security group page, in the Basic details section, enter the following details.

For Name, type QS SecGP
For Description, type for RDS
For VPC, choose the VPC id for your RDS instance.

j. In the Inbound rules section, choose Add rule.

For Type, choose All traffic
For Source, choose Custom
In the search box, paste the security group id you copied in step 6.g.

k. Choose Create security group.

l. On the sg-QS SecGp page, copy the security group id. This security group is needed for Amazon QuickSight to connect to Amazon RDS.

m. On the Security Groups page, choose the security group you created in step 6.g.

n. In the Inbound rules section, choose Edit inbound rules.

o. On the Edit inbound rules page, in the Inbound rules section, choose Add rule. Then, enter the following details.

For Type, choose MSSQL
For Source, choose Custom
In the search box, paste the security group id you copied in Step 6.l

p. Choose Save rules. This security group is needed for Amazon RDS to connect Amazon QuickSight.

q. Open the Amazon RDS console, in the left-hand navigation, choose Databases. Then, choose the qsdatabase.

r. On the qsdatabase page, choose Modify.

s. On the Modify DB instance: qsdatabase page, in the Connectivity section, for Security group, choose RDS SecGP (for QS). Then, choose Continue.

t. On the Modify DB instance: qsdatabase page, in the Scheduling of modifications section, choose Apply immediately. Then, choose Modify DB instance.

Step 7. Create your Amazon QuickSight account

Complete the following steps to create your Amazon QuickSight account.

Note: For more information, see Setting up Amazon QuickSight in the Amazon QuickSight documentation.

a. Open the Amazon QuickSight landing page, and choose Sign up for QuickSight.

b. On the Create you QuickSight account page, for Edition, choose Enterprise, and choose Continue.

c. On the Create your QuickSight account page, in the Edition section, choose Use IAM federated identities and QuickSight-managed users.

d. In the QuickSight region section, enter the following details.

Select a region from the drop-down list.
For QuickSight account name, type a unique account name.
For Notification email address, type an email address where you will receive notifications.

e. Then, choose Finish.

f. Choose Go to Amazon QuickSight, to open the Amazon QuickSight console.

Step 8. Enable Amazon QuickSight to connect to Amazon RDS and create a dataset for visualization

Complete the following steps to create a secure private connection to an Amazon VPC, and visualize the Amazon RDS data.

Note: For more information, see Configuring the VPC Connection in the QuickSight Console in the Amazon QuickSight documentation.

a. On the Analyses page, in the top right corner of the screen, and choose your username. Then, from the drop-down list, choose Manage QuickSight.

b. On the left navigation pane, choose Manage VPC connections. Then, choose Add VPC connection.

c. In your web browser, open a new tab. Then, open the Amazon RDS console, in the left-hand navigation, choose Databases. Then, choose the qsdatabase.

d. On the qsdatabase page, in the Connectivity & security section, under VPC, copy the id. Then, under Subnets, copy one of the ids.

d. Navigate back to the Adding VPC connection page, and enter the following details.

For VPC connection name, type RDSVPC
For VPC ID, choose the id you copied in Step 8.e
For Subnet ID, paste the id you copied in Step 8.e
For Security group ID, paste the id you copied in Step 6.g

e. Then, choose Create.

f. On the top left corner of your screen, choose the QuickSight icon. Then, in the left navigation, choose Datasets.

g. On the Datasets page, choose New dataset.

h. On the Create a Datasets page, choose RDS.

i. On the New RDS data source page, enter the following details.

For Data source name, type DataFromRDS
For Instance ID, choose qsdatabase
For Connection type, choose RDSVPC
For Database name, type Visualize
For Username, type the username you entered when creating the Visualize database
For Password, type the password you entered when creating the Visualize database

j. Then, choose Validate connection. If the connection was successful, choose Create data source.

k. On the Choose your table page, in the Schema section, choose dbo.

l. In the Tables section, choose newhire. Then, choose Select.

m. On the Finish dataset creation page, leave the default selections, and choose Visualize.

n. On the Visualize page, in the Visual types section, choose the Stacked Area Line Chart.

o. In the Fields list section, drag and drop ename and salary to the Field Wells section.

Note: For more information, see Working with Visuals in the Amazon QuickSight documentation.

Step 11. Clean up

In this step, you delete the resources you used in this lab.

Important: Deleting resources that are not actively being used reduces costs and is a best practice. Not deleting your resources will result in charges to your account.

a. Delete the dashboard: On the QuickSight home page, choose All dashboards. Choose the details icon (...) of the dashboard you published, and choose Delete. When prompted to confirm, choose Delete.

b. Delete the analysis: Choose the details icon (...) of the newhire analysis and choose Delete. When prompted to confirm, choose Delete.

c. Delete the data: Choose Manage data. Choose the newhire data set and then choose Delete data set. When prompted to confirm, choose Delete.

d. Delete the database instance: Open the Amazon RDS console, select Databases, and choose qsdatabase. Then, from the Actions drop down menu, choose Delete.

e. Manage QuickSight usage and subscription: For information on the QuickSight trial, SPICE capacity, adding readers, or managing your subscription, see Managing Amazon QuickSight Usage. For information on pricing after the Amazon QuickSight 60-day trial, see Amazon QuickSight Pricing.

f. Uninstall the MS SQL Management Studio client: using the Add or Remove programs on Windows.

Congratulations

You created a sample Amazon RDS instance on an MS SQL engine, and connected the data to for visualization using Amazon QuickSight.

Was this tutorial helpful?

Feedback

Recommended next steps