Gather real-time analytics with IBM Cloud Pak for Data on AWS Cloud

Using Data Virtualization on IBM Cloud Pak for Data, hosted on Amazon Web Services (AWS) Cloud, enables developers to break down data silos and speed queries. Companies often try copying disparate data for analysis into central data stores, such as data marts, data warehouses, and data lakes, in order to break down silos. This is costly and prone to error when most manage an average of 400 unique data sources for business intelligence. With Data Virtualization, you can access data at the source without moving data, accelerating time to value with faster and more accurate queries. And you can centralize authentication and authorization for data sources in a trusted environment where credentials for your private databases are stored encrypted at the local device and are private to that device.

The importance of modernizing your organization’s underlying data management can’t be overstated. By leveraging Data Virtualization, machine learning-powered SQL queries, and containerization, data can be made more accessible across your whole organization. This can result in lower operational costs, faster query performance, improved analytics, and more agile development. Follow along with this tutorial to learn how.

Learning objectives

In this tutorial, we will demonstrate how to:

• Virtualize data from distinct sources
• Create data source connections to different sources such as MySQL on AWS, Postgres on AWS, and Db2 Warehouse on IBM Cloud Pak for Data
• Create a single view from multiple tables and multiple data sources
• Connect to the virtualized data as a data source from Cognos-embedded dashboard service

Prerequisites

• IBM Cloud Pak for Data on AWS ROSA
• MySQL database with Amazon Relational Database Service (RDS) connection
• PostgreSQL database with Amazon RDS connection

Estimated time

Completing this tutorial should take 30-45 minutes.

Steps

1. Provision and load data into Amazon Aurora DB for MySQL database.
2. Provision and load data into Amazon Aurora DB for PostgreSQL database.
3. Provision and load data into IBM DB2 on IBM Cloud Pak for Data.
4. Virtualize data from distinct sources using the above three different data sources (databases) in IBM Cloud Pak for Data.
5. Connect to the virtualized data as a data source from Cognos embedded dashboard service of IBM Cloud Pak for Data.

Step 1. Enter credentials

Log into IBM Cloud Pak for Data.

Step 2. Navigate to connections

Go the hamburger (☰) menu, expand Data and click Platform connections.

Step 3. Select third-party connection

Click on New Connection and select Amazon RDS for MySQL from Third-party section.

Step 4. Enter MySQL credentials

Enter your MySQL credentials as shown below. You will get a success message upon successful connection.

Step 5. Enter Db2 credentials

Enter your Db2 credentials you provisioned during the IBM Cloud Pak for Data setup. Click on Platform Connections > New Connection and select Db2 as connection type.

Step 6. Load PostgreSQL data

1. Load data into the Amazon Aurora PostgreSQL database on AWS.

2. Export the PostgreSQL database credentials to your path:

export DB_DATABASE="healthcare"
export DB_USER="<your_DB_USERNAME>"
export DB_PASSWORD="<your_DB_PASSWORD>"
export DB_HOST="<aws_auroradb_HOSTNAME>"
export DB_PORT=5432

Note: The default PORT for PostgreSQL is 5432. If you have provisioned the database with some other port, change the DB_PORT accordingly.

Once the DB credentials are exported to the environment, run the Python script to create tables and insert data into the tables. Go to data-upload-script directory in the cloned repo and run:

cd data-upload-script/
pip install -r requirements.txt
python postgres-upload.py

CREATE TABLE PROVIDERS ("ID" text,"ORGANIZATION" text,"NAME" text,"GENDER" text,"SPECIALITY" text,"ADDRESS" text,"CITY" text,"STATE" text,"ZIP" text,"LAT" text,"LON" text,"UTILIZATION" text);
Tables Created Successfully
Inserting data into PROVIDERS table...
100%|██████████████████████████████████████████████████████████████████████████████████████████████████████████████| 635/635 [07:45<00:00,  1.36it/s]
Insertion Time: 8 min
Total 635 rows inserted successfully

CREATE TABLE PAYERS ("ID" text,"NAME" text,"ADDRESS" text,"CITY" text,"STATE_HEADQUARTERED" text,"ZIP" text,"PHONE" text,"AMOUNT_COVERED" text,"AMOUNT_UNCOVERED" text,"REVENUE" text,"COVERED_ENCOUNTERS" text,"UNCOVERED_ENCOUNTERS" text,"COVERED_MEDICATIONS" text,"UNCOVERED_MEDICATIONS" text,"COVERED_PROCEDURES" text,"UNCOVERED_PROCEDURES" text,"COVERED_IMMUNIZATIONS" text,"UNCOVERED_IMMUNIZATIONS" text,"UNIQUE_CUSTOMERS" text,"QOLS_AVG" text,"MEMBER_MONTHS" text);
Tables Created Successfully
Inserting data into PAYERS table...
100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 10/10 [00:06<00:00,  1.45it/s]
Insertion Time: 1 min
Total 10 rows inserted successfully

