Assignment No.

Aim: Setup, Create and visualize data in an Amazon Relational Database (Amazon RDS) MS
SQL Express server using Amazon Quick Sight.

Outcome: At the end of this experiment, students will be able to Create and visualize data in an
Amazon Relational Database (Amazon RDS) MS SQL Express server using Amazon Quick
Sight

Theory:

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.

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.

. 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.