Build Credit Card Analytics with Oracle Autonomous Database using Devart ODBC for Oracle

January 16, 2023

1270

1. Abbreviation

Oracle Cloud Infrastructure Console	OCI
Oracle Autonomous Data Warehouse	ADW

2. Objective

This tutorial is to build Credit Card Analytics by leveraging Power BI Desktop, Devart ODBC for Oracle driver and Oracle Autonomous Data Warehouse Database. It will not be used as the instruction on how to work on PowerBI Desktop or ADW Engine instead we will demonstrate how we will work with Devart ODBC for Oracle driver.

3. Technology Background

1. Power BI Desktop

Microsoft Power BI Desktop is built for the analyst. It combines state-of-the-art interactive visualizations, with industry-leading data query and modelling built-in. Create and publish your reports to Power BI. Power BI Desktop helps to empower others with timely critical insights, anytime, anywhere.

2. Oracle Autonomous Data Warehouse Database

Oracle Autonomous Data Warehouse (ADW) is fully-managed and offers high performance. It includes all of the performance of the Oracle Database in the fully-managed environment that is turned and optimized for the Data Warehouse workload. It means you don’t need to take more effort and resources (DBA role) to manage the database and optimize the workload.

Self-Driving

A user defines service levels, the database makes them happen

Self-Securing

Protection from both external attacks and malicious internal users

Self-Repairing

Automated protection from all downtime

3. Devart ODBC for Oracle Driver

ODBC Driver for Oracle is a high-performance connectivity solution with enterprise-level features for accessing Oracle databases from ODBC-compliant reporting, analytics, BI, and ETL tools on both 32-bit and 64-bit Windows, macOS, and Linux. Our ODBC driver fully supports standard ODBC API functions and data types and enables easy and secure access to live Oracle data from anywhere.

A distinctive feature of this driver is the ease of establishing a connection – just specify the Host and Port of the Oracle server and there is no need to install and configure the Oracle Client.

4. Prerequisites

Before starting to connect the Oracle database by using Devart ODBD for Oracle and building Credit Card Analytics, make sure to download the necessary tools and drivers:

Click here to download Power BI Desktop
Click here to download Devart ODBC for Oracle Driver
Click here to download the Credit Card data
Click here to understand how to provision ADW instances on Oracle Cloud

5. Architecture

Power BI Desktop uses Devart ODBC for Oracle driver to connect ADW that is hosted in Oracle Cloud, and query data for building analytics.

6. Install Devart ODBC for Oracle

Download and run the installer file to install

Follow the instruction

Select Destination Location

Select Components -> Full Installation

Click Next

Click Next and enter the Activation Key or choose Trial option -> Next

Click Next to start the installation

After the installation is completed, we continue configuring the driver

7. Driver Configuration

In this step, we will need to configure Oracle Call Interface and Windows DNS

1. Wallet Configuration

Download client credentials and store the file in a secure folder on your client computer by following the link https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/connect-download-wallet.html#GUID-DED75E69-C303-409D-9128-5E10ADD47A35

Unzip/uncompress the credentials file into a secure folder on your client computer such as the folder C:\instantclient-basic-19-3\ADW_DEMO

Create the TNS_ADMIN environment variable and set it to the location of the credentials file:
- Click Start, Run
- Type rundll32.exe sysdm.cpl,EditEnvironmentVariables

Environment Variables window appears -> Click New…
Enter Variable name: TNS_ADMIN
Enter Variable value: C:\instantclient-basic-19-3\ADW_DEMO
- This folder is where the wallet file is uncompressed

Click OK -> Restart your computer to ensure the variable is created completely

2. Window DNS Configuration

Click Start, Run
Type the C:\WINDOWS\SysWOW64\odbcad32.exe if the system is 64bit to open ODBC Data Source Administrator
Click on the Driver tab and make sure Devart ODBC Driver for Oracle is in the list of drivers

Select the User DSN or System DSN tab.
Click Add. The Create New Data Source dialogue will appear.

Select Devart ODBC Driver for ODBC Driver for Oracle and click Finish. The driver setup dialogue will open.

Enter the connection information in the appropriate fields. Check on the checkbox Direct, Oracle Client will not be required to download and install in our environment.
Open tnsname.ora file in the folder where the wallet file is uncompressed. We will get the connection information in the file such as Host Name, Port, and Service Name. We will select one of three services to establish the DNS Configuration for Devart ODBC.

Host: adb.us-ashburn-1.oraclecloud.com
Port: 1522
Service Name: ghkzzgdddmcvkfb_adwdemo_medium.adb.oraclecloud.com
User ID: <database user schema created in ADW instance>
Password: <enter password>
The above information will be different and depend on your ADW instance provisioned

Autonomous Data Warehouse by default supports Mutual TLS (mTLS) connections that clients connect through a TCPS (Secure TCP) database connection using standard TLS 1.2 with a trusted client certificate authority (CA) certificate. Certification authentication with Mutual TLS uses an encrypted key stored in a wallet on both the client (where the application is running) and the server (where your database service on the Autonomous Data Warehouse is running). It means we will need to provide the wallet file for Devart ODBC to ensure the secured connection
Go to Security Settings -> SSL Options
- Wallet Path: path to cwallet.sso file. This file is located in the folder where the wallet file is uncompressed C:\instantclient-basic-19-3\ADW_DEMO\cwallet.sso
- Server Certificate DN: CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US
- Server Certificate DN is extracted from tnsname.ora file that is the value of ssl_server_cert_dn

