Displaying images (from R / ORE / BLOBs) in Oracle Data Visualization

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:

               

Sections

1. Using R-Visualizations on Oracle DV Datasets
2. Using ORE Images in your Oracle DV analysis
3. Using BLOB images from your Oracle DB as part of DV analysis

Using R-Visualizations on Oracle DV Datasets

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. This plugin 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. base64Image plugin 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 base64Image plugin 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. 

Here is the complete script:
<script>

       <scriptname>obiee.RImageEncSplit</scriptname>  <version>12.2.1.0.0</version>
       <inputs>
              <column>
                     <name>Prod</name>
                     <nillable>NO</nillable>

                     <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:

Are you an Oracle Analytics customer or user?

We want to hear your story!

Please voice your experience and provide feedback with a quick product review for Oracle Analytics Cloud!