Project description: This is a guided project in which we describe concisely how to set-up an aws rds postgres database instance using terraform, deploy a databse and connect to it to build a simple Power BI dashboard.

The steps we will follow are:

• Deploy an AWS RDS POSTGRES INSTANCE using TERRAFORM (Free Tier)
• Check connection to the POSTGRES INSTANCE using PGADMIN
• Load a relational database (NorthWind) to the RDS POSTGRES instance using PSQL
• Connect Power BI Desktop to the RDS POSTGRES NorthWind database
• Build a Simple Power BI Dashboard using the NorthWind dabase
• Deploy the Power BI dashboard

• An AWS Account set-up (including IAM credentials)
• Be familiar with AWS RDS and VPC settings
• Latest version of TERRAFORM CLI installed and set-up on your local machine (https://www.terraform.io/downloads.html)

AWS provides a relational database cloud service called RDS (Relational Database Service). This means that database instances can be deployed and managed in the cloud by AWS (https://aws.amazon.com/rds/)

Currently, AWS supports multiple flavors of databases, namely:

• Aurora
• Postgres SQL
• MySQL
• MariaDB
• Oracle
• Microsodft SQl server

AWS provides an RDS free tier service for small cloud instances and open source databases such as MySQL, Postgres (https://aws.amazon.com/rds/free/)

750 hours of Amazon RDS Single-AZ db.t2.micro Instance usage running MySQL, MariaDB, PostgreSQL, Oracle BYOL or SQL Server (running SQL Server Express Edition) – enough hours to run a DB Instance continuously each month

We will be using a free tier RDS database instance.

PostgreSQL is a general purpose and object-relational database management system. It is open-source, free and used by many well know companies including uber, netflix, and spotify (https://www.postgresql.org/about/press/faq/)

We will be deploying a Postgres database instance.

Terraform is a tool for building, changing, and versioning cloud infrastructure services efficiently. It is cloud vendor agnostic (i.e. supports Azure, AWS, GCP etc.) and uses infrastrcuture as Code (IAC) to deploy and manage most cloud services (https://www.terraform.io/intro/index.html).

Essentially, terraform lets users set-up cloud services using configuration files which are used to deploy, track, update or shutdown specified cloud services.

We will be using terraform to deploy our RDS database instance in AWS.

1. Create the TERRAFORM configuration file to deploy the AWS RDS POSTGRES instance (file should have a .tf extension)

The following provides the minimum configuration settings to dploy an AWS RDS POSTGRES free tier instance:

  provider "aws" {
    region = "ap-southeast-2"
  }
  resource "aws_db_instance" "dsdj-postgres-db-instance" {
    allocated_storage    = 20
    #db_subnet_group_name = "db-subnetgrp"
    engine               = "postgres"
    engine_version       = "11.5"
    identifier           = "dsdj-postgres-db"
    instance_class       = "db.t2.micro"
    password             = "mypostgrespassword"
    skip_final_snapshot  = true
    storage_encrypted    = false
    publicly_accessible    = true
    username             = "postgres"
    apply_immediately = true
  }

A few key RDS settings to understand:

• Replace mypostgrespassword with a strong password to access the PostGres databse We use aws AWS' db.t2.micro instance so for the free tier service. Anything else, will trigger AWS costs.
• Since I am based in New Zealand, I have am setting up the instance in the ap-southeast-2 region.
• I am making the database and the data accessible from any IP by setting the publicly_accessible setting to true. Beware ! This means that the instance is visible and can be reached anywhere in the word (ie. without having to hop through a proxy AWS EC2 instance for security).
• The postgres database user login and associated passwords are set in the 'username' and 'password' s ettings in the terraform files.
• The postgres database verison is set in the engine_version (11.5 in this case).

2. Deploy the AWS RDS POSTGRES instance using terraform from the CLI

   • Initialize TERRAFORM using the following command: terraform init

   • Check what TERRAFORM is planning to deploy in AWS using the following command: terraform plan

     

   • Deploy our RDS POSTGRES instance in AWS using: terraform apply

     

   • It looks good, an instance seems to have been created without any error !

     

   • Validate manually that TERRAFORM has properly deployed the RDS POSTGRES instance in AWS by checking into the AWS RDS online web console:

     

Remember to release the RDS instance once you are finished to avoid running out of AWS free tier resource ! Use the following command to shut down the database instance : terraform destroy

• Deploy an AWS RDS POSTGRES INSTANCE using TERRAFORM (Free Tier)
• Check connection to the POSTGRES INSTANCE using PGADMIN
• Load a relational database (NorthWind) to the RDS POSTGRES instance using PSQL
• Connect Power BI Desktop to the RDS POSTGRES NorthWind database
• Build a Simple Power BI Dashboard using the NorthWind dabase
• Deploy the Power BI dashboard

reference: https://tech.instacart.com/terraforming-rds-part-1-7cc78f92b24d?gi=bbb72a10f9d1

Now that we have a POSTGRES database instance running in AWS, we will be checking we have proper access to the database from our local environment.

• pgAdmin installed (https://www.pgadmin.org/)

pgAdmin is POSTGRES SQL administration tool. It is a web based gaphical user interface to connect to POSTGRES databases and facilitates the creation, maintenance, querying and use of database objects and tables.

You can use AWS RDS console to check for the host connection information

1. Use pgAdmin to connect to our database instance - create a server

2. Use pgAdmin to connect to our database instance - provide host, port, user login and password connection details

3. Check that you can see the live connection in the pgAdmin UI

• Deploy an AWS RDS POSTGRES INSTANCE using TERRAFORM (Free Tier)
• Check connection to the POSTGRES INSTANCE using PGADMIN
• Load a relational database (NorthWind) to the RDS POSTGRES instance using PSQL
• Connect Power BI Desktop to the RDS POSTGRES NorthWind database
• Build a Simple Power BI Dashboard using the NorthWind dabase
• Deploy the Power BI dashboard

We now have a POSTGRES SQL database instance running in AWS, let's deploy the NorthWind relational tables and content to the database.

Northwind Traders Access database is a sample database. The Northwind database contains sales data for a fictitious company called Northwind > Traders, which imports and exports specialty foods from around the world.

• PSQL CLI installed (http://postgresguide.com/utilities/psql.html)
• NorthWind database SQL script downloaded to your local environment (https://github.com/pthom/northwind_psql/blob/master/northwind.sql)

PSQL is the interactive CLI to interact with Postgres databases. PSQL lets user create, query, update and delete POSTGRES tables and databases through the command line.

1. Deploy the NorthWind database sql script

   • Create the NorthWind database

   psql -h dsdj-postgres-db.clpvihbunw2c.ap-southeast-2.rds.amazonaws.com -U postgres -p 5432 -c "CREATE DATABASE \"NorthWind\";"
   

   

   • Create the NorthWind tables

   psql -h dsdj-postgres-db.clpvihbunw2c.ap-southeast-2.rds.amazonaws.com -U postgres -p 5432 -d NorthWind < northwind.sql
   

   

2. Check that the NorthWind database was properly created with PGADMIN or PSQL

   • PGADMIN

     

   • PSQL, Once connected to the Northwind Database use: \d

     

• Deploy an AWS RDS POSTGRES INSTANCE using TERRAFORM (Free Tier)
• Check connection to the POSTGRES INSTANCE using PGADMIN
• Load a relational database (NorthWind) to the RDS POSTGRES instance using PSQL
• Connect Power BI Desktop to the RDS POSTGRES NorthWind database
• Build a Simple Power BI Dashboard using the NorthWind dabase
• Deploy the Power BI dashboard

Now that we have database instance running with the NorthWind tables loaded, we turn our attention to connecting to the tables with Power BI

• Power BI desktop installed (https://powerbi.microsoft.com/en-us/downloads/)

Power BI is an ETL and visualization Microsoft tool for business analytics and reporting. It provides interactive visualizations and business intelligence capabilitieto create reports and dashboards which can be deployed in Microsoft and Azure's environment.

1. Launch Power BI Desktop

2. Connect to the POSTGRES NorthWind database using AWS host connection information

• Use get data and find the Postgres data source in the list

  

• Enter the Postgres RDS host information and database name

  

• Enter the Postgres RDS host information and database name

  

3. Load to the POSTGRES NorthWind tables

• Power BI will show the available NorthWind database tables available

  

• Select and load the NorthWind database tables

  

• Deploy an AWS RDS POSTGRES INSTANCE using TERRAFORM (Free Tier)
• Check connection to the POSTGRES INSTANCE using PGADMIN
• Load a relational database (NorthWind) to the RDS POSTGRES instance using PSQL
• Connect Power BI Desktop to the RDS POSTGRES NorthWind database
• Build a Simple Power BI Dashboard using the NorthWind dabase
• Deploy the Power BI dashboard

We've connected to the NrthWind database with Power BI and now are going to build a simple set of graphs for our dashboard.

The NorthWind database includes 14 tables and the their relationships - below are a few key tables:

• Suppliers: Suppliers and vendors of Northwind
• Customers: Customers who buy products from Northwind
• Employees: Employee details of Northwind traders
• Products: Product information
• Shippers: The details of the shippers who ship the products from the traders to the end-customers
• Orders and Order_Details: Sales Order transactions taking place between the customers & the company

• Northwind database and table relationships (https://docs.yugabyte.com/latest/sample-data/northwind)
• NorthWind's entity relationship diagram is shown below:

Once the database table are imported in Power BI, the tool with automatically identify the table relationships

1. Check the table relationships in Power BI

2. Build charts and graphs for the dashboard (examples below)

• NorthWind products overview

  

• NorthWind products revenue overview

  

• NorthWind customers overview

  

• Deploy an AWS RDS POSTGRES INSTANCE using TERRAFORM (Free Tier)
• Check connection to the POSTGRES INSTANCE using PGADMIN
• Load a relational database (NorthWind) to the RDS POSTGRES instance using PSQL
• Connect Power BI Desktop to the RDS POSTGRES NorthWind database
• Build a Simple Power BI Dashboard using the NorthWind dabase
• Deploy the Power BI dashboard

We are done, we now simply need to publish the dashboard to a Power BI Workspace and start analyzing !

• Deploy an AWS RDS POSTGRES INSTANCE using TERRAFORM (Free Tier)
• Check connection to the POSTGRES INSTANCE using PGADMIN
• Load a relational database (NorthWind) to the RDS POSTGRES instance using PSQL
• Connect Power BI Desktop to the RDS POSTGRES NorthWind database
• Build a Simple Power BI Dashboard using the NorthWind dabase
• Deploy the Power BI dashboard