Sunday, February 5, 2023
HomeODBCBuild Prediction Sales Analytics with Power BI Desktop using Devart ODBC for...

Build Prediction Sales Analytics with Power BI Desktop using Devart ODBC for Oracle

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

Technology Background

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 empower others with timely critical insights, anytime, anywhere.

Oracle Database

Oracle database services and products offer customers cost-optimized and high-performance versions of Oracle Database, the world’s leading converged, multi-model database management system, and in-memory, NoSQL and MySQL databases.

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.

Prerequisites

Before starting to connect the Oracle database by using Devart ODBD for Oracle and building Prediction Sales Analytics, make sure to download all 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 Sales data

Make sure to install the Oracle Database application as well.

Architecture

Power BI Desktop uses Devart ODBC for Oracle driver to connect Oracle Database to query data for building analytics.

Install Devart ODBC for Oracle

Download and run the installer file to install

Follow the instructions

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

Driver 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.
  • 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. Make sure the Oracle database is running up.
  • Click on Test Connection and make sure the connection is correct.

Create Data

  • Now, we will load Sales data into the DW_SALES user-schema.
  • Create a new table DW_SALES_F
  • Import data

Metadata Definition

Column Name Description Data Type
ORDERNUMBER Order Number ID NUMBER(10,0)
QUANTITYORDERED Quantity Ordered NUMBER(10,0)
PRICEEACH Price Each NUMBER(10,2)
ORDERLINENUMBER Order Number ID NUMBER(10,0)
SALES Sales NUMBER(10,2)
ORDERDATE Order Date DATE
STATUS Status VARCHAR(50)
QTR_ID Quarter ID of Order Date NUMBER(2,0)
MONTH_ID Month ID of Order Date NUMBER(2,0)
YEAR_ID Year of Order Date NUMBER(4,0)
PRODUCTLINE Product Line VARCHAR(100)
MSRP MSRP VARCHAR(100)
PRODUCTCODE Product Code VARCHAR(100)
CUSTOMERNAME Customer Name VARCHAR(100)
PHONE Phone VARCHAR(100)
ADDRESSLINE1 Address Line 1 VARCHAR(100)
ADDRESSLINE2 Address Line 2 VARCHAR(100)
CITY City VARCHAR(100)
STATE State VARCHAR(100)
POSTALCODE Postal Code VARCHAR(100)
COUNTRY Country VARCHAR(100)
TERRITORY Territory VARCHAR(100)
CONTACTLASTNAME Contact Last Name VARCHAR(100)
CONTACTFIRSTNAME Contact First Name VARCHAR(100)
DEALSIZE Deal Size VARCHAR(100)
CREATE TABLE DW_SALES_F(
    ORDERNUMBER       NUMBER(10, 0),
    QUANTITYORDERED   NUMBER(10, 0),
    PRICEEACH         NUMBER(10, 2),
    ORDERLINENUMBER   NUMBER(10, 2),
    SALES             NUMBER(10, 2),
    ORDERDATE         DATE,
    STATUS            VARCHAR(50),
    QTR_ID            NUMBER(2, 0),
    MONTH_ID          NUMBER(2, 0),
    YEAR_ID           NUMBER(4, 0),
    PRODUCTLINE       VARCHAR(100),
    MSRP              VARCHAR(100),
    PRODUCTCODE       VARCHAR(100),
    CUSTOMERNAME      VARCHAR(100),
    PHONE             VARCHAR(100),
    ADDRESSLINE1      VARCHAR(100),
    ADDRESSLINE2      VARCHAR(100),
    CITY              VARCHAR(100),
    STATE             VARCHAR(100),
    POSTALCODE        VARCHAR(100),
    COUNTRY           VARCHAR(100),
    TERRITORY         VARCHAR(100),
    CONTACTLASTNAME   VARCHAR(100),
    CONTACTFIRSTNAME  VARCHAR(100),
    DEALSIZE          VARCHAR(100)
);

Create ODBC Connection in PowerBI Desktop

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

  • Open PowerBI Desktop -> Click Get Data -> after the Get Data window appears -> Choose Others. On the left panel, there is a list of PowerBI-supported drivers.
  • Choose ODBC driver -> Connect
  • From the Data Source dropdown list -> Choose the DNS: Devart_ODBC_Oracle which was created in the Driver Configuration section of this article.
  • Click OK. Then enter username and password if they are required
  • Click Connect. A Navigator window appears and we will add the Sales table to PowerBI Desktop
  • Click Transform Data. In this step, we rename the column name for better readability
  • Right-click on the Column header -> Rename
  • Click on Close & Apply. Now, we can start building Prediction Sales Analytics in PowerBI Desktop.
  • Right-click on the dataset in the Fields panel and rename it to Sales

Build Sales Analytics

  • Before building any reports we need to create some measures in PowerBI.
  • Click on the dataset -> Create new measures by following formulas:
    • Total Sales = SUM(Sales[Sales])
    • Total Quantity Order = SUM(Sales[Quantity Ordered])
    • Total Order = DISTINCTCOUNT(Sales[Order Number])
  • Let’s build the first visualization based on the sales data in the Oracle database
  • Create a new canvas: Sales By Country
  • Create a map chart by choosing Map chart in the Visualization panel
  • Drag and drop Country from Sales to Location and Legend
  • Drag and drop Total Sales from the Sales dataset to Tooltips
  • Create a Slicer to filter Territory. On the Visualizations panel, select Slicer chart type then drag and drop Territory to Field.
  • Continue creating a new Slicer to filter Order Date
  • Right-click on Country -> Create Hierarchy to create a Country Hierarchy with the following structure
    • Country -> State -> City
  • Right-click on State -> Add to Hierarchy -> Choose Country Hierarchy
  • Right-click on City -> Add to Hierarchy -> Choose Country Hierarchy
  • Now, we create a new Pivot visualization. Then drag and drop Country Hierarchy to Rows and Total Order, Total Sales to values
  • Continue creating new canvas Sales By Product. In this canvas, we will analyze Sales By Product by creating a Line chart for forecasting Total Sales by Year, and a 100% Stacked Bar chart to analyze the % Share of each Product Line by Territory.
  • When we create the Line chart for Total Sales by Order Date, we are able to add Forecast line to forecast Sales Amount for next three years.
  • In the next step, we will need a new canvas to analyze the overview insights.

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 will suit both small- and large-scale businesses, and come to the rescue even if you have literally loads of data to process. In this article, you discovered how easy it is to connect to the database, fetch all data you require, and neatly visualize it for the general audience, with graphs, bells, and whistles. Don’t wait for other signs – proceed to seamless connection and high performance right now!

RELATED ARTICLES

Whitepaper

Social

Topics

Products