adwdemo_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=ghkzzgdddmcvkfb_adwdemo_medium.adb.oraclecloud.com))(security=(ssl_server_cert_dn=”CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US“)))

Click on Test Connection and make sure the connection is corrected.

8. Create Data

Now, we will load Credit Card data into ADW user schema
Create a new table DW_CREDIT_DATA_F
Import data

Metadata Definition

COLUMN	DESCRIPTION	DATA TYPE
AGE	Age of Person	NUMBER(3,0)
SEX	Sex/Gender: male, female	VARCHAR2(20)
JOB	0 – unskilled and non-resident1 – unskilled and resident2 – skilled3 – highly skilled	NUMBER(1,0)
HOUSING	Identify a person who owns a house or notown, rent, or free	VARCHAR2(50)
SAVING_ACCOUNTS	Kind of Saving Accountslittle, moderate, quite rich, rich	VARCHAR2(50)
CHECKING_ACCOUNT		NUMBER(22,7)
CREDIT_AMOUNT		NUMBER(22,7)
DURATION	Identify how long for cre	NUMBER(4,0)
PURPOSE	The purpose of usage:car, furniture/equipment, radio/TV, domestic appliances, repairs, education, business, vacation/others	VARCHAR2(150)
RISK	Value Target:goodbad	VARCHAR2(50)

SQL script to create a table

CREATE TABLE DW_CREDIT_DATA_F(

    ROW_ID                        NUMBER(10, 0),

    AGE                                NUMBER(3, 0),

    SEX                                 VARCHAR2(20),

    JOB                                 NUMBER(1, 0),

    HOUSING                       VARCHAR2(50),

    SAVING_ACCOUNTS    VARCHAR2(50),

    CHECKING_ACCOUNT VARCHAR2(50),

    CREDIT_AMOUNT        NUMBER(22, 7),

    DURATION                     NUMBER(4, 0),

    PURPOSE                        VARCHAR2(150),

    RISK                                 VARCHAR2(50)

)

9. Create ODBC Connection in PowerBI Desktop

In this step, we will use Devart ODBC for the Oracle driver to establish the connection bridge between PowerBI Desktop and Oracle Database Engine. Make sure that PowerBI Desktop is installed completely.

Open PowerBI Desktop -> Click on Get Data -> Get Data window appears -> Choose Others. On the left panel, there are a lot of drivers that PowerBI supports
Choose ODBC driver -> Connect
From the Data Source dropdown list -> Choose the DNS: Devart_ODBC_ADW which is created in 7.Driver Configuration

Click OK. Then enter username and password if they are required
Click Connect. A Navigator window appears and we will add the DW_CREDIT_DATA table to PowerBI Desktop

Click Transform Data. In this step, we will rename the column name to make them to be easy for understanding. The data set looks like as below after transforming

Click on Close & Apply. Now, it’s ready for us to start building Credit Analytics in PowerBI Desktop.
Right-click on the dataset in the Fields panel and rename it to Credit Data

10. Build Credit Analytics

Before building any reports, we will need to create some measures in PowerBI.
Click on the dataset -> Create new measures by following formulas:
- Total Bad Risk = SUMX(‘Credit Data’,IF(‘Credit Data'[Risk]=”Bad”,1,0))
- Total Good Risk = SUMX(‘Credit Data’,IF(‘Credit Data'[Risk]=”Good”,1,0))

Right-Click on Age -> create a new Group and then set Bin size = 5

We will create two visualizations that will show Histogram By Age Group, using Clustered Column chart type

PowerBI Desktop does not support Box-Plot chart type directly. In case, we would like to analyze the distribution of Credit Amount by Age Categories such as Student, Young, Adult or Senior, we will use a Python script that can be run in PowerBI Desktop in order to create a Box-Plot chart type,
Select the Table chart type and then drag and drop the Age, Credit Amount and Risk fields into the chart. On the Visualization panel, click on Python visual to open the Python script editor panel where we will build our Python script to create the chart

# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:

# dataset = dataset.drop_duplicates()

# Paste or type your script code here:

import pandas as pd

import numpy as np

import seaborn as sns

import matplotlib.pyplot as plt

# List of Age Category

interval = (18, 25, 35, 60, 120)

cats = ['Student', 'Young', 'Adult', 'Senior']

dataset["Age_cat"] = pd.cut(dataset.Age, interval, labels=cats)

# Create Box-plot visualizaton

fig, ax = plt.subplots(figsize=(20,10))

box = sns.boxplot(x=dataset["Age_cat"],y=dataset["Credit Amount"], hue=dataset["Risk"], data=dataset)

box.set_xticklabels(box.get_xticklabels(), rotation=45)

fig.subplots_adjust(bottom=0.5)

plt.tight_layout()

plt.show()

The Age Distribution canvas will look like as below

We can also download the BI Custom Visuals from https://appsource.microsoft.com/en-us/marketplace/apps?page=1&product=power-bi-visuals and import them into our PowerBI Desktop.

11. Conclusion

Devart ODBC Driver for Oracle is a high-performance connectivity solution with enterprise-level features for accessing Oracle databases from ODBC-compliant reporting, analytics, BI, and ETL tools on both 32-bit and 64-bit Windows, macOS, and Linux. It also supports connectivity on both Oracle on-premise database and the Oracle Cloud database.

Build Credit Card Analytics with Oracle Autonomous Database using Devart ODBC for Oracle

Contents

1. Abbreviation

2. Objective