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:
