Mapping Global COVID-19 cases with the Databases for MongoDB EE (Enterprise Edition) Analytics Add-On and Tableau

Step 1: Obtain an API key to deploy infrastructure to your account

Follow the steps here to create an API key. This key is used in Step 2.

Step 2: Clone the repo and change directories into the Terraform directory

Use the following command to clone the appropriate repo and change directories into the Terraform directory:

git clone https://github.com/IBM-Cloud/mongo-bi-tools-tutorial.git
cd mongo-bi-tools-tutorial/terraform

Step 3: Create your terraform.tfvars document

Using the following command, create a document that is named terraform.tfvars:

ibmcloud_api_key = "<your_api_key_from_step_1>"
region = "eu-gb" #Choose a suitable region for your deployment instance.
admin_password = "<create_10_character_password>"

The terraform.tfvars document contains variables that you might want to keep secret.

Step 4: Create your infrastructure

Using the following command, run the Terraform script:

terraform init 
terraform apply --auto-approve
terraform output -json > ../import-covid-data/output.json

The Terraform folder contains a number of simple scripts:

• main.tf tells Terraform to use IBM Cloud.
• variables.tf contains the variable definitions whose values are populated from the terraform.tfvars file.
• mongo.tf creates a Databases for MongoDB EE instance with the Analytics Add-On with the following options:
  • 6 CPU cores
  • 14 GB RAM
  • 20 GB memory.

The Terraform script outputs connection strings that are stored in a file named output.json. This file is used in the following step.

Step 5: Upload data to your Databases for MongoDB EE instance

In this step, you use a Node.js script to upload data to your Databases for MongoDB EE instance. The World Health Organization (WHO) Coronavirus (COVID-19) Dashboard provides a daily update of COVID-19 data (deaths, vaccine utilization, and cases).

Ensure you are in your project directory, then use the following command to import this data into your Databases for MongoDB EE instance.

cd import-covid-data
npm install
node main.js

The npm install command installs all dependencies that are needed by your script. The main.js script does a number of things:

• It reads in the connection strings so that it can connect to your Databases for MongoDB EE instance.
• It reads in your terraform.tfvars credentials file for the password to access your Databases for MongoDB EE instance.
• It reads the COVID data file from the WHO Dashboard. This data file is in CSV format and MongoDB requires JSON format, so the script reads the data and converts it into a JSON document.
• It connects to your Databases for MongoDB EE instance and uploads all data (200k+ rows) into a collection named daily_covid_global_data. If the collection exists, it deletes it and starts again. You can run the script multiple times without importing duplicate data.

It takes only a few seconds to upload all of your data. You are now ready to visualize your using Tableau! The script that you ran will print the connection strings that you need for the next step.

Step 6: Connect your database instance to Tableau

Connect your Databases for MongoDB EE (Enterprise Edition) Analytics Add-On instance to Tableau. In the "Connect" tab, choose the MongoDB BI Connector and enter your connection information:

The server and port values come from the printed output of Step 4. Leave the database blank. The username is "admin". The password is the one you created in Step 2.

In the Initial SQL tab, enter "FLUSH SAMPLE" and click "Sign In"

The FLUSH SAMPLE command tells the BI Connector to re-create the necessary tabular schema from a sample of the collection documents. Whenever your document structure changes, you need to reexecute the FLUSH SAMPLE command to re-create the BI Connector schema.

If your database has many documents, schema creation is a time-consuming process. You should only run FLUSH SAMPLE if your document structure changes or you create new collections.

Step 7: Visualize your data!

You can now select the WHO database on the right. Next, press "Update Now" to see the data in tabular format.

If you select the map view from the right menu, you can also see your data in a nice map, with the columns containing country codes and the rows containing cumulative cases.

Step 8: Deprovision your infrastructure

To avoid incurring charges, remember to deprovision your resources. Terraform can take care of this. In the terminal, make sure you are in the project's terraform directory, then use the following command:

terraform destroy --auto-approve

To confirm that your instance is deprovisioned, check the Resources section in the IBM Cloud Console.