A Pictochart Custom Visualization plugin was just made available for free on Oracle BI Public Store. Anyone can download and deploy and use this plugin on Oracle DV Desktop in a few minutes. A PictoChart uses discrete icons or images to visualize an absolute number or a percentage of a population that shares a certain feature. It is extensively used in infographics as a more interesting and effective way to present numerical information than traditional tables and lists. This particular plug-in comes with various options that let you choose if you wish to see these icons as an absolute value or as a percentage to total. There are different icon options to choose from. It also comes with various interactivity features like highlighting, filtering, selection etc which make it visually compelling.
In this blog we will discussing ways in which Oracle Data
Visualization (DV) can render images results from R scripts, Oracle R
Enterprise (ORE) scripts or Oracle database BLOBs. We'll make use of new Oracle
Visualization plugin called base64Image to
achieve this (available on Oracle BI
Public Store).
This plugin can consume
and display base 64 encoded binary images directly on Oracle DV. Such images
displayed on Oracle DV may be fully integrated with users’ analysis and change
according to filters/data selected. In this blog let us go through process to
follow display visualizations generated by R, ORE and display binary images
stored in your database in BLOB format. Following video is a brief overview of
this functionality:
It is well known that OracleDV supports R-Integration and
allows users to run their Custom R-scripts on Oracle DV. This integration is
quite versatile and powerful because Oracle DV allows users to fetch data from
R-Scripts in a tabular format and visualize it or perform further analysis on
top of the result data.
With the help of base64Image
Viz Plugin, we go beyond consuming the tabular results. Thisplugin
renders R-Visualizations/Images directly and they are auto-updated based on
users filter/data selection.
This combination of fully interactive Tabular data and
real-time updatable Image outputs generated by R empowers users to fully
leverage R-Advanced Analytics capabilities in a user friendly manner.
How does it work: Image
generated by R-Script is captured and converted to base64 encoded string. The
base64 encoded image string is split into multiple strings of 2000 characters
each. These strings are then stored in an R data frame and returned to Oracle DV.
base64Imageplugin then concatenates back these small pieces of base64
strings and renders the image in OracleDV.
Steps to be followed: This example shown in the above video shows a Heatmap generated by R-Script
on Oracle DV. Following is the visualization:
Following steps to walk you through the process followed to generate this heatmap on Oracle DV.
1)Download
and deploy base64Imageplugin from Oracle BI
Public Store.
2) Install base64enc R-package using
R or R-studio and load the package using “library(base64enc)” command.
3) Create an R-script that does the analysis and generates the image
output.
<required>YES</required>
</column>
<column>
<name>Sales</name>
<nillable>NO</nillable>
<required>YES</required>
</column>
<column>
<name>Profit</name>
<nillable>NO</nillable>
<required>YES</required>
</column>
<column>
<name>Q_Ordered</name>
<nillable>NO</nillable>
<required>YES</required>
</column>
<column>
<name>Shipping_Cost</name>
<nillable>NO</nillable>
<required>YES</required>
</column>
</inputs>
<outputs>
<column>
<name>img_id</name>
<datatype>varchar(10)</datatype>
<aggr_rule>none</aggr_rule>
</column>
<column>
<name>img_part_id</name>
<datatype>varchar(10)</datatype>
<aggr_rule>none</aggr_rule>
</column>
<column>
<name>img_part</name>
<datatype>varchar(2000)</datatype>
<aggr_rule>none</aggr_rule>
</column>
<column>
<name>Metric</name>
<datatype>double</datatype>
<aggr_rule>sum</aggr_rule>
</column>
</outputs>
<options>
<option>
<name>dummmy</name>
<value>100</value>
</option>
</options>
<scriptcontent>
<![CDATA[
function(dat,dummy) { library(base64enc) #################### Image -1 HeatMap Generation ###################### for (i in 2:ncol(dat)) dat[,c(i)] <- as.numeric(dat[,c(i)]) hm_dat <- as.data.frame(aggregate(dat[,c(2:4)], by=list(Product_Sub_Category=dat[,c(1)]), FUN=sum)) df <- as.matrix(as.data.frame(lapply(hm_dat[,c(2:4)], as.numeric))) metrics_matrix <- as.matrix(df) row.names(metrics_matrix) <- hm_dat[,c(1)] fpath = tempfile() # setup tempfile to capture the heatmap image png( fpath, width = 600, height = 600 ) hv <- heatmap(metrics_matrix, col = cm.colors(512), scale="column", Rowv=NA, Colv=NA, margin=c(5,10),xlab = "", ylab= "Product Sub Categories",main = "Metrics heatmap") dev.off() # Convert the image into a base64 encoded string p <- base64encode(fpath) # Break the image into substrings and number them s <- substring(p, seq(1, nchar(p)-1, 2000), seq(2000, nchar(p)+2000, 2000)) # Capture all the fragments into a data frame o <- data.frame(img_id=1,img_part_id = substring(1000+1:length(s),2,10), img_part = s) o$Metric <- 1 return(o); } ]]>
</scriptcontent>
</script>
Explanation of key
elements in the above R-Code:
4) R-Visualization that is going to be generated should be captured in a
temporary image file before converting
it to base64 encoded string. Following lines of code does this process:
fpath = tempfile()
png( fpath, width = 600, height =
600 )
5) Generate the R-image. Following line of code generates Heatmap: hv <- heatmap(metrics_matrix, col =
cm.colors(512),
scale="column",Rowv=NA, Colv=NA,
margin=c(5,10), xlab = "", ylab=
"Product Sub Categories",
main = "Metrics
heatmap")
6) Convert the image to base64 encoded string and then split
the string into smaller strings of 2000 characters each. Then put these smaller
strings in a data frame and return. Following are the lines of code that does
this process:
p <-
base64encode(fpath)
s <- substring(p, seq(1,
nchar(p)-1, 2000), seq(2000, nchar(p)+2000, 2000))
o <- data.frame(img_id=2,image_part_id
= substring(1000+1:length(s),2,10), image_string = s)
7) In OracleDV, create a new project using “Sample
Online Sales” data and add following three calculations:
Calculation-1 : Name: Img_Id
EVALUATE_SCRIPT('filerepo://obiee.RImageEncSplit.xml','img_id',
'Prod=%1;Sales=%2;Profit=%3;Q_Ordered=%4;shipping_Cost=%5',
XSA('weblogic'.'Sample Order Lines')."Columns"."Product Sub
Category",XSA('weblogic'.'Sample Order
Lines')."Columns"."Sales",XSA('weblogic'.'Sample Order
Lines')."Columns"."Profit",XSA('weblogic'.'Sample Order
Lines')."Columns"."Quantity Ordered",XSA('weblogic'.'Sample
Order Lines')."Columns"."Shipping Cost")
NOTE: Please note, if only a single
image is returned by R, just use the value 1
Calculation-2: Name: img_part_id
EVALUATE_SCRIPT('filerepo://obiee.RImageEncSplit.xml','img_part_id',
'Prod=%1;Sales=%2;Profit=%3;Q_Ordered=%4;shipping_Cost=%5',
XSA('weblogic'.'Sample Order Lines')."Columns"."Product Sub
Category",XSA('weblogic'.'Sample Order Lines')."Columns"."Sales",XSA('weblogic'.'Sample
Order Lines')."Columns"."Profit",XSA('weblogic'.'Sample
Order Lines')."Columns"."Quantity
Ordered",XSA('weblogic'.'Sample Order
Lines')."Columns"."Shipping Cost") Calculation-3: Name: img_part EVALUATE_SCRIPT('filerepo://obiee.RImageEncSplit.xml','img_part',
'Prod=%1;Sales=%2;Profit=%3;Q_Ordered=%4;shipping_Cost=%5',
XSA('weblogic'.'Sample Order Lines')."Columns"."Product Sub
Category",XSA('weblogic'.'Sample Order
Lines')."Columns"."Sales",XSA('weblogic'.'Sample Order
Lines')."Columns"."Profit",XSA('weblogic'.'Sample Order
Lines')."Columns"."Quantity Ordered",XSA('weblogic'.'Sample
Order Lines')."Columns"."Shipping Cost")
8)
Select all the 3 newly calculated items; right click; choose pick
visualization; and select “Base64Image Plugin”
9) It generates following visualization:
Using ORE Images in your Oracle DV analysis to get more insights
Oracle R Enterprise (ORE) integrates R with Oracle Database.
ORE is designed to perform analysis on large volumes of data stored in Oracle
Database. Not only does it offer compelling analytics capabilities by
leveraging the Parallelism and Scalability of Oracle Database but it also visualizes
data stored in your Oracle DB using cool Visualization capabilities of R. With
this new Visualization plugin on OracleDV we can now display those visualizations
generated by your ORE scripts that are sitting in your Oracle Database. All you
have to do is fire a SQL that invokes your ORE script and see the Visualization
on Oracle DV
How does it work: From Oracle DV users
have to issue a SQL that invokes the ORE script. This SQL consumes the
Visualization generated by ORE script which is in a binary format, encodes the
image in base64 format, splits the encoded string and sends it to the plugin.
The plugin smartly stitches up the encoded image string fragments and renders
the image on your OracleDV canvas.
Steps to follow:
1) If not done already, download and deploy base64Image plugin from Oracle BI
Public Store.
2) Open Oracle DV and create a New Project.
3) Connect to your Oracle Database and choose “Enter SQL” to fetch the
data.
4) Enter SQL which invokes your ORE script and consumes the image in base64
encoded string fragments. SQL will looks like this:
select
id,(column_value-1)/1455 img_part_id,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(DBMS_LOB.SUBSTR(image,
1455, column_value))) img_part
from (<SQL
that invokes ORE_Script>) t1, table(cast(multiset(select 1455*(level-1)+1 idx from dual connect by
level <= ceil(length(image)/1455)) as sys.OdciNumberList)) t2
In this example we used a SQL which invokes ORE Script that visualizes
Association Rules on a movie database generated using Associating rule mining
algorithm. Following is the SQL:
select
id,(column_value-1)/1455 img_part_id,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(DBMS_LOB.SUBSTR(image, 1455,
column_value))) img_part
from (select id, image from
table(rqTableEval(cursor(select 0 from dual), cursor(select 550
"ore.png.height", 550 "ore.png.width",1
"ore.connect" from dual),'PNG','Association Rules Movies'))) t1,
table(cast(multiset(select 1455*(level-1)+1 idx from dual connect by level
<= ceil(length(image)/1455)) as sys.OdciNumberList)) t2;
5) Mark all the 3 columns generated by the SQL as Attribute columns during data
“Prepare”
stage.
6) Select all the 3 newly calculated items; right click; choose pick
visualization; and select “Base64Image Plugin”
7) This displays the output image of the ORE script:
Using BLOB images from
your Oracle DB as part of DV analysis
Want to use images/thumbnails stored in your database in your analysis on
OracleDV? With base64Image visualization plugin you
can easily fetch binary images stored as BLOBs from underlying database and
display them on your OracleDV. Binary images generated from the database are not
just posters, they are interactive and respond to the filters/data selected by
users.
How does it work: It works similar
to the ORE visualizations, from OracleDV users have to issue a SQL that invokes
the ORE script. This SQL consumes the Visualization generated by ORE script
which is in a binary format, encodes the image in base64 format, splits the
encoded string and sends it to the plugin. The plugin smartly stitches up the
encoded image string fragments and renders the image on your Oracle DV canvas.
Steps to follow:
1) If not done already, download and deploy base64Image plugin from Oracle BI
Public Store.
2) Open Oracle DV and create a New Project.
3) Connect to your Oracle Database and choose “Enter SQL” to fetch the
data.
4) Enter SQL which fetches images from the underlying table and consumes the
image in base64 encoded string fragments. SQL will looks like this:
select
id,(column_value-1)/1455 img_part_id,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(DBMS_LOB.SUBSTR(image,
1455, column_value))) img_part
from (<SQL
that fetches images from underlying tables>)t1, table(cast(multiset(select 1455*(level-1)+1 idx from dual connect by
level <= ceil(length(image)/1455)) as sys.OdciNumberList)) t2
In this example we have used a SQL which fetches images from a table
called PRODUCT_IMAGES:
select products,(column_value-1)/1455
img_part_id,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(DBMS_LOB.SUBSTR(image,
1455, column_value))) img_part
from (select
products, image from BISAMPLE.Product_images)t1, table(cast(multiset(select 1455*(level-1)+1 idx from dual connect by
level <= ceil(length(image)/1455)) as sys.OdciNumberList)) t2
Alternatively
users can use the following SQL to fetch ORE images in base64 encoded string
fragments: select Products, itr img_part_id,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(DBMS_LOB.SUBSTR(image, 1455,
1455*(itr-1)+1 ))) img_part
from
(select itr from dual model dimension by (0 rn) measures (0 itr) rules iterate
(2000) (itr[iteration_number] = iteration_number+1)), (select products,
image from BISAMPLE.Product_images) t1, where itr <=
ceil(length(image)/1455);
5) Mark all the 3 columns generated by the SQL as Attribute
columns during data “Prepare” stage.
6) Select all the 3 newly calculated items; right click;
choose pick visualization; and select “Base64Image Plugin”
7)
This displays the images stored in the underlying database. These images
respond to the user selection of data/filters on other visualizations in the
canvas:
Visualizations in Oracle DV are not just cool looking charts,
they have the capability to drive your analysis and gain wonderful insights from
your data. Oracle DV already has capability to use any visualization as a filter. The selected visualization acts a driver and filters all the visualizations in that canvas according to your selection on the driver visualization. This helps users in narrowing their analysis. How to do that? Its just few mouse clicks.
With Oracle DVD 12.2.2, Visualizations have become even more
powerful and functional and therefore integral part of your analysis flow. In Oracle DV 12.2.2, now users can also add
visualization level filters. The visualization level filters help users add
filters to the visualizations they are interested in rather than the entire
canvas.
Please go through this demo video to see new filtering
options in Oracle DVD 12.2.2 :
Want to have OBIEE 12.2.1.2 in your SampleApp V607 and enjoy VA lightweight SSO (no double logins when opening VA), or dva projects import/export ? You can directly upgrade the version of OBI in your image to 12.2.1.2 by following
simple steps, without losing any of your content. Download the software from here and follow
the steps outlined in this doc to perform this update:
This is an inplace upgrade, that is, the upgrade operations are performed on the existing 12.2.1.1 domain, so you wont loose any dashboards/reports that you built.
Want to analyze the Sales performance data present in excel
& .csv files in your Dropbox account?? With Oracle DV you don’t even have
to download them. New Oracle DV Desktop allows to connect to your Dropbox account
and fetch excel, .csv files for analysis. You can prepare the data using host
of available wrangling functions, then visualize and analyze the data to gain insights.
In case
the data in the excel sheet is updated you don’t even have to re fetch the files.
All you have to do is refresh your data sources in Oracle DV. The modified
files will be fetched automatically from your Dropbox account.
How to
connect to Dropbox? Users have to generate the OAuth credentials from Dropbox
developers’ console and use those credentials in connection page of Dropbox.
This video demonstrates how to connect to Dropbox and perform analysis:
Want to analyze the Sales performance data present in excel
& .csv files in your Dropbox account?? With Oracle DV you don’t even have
to download them. New Oracle DV Desktop allows to connect to your Dropbox account
and fetch excel, .csv files for analysis. You can prepare the data using host
of available wrangling functions, then visualize and analyze the data to gain insights.
In case
the data in the excel sheet is updated you don’t even have to re fetch the files.
All you have to do is refresh your data sources in Oracle DV. The modified
files will be fetched automatically from your Dropbox account.
How to
connect to Dropbox? Users have to generate the OAuth credentials from Dropbox
developers’ console and use those credentials in connection page of Dropbox.
This video demonstrates how to connect to Dropbox and perform analysis:
Visualize data present in MS Access database and draw
meaningful insights using Oracle DV. New Oracle DV can connect to MS Access and
fetch the data by selecting the tables they want to analyze. Users can also
fetch the data using Custom SQL. Data fetched from MS Access can be wrangled/prepared
in Oracle DV and can be mashed up with data from other data sources seamlessly.
How to connect to MS Access from Oracle DV? It’s as simple
as choosing the access database files on which you want to perform Analysis.
Oracle DV supports multiple file formats of MS Access db files like .accdb &
.mdb. Users have to mention the File Name along with Path information and
Username, password (if any) to access the files. Viola! They have their MS
Access data ready for Analysis in Oracle DV.
Take a look at this video to see how to connect to MS Access
from Oracle DV:
