Sunday, February 5, 2023
HomeHow ToBuild Credit Card Analytics with Oracle Autonomous Database using Devart ODBC for...

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

Contents

1. Abbreviation

2. Objective

3. Technology Background

3.1. Power BI Desktop

3.2. Oracle Autonomous Data Warehouse Database

3.3. Devart ODBC for Oracle Driver

4. Prerequisites

5. Architecture

6. Install Devart ODBC for Oracle

7. Driver Configuration

7.1. Wallet Configuration

7.2. Window DNS Configuration

8. Create Data

9. Create ODBC Connection in PowerBI Desktop

10. Build Credit Analytics

11. Conclusion

1. Abbreviation

Oracle Cloud Infrastructure ConsoleOCI
Oracle Autonomous Data WarehouseADW

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

  • 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

COLUMNDESCRIPTIONDATA TYPE
AGEAge of PersonNUMBER(3,0)
SEXSex/Gender: male, femaleVARCHAR2(20)
JOB0 – unskilled and non-resident1 – unskilled and resident2 – skilled3 – highly skilledNUMBER(1,0)
HOUSINGIdentify a person who owns a house or notown, rent, or freeVARCHAR2(50)
SAVING_ACCOUNTSKind of Saving Accountslittle, moderate, quite rich, richVARCHAR2(50)
CHECKING_ACCOUNTNUMBER(22,7)
CREDIT_AMOUNTNUMBER(22,7)
DURATIONIdentify how long for creNUMBER(4,0)
PURPOSEThe purpose of usage:car, furniture/equipment, radio/TV, domestic appliances, repairs, education, business, vacation/othersVARCHAR2(150)
RISKValue Target:goodbadVARCHAR2(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

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

Whitepaper

Social

Topics

Products