CREATE TABLE ORGANIZATIONS ("ID" text,"NAME" text,"ADDRESS" text,"CITY" text,"STATE" text,"ZIP" text,"LAT" text,"LON" text,"PHONE" text,"REVENUE" text,"UTILIZATION" text);
Tables Created Successfully
Inserting data into ORGANIZATIONS table...
100%|██████████████████████████████████████████████████████████████████████████████████████████████████████████████| 634/634 [07:23<00:00,  1.43it/s]
Insertion Time: 8 min
Total 634 rows inserted successfully

CREATE TABLE ENCOUNTERS ("ID" text,"START" text,"STOP" text,"PATIENT" text,"ORGANIZATION" text,"PROVIDER" text,"PAYER" text,"ENCOUNTERCLASS" text,"CODE" text,"DESCRIPTION" text,"BASE_ENCOUNTER_COST" text,"TOTAL_CLAIM_COST" text,"PAYER_COVERAGE" text,"REASONCODE" text,"REASONDESCRIPTION" text);
Tables Created Successfully
Inserting data into ENCOUNTERS table...
100%|██████████████████████████████████████████████████████████████████████████████████████████████████████████████| 27625/27625 [1:06:37<00:00,  1.44it/s]
Insertion Time: 66 min
Total 27625 rows inserted successfully

All Tables Created!

Note: The ENCOUNTERS table has 27,625 rows, which is more compared to other tables, so it might take some time to complete the data insertion. Please be patient.

At this point, you have successfully created PROVIDERS, PAYERS, ORGANIZATIONS, and ENCOUNTERS tables in the Amazon Aurora PostgreSQL database. Follow the same steps to create the PATIENTS table in the Amazon Aurora MySQL database.

Step 7. Load MySQL data

Similar to the previous step, export the MySQL database credentials to your path:

export DB_DATABASE="healthcare"
export DB_USER="<your_DB_USERNAME>"
export DB_PASSWORD="<your_DB_PASSWORD>"
export DB_HOST="<aws_auroradb_HOSTNAME>"
export DB_PORT=3306

Note: The default PORT for MySQL is 3306. If you have provisioned the database with some other port, change the DB_PORT accordingly.

Once the database credentials are exported to the environment, run the Python script to create tables and insert data into the tables. Make sure you are in the data-upload-script directory in the cloned repo and run:

python mysql-upload.py
Inserting data into PATIENTS table...
586it [00:46, 12.60it/s]
Insertion Time: 1 min
586 records inserted successfully

You have successfully created the PATIENTS table in the Amazon Aurora MySQL database.

Step 8. Load data to Db2 Warehouse

Load data into Db2 Warehouse by following the steps in the Collect, cleanse, and enhance your data tutorial. For loading data, use the data files (.csv) procedures, observations, and conditions. Download the data files from GitHub.

Step 9. Virtualize the data

On the IBM Cloud Pak for Data web console, click on the hamburger (☰) menu, expand Data and click Data Virtualization.

Under Data Sources, click on Add connection and select Existing connection since we already created platform connections. Alternatively, you could create a similar data source connection using the respective data source database credentials as a new connection.

Now add the existing connection to the Data Virtualization layer.

Under Data Sources > Virtualization, select Virtualize to initiate the virtualization process.

Select the databases we created connections for; here, we select IBM Db2, MySQL, and Postgres.

To select the tables you want to virtualize, type the table names in the search/find bar. Here, we typed Patients table. Similarly search the other tables (Organizations, Encounters, Patients, and Procedures) and click on Add to cart.

Click on View Cart and assign them to a project. Here we are assigning to an already created project named DataVirtualizationProject. Click Virtualize.

Now go to Virtualized data, select any two tables and click Join. We use a custom SQL query because we want to create a view among different tables from distinct data sources.

Open a SQL editor and click Create new. Note that we will be pasting the SQL copied from this file.

Copy the SQL from the file and paste it in the SQL editor. Now Run All and you will see a success message.

Now that we have successfully created a view, we want to access the view as a new data source for which the credentials can be taken from the service settings. Alternatively, you could choose to fetch the credentials from Connection Information.

Step 10. Access the virtualized data

Let's connect the virtualized view from the embedded Cognos Analytics dashboard, go the hamburger (☰) menu, expand Projects, click All Projects, and open the DataVirtualizationProject. Launch the dashboard service and choose an empty dashboard template.

Connect to the virtualized data view we created earlier by clicking Add Source.

Select HealthCare_DV_View. Please note that the view is available here because we assigned it to a project and the same project we are accessing here.

Here is the sample dashboard screenshot for reference only. (See GitHub for more information on creating a dashboard.)

Summary

In this tutorial, you learned how to configure Data Virtualization on an AWS ROSA cluster and build a view, which can be used as a data source and can be accessed from external applications.