Advanced Analytics: Calculate Attribute Importance using Custom R-Scripts on OracleDV

Resources: OracleBI Public Store , Boruta Documentation , Boruta in Action

Attribute Importance is a method that identifies and ranks the attributes that are most important in predicting a target attribute or in understanding the degree of influence of an attribute on the target attribute. For example in a typical customer satisfaction study customers are asked to provide ratings on individual attributes followed by a rating on overall Satisfaction. Customers do not give equal weightage to all attributes and some factors influence the ratings more than others. So marketers need to identify which of these individual attributes are rated more by customers to so that can focus their limited resources on improving the customer satisfaction for that attribute. In such scenarios Attribute Importance method comes to the rescue of Marketers.

In this blog we will discuss a way to calculate the attribute importance on OracleDV using a custom R-Script. This R-Script along with a sample DV project can be downloaded from Oracle BI Public Store. For the purpose of demonstration we have taken a dataset which contains the factors along with metric values that contribute to diabetes. We will take this dataset and identify the importance of each of these attributes in causing diabetes. This R-Script is quite easy to deploy and can be used for many other datasets. This R-Script uses Boruta R package, which  can be downloaded from CRAN repository. Boruta follows an all-relavant feature selection method. This method captures all features which are in some circumstances relavant to the outcome variable.

How does this Script work:This scripts calculates Importance of Attribute Columns(numerical/categorical) in determining values of Target column. Boruta R uses all-relavant feature selection method. This method is performed using multiple iterations; summary of scores obtained by each column in these iterations are returned along with the Decision if the column should be considered important in determining values of Target Column("Confirmed") or not("Rejected"). For more information on the Boruta Package please refer to the Boruta Documentation. For detailed explanation on usage of Boruta R package using an example, please refer to this link: Boruta in Action.

Inputs: This script needs a RecId column, a Target column based on which we will compute Importance of Attribute columns and Attribute columns. Attribute columns can be numerical or categorical
Optional Input: ColNameList : By default script assigns Column1, Column2, Column3 etc as names to the input columns and as a result in output of "ColumnName" column we will see the same names. However to see actual column names in output, pass the actual column names as optional input parameter to the script in a comma seperated format, for ex: "pregnant,diabetes,age.."

Output: This script returns Attribute Importance score of each column in determining the values of Target column along with column names passed as optional input.
   ColumnName: Name of the Column
MeanImp   : Mean of the Importance score computed over multiple iterations.
MedianImp : Median of the Importance score computed over multiple iterations.
MinImp    : Minimum of the Importance score computed over multiple iterations.
MaxImp    : Maximum of the Importance score computed over multiple iterations.
NormHits  : Number of hits normalised to number of importance source runs
   Decision  : "Confirmed" : Column can be considered Important ; "Rejected" : Column has very low importance score and can be neglected

Following are the steps to deploy this R-Script in your local OracleDV:

1) Install Advanced Analytics feature in Oracle DV by clicking on the below icon. This will install Oracle R deployment. Alternatively you can install Advanced Analytics by running install_advanced_analytics.cmd present in <DV_INSTALL_DIRECTORY>

2) If not installed Boruta R-Package already, please install it using following instructions
    Open R console(double click Rgui.exe present in <Advanced_Analytics_Install_Dir>\bin\x64) and
    install Boruta Package.
    Following are the R commands to install:
     Set Proxy:
        $ Sys.setenv(http_proxy="http://<your_proxy_host>:<port>")
           set proxy appropriate to your network settings.
     Install Package:
        $ install.packages("Boruta")
3) Download Attribute_Importance_V1.zip from OracleBI Public Store and unzip it.
4) Copy R.AttributeImportance.xml to <DV_INSTALL_DIRECTORY>\OracleBI1\bifoundation\advanced_analytics\script_repository
5) Import the .dva project to Oracle DV. Password for the .dva is Admin123

Here is a snapshot:

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!

 

Row Expander plugin for drill up/drill down in Oracle Data Visualization

A Row Expander Custom Visualization plugin for Oracle DV Desktop is available on Oracle BI Public Store. This plugin deploys on Oracle DV Desktop in a few minutes and enables a fully interactive drill up/drill down hierarchy experience. 

The plugin accommodates multiple attributes that may or may not belong to a hierarchy, and supports multiple additive metrics in the drilling behavior. 

This first version of the plugin only addresses additive measures (sum), and may hit a limitation in number of rows returned. This limitation will disappear with upcoming builds of DV Desktop. 

The following video highlights the experience of this plugin. 

        

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!

       

Advanced Analytics: Association Rule Mining on OracleDV using Custom R-Scripts

Association Rule Mining is a common technique used to find associations between many variables. It is intended to identify strong rules existing in data using some measures of interestingness. It is often used by grocery stores to perform Market Basket Analysis(MBA), and used by online stores to provide suggestions for purchases. 

Do you have a transactional detaset with you and would like to perform Association Rule Mining on it? You can do it very easily on OracleDV using Rule mining Custom R-Script. The R-script returns association rules in a tabular format with Support, Confidence and Lift associated with each rule. This list of association rules can also be exported/downloaded to excel format. You can also use the script in Dataflows by saving the Rule set generated from your data and consume it as a source in the dataflow. In this blog we will discuss how you can deploy this R-Script and perform Rule Mining on OracleDV Desktop.

Steps to deploy:

1) Install Advanced Analytics feature in Oracle DV by clicking on the below icon. This will install Oracle R deployment. Alternatively you can install Advanced Analytics by running install_advanced_analytics.cmd present in <DV_INSTALL_DIRECTORY>

2) If not installed arules R-Package already, please install it using following instructions
    Open R console(double click Rgui.exe present in <Advanced_Analytics_Install_Dir>\bin\x64) and
    install arules Package.
    Following are the R commands to install:
     Set Proxy:
        $ Sys.setenv(http_proxy="<your_proxy_host>:<port_number>")
           set proxy appropriate to your network config.
     Install Package:
        $ install.packages("arules")

3) Download Association_Rule_Mining_V1.zip from OracleBI Public Store and unzip it.
4) Copy R.RuleMining.xml to <DV_INSTALL_DIRECTORY>\OracleBI1\bifoundation\advanced_analytics\script_repository
5) Import the .dva project to Oracle DV. Password for the .dva is Admin123

Here is a snapshot:

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!

 

Advanced Analytics: Missing Data? Fret no more, fill in the Missing Data on Oracle DV using R

More often than not, we find data missing in our data sources. And how do we deal with it? we either remove that data or fill it manually. But these methods have a serious implication of skewing your data and giving you wrong insights leading to flawed analysis. No more of this manual guessing or hunch based substitutions or elimination of missing data on OracleDV. You can impute(fill) the missing data in your analysis using Predictive Analytics Algorithms using this Custom R-Script. This R-Scripts uses imputation algorithms present in R data imputation packages like MICE and Hmisc.

And it is quite easy to deploy this data imputation R-Scripts in your local machine. This technique can be used in Dataflows quite easily. The R-script returns the imputed data in a tabular format which can be saved as datasource in DV or can be exported/downloaded to an excel sheet and this excel sheet can be used in Dataflows. Here are the steps to install deploy this R Script in your OracleDV:

1) Install Advanced Analytics feature in Oracle DV by clicking on the below icon. This will install Oracle R deployment. Alternatively you can install Advanced Analytics by running install_advanced_analytics.cmd present in <DV_INSTALL_DIRECTORY>

2) If not installed Mice, Hmisc R-Packages already, please install them using following instructions
    Open R console(double click Rgui.exe present in <Advanced_Analytics_Install_Dir>\bin\x64) and
    install MICE,Hmisc Packages.
    Following are the R commands to install:
     Set Proxy:
        $ Sys.setenv(http_proxy="http://<your_proxy_host>:<port>")
           set proxy appropriate to your network settings.
     Install Package:
        $ install.packages("mice")
        $ install.packages("Hmisc")
3) Download Data_Imputation_V1.zip from OracleBI Public Store and unzip it.
4) Copy R.ImputeValues.xml to <DV_INSTALL_DIRECTORY>\OracleBI1\bifoundation\advanced_analytics\script_repository
5) Import the .dva project to Oracle DV. Password for the .dva is Admin123

Here is a snapshot:

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!

 

Advanced Analytics: Perform Sentiment Analytics on DV using Custom R Scripts

In this blog we will discuss how to perform Sentiment Analysis on Oracle DV on textual data like, Product reviews, customer feedback and social media posts etc. It is well known that OracleDV supports R-Integration and allows users to run their Custom R-scripts. This integration is quite versatile and powerful because Oracle DV allows users to fetch results from R-Scripts in a tabular format and mash it up with data sources. In this example Sentiment Analysis is implemented using a custom R-Script which returns the tonality of the textual data. This example can be downloaded from Oracle BI Public Store.

The R-Script takes textual data as input and categorizes input into 6 categories based on tonality of the data: Very Positive, Positive, Neutral, Negative, Very Negative and Sarcasm. This tonality information can be mashed up with your source data to gain further insights. In Dataflows, you can enrich the data with sentiment information returned by the R-script. Results returned by the R-script can also be downloaded/exported to excel sheets, which can then be used in Dataflows.

Following are the steps to deploy this example in OracleDV desktop:

1) Install Advanced Analytics feature in Oracle DV by clicking on the below icon. This will install Oracle R deployment. Alternatively you can install Advanced Analytics by running install_advanced_analytics.cmd present in <DV_INSTALL_DIRECTORY>

2) If not installed RSentiment Package already, please install it using following instructions
    Open R console(double click Rgui.exe present in <Advanced_Analytics_Install_Dir>\bin\x64),
    install arules Package. Following are the R-commands to install:
     Set Proxy:
        $ Sys.setenv(http_proxy="<your_proxy_host>:<port_number>")
           set proxy appropriate to your network config.
     Install Package(updated instructions):
        $ install.packages("http://cran.r-project.org/src/contrib/Archive/RSentiment/RSentiment_1.0.4.tar.gz",repos=NULL, type="source")
3) Download Sentiment_Analysis_V1.zip from OracleBI Public Store and unzip it.
4) Copy R.Sentiment.xml to <DV_INSTALL_DIRECTORY>\OracleBI1\bifoundation\advanced_analytics\script_repository
5) Import the .dva project to Oracle DV. Password for the .dva is Admin123

Here is a snapshot:

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!

 

Advanced Analytics: R Term Frequency Analysis on OracleDV

In this blog we will discuss how to perform Term Frequency Analysis on Oracle DV using R. What is Term Frequency Analysis(TFA)? TFA is a technique which takes Textual data as input and counts how many times each word is repeated in the textual data. Will it count frequency of each and every distinct word in the text? Yes, it does. But it also provides users option to filter out common words, which do not actually add any meaning like (and, like etc), these words are called stop words. TFA can filter out these stop words and make your analysis more meaningful. TFA has many applications and most common among them are: to analyse the quality of web pages, to identify key highlights in online reviews/posts and to identify popularity of a particular brand or product in social media posts.

It is well known that OracleDV supports R-Integration and allows users to run Custom R-scripts on Oracle DV. Term Frequency Analysis is implemented using Custom R-Script. And it is quite easy to deploy Term Frequency Analysis R Cartridge on your DV. Just download from OracleBI Public store, deploy it and get going with your analysis. You can also use the R-script in Dataflows and perform analysis on your textual data as part of the flow. The R-script generates output in a tabular format containing the words used and associated frequency. This tabular output can either be saved as dataset or exported/downloaded to excel and can be used as part of Dataflow.

Below are the steps to deploy:

1) Install Advanced Analytics feature in Oracle DV by clicking on the below icon. This will install Oracle R deployment. Alternatively you can install Advanced Analytics by running install_advanced_analytics.cmd present in <DV_INSTALL_DIRECTORY>

2) If not installed "tm" R-Package already, Please install it using following instructions:
     Open R console(double click Rgui.exe present in <Advanced_Analytics_Install_Dir>\bin\x64),
     install arules Package.
     Following are the R-commands to install:
     Set Proxy:
        $ Sys.setenv(http_proxy="<your_proxy_host>:<port_number>")
           set proxy appropriate to your network config.
     Install Package:
        $ install.packages("tm")
3) Download Term_Frequency_Analysis_V1.zip from from OracleBI Public store and unzip it.
4) Copy R.TermFrequency.xml to <DV_INSTALL_DIRECTORY>\OracleBI1\bifoundation\advanced_analytics\script_repository
5) Import the .dva project to Oracle DV. Password for the .dva is Admin123

Here is a snapshot:

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!

 

Daum Map Plugin is now available on Oracle BI Public Store

Daum Map plug-in for Oracle DV is now available for download on Oracle BI Public Store. Daum map provider has detailed maps exclusively for Korean geographic region. For other regions map may not render at all.

This plugin-in viz  automatically groups and renders densely packed point features based on proximity (clustering feature). Points clustered within a specified distance (in pixels) are grouped and displayed using a circle marker with a count. This plugin also displays details (metric value, label name) on hovering on the points.

Here are few screenshots of map rendered using this plug-in:

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!

 

Cluster Map Plugin is now available on Oracle BI Public Store

Cluster Map plugin for OracleDV displays lat-long locations on a map (along with its associated metrics and attributes). This plugin automatically groups and renders densely packed point features based on proximity. Points that are clustered within a specified distance (in pixels) are grouped and displayed using a circle marker with a count. Icing on the cake is that you have a host of background map choices (see list below). With Cluster Map plugin, now users can get a good bird’s-eye view of the entire map without making the map look cluttered. Download it from Oracle BI Public Store.

Here is the list of features of Cluster Map plugin:

       - Auto Clustering of Points

       - Choice of background maps

           - Open Street Map

           - Google Map (Satellite, Road, etc)

           - Oracle Map

           - Carto Positron Map

           - Carto Dark Map

           - Mapbox (requires map access key)

      - Map Wrap Around or Repeat Background

      - Auto-zoom to displayed theme

Here is a screen shot of how the Cluster map looks like:

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!

 

Custom Points Map Plugin gets a boost in functionality

Custom Points Maps plugin allowed you to display your location data in multiple interesting ways on top of map backgrounds like Google Maps, Oracle Maps and Open Street Maps.

There are exciting new improvements to this plugin, for example with the new version more Background Map options are available. In addition to the existing Background Maps now you can use Mapbox Light, Carto Positron, Carto Dark background maps. It can now display attribute labels as well. Download it from Oracle BI Public Store.

Here is the list of all capabilities of the plugin. New capabilities added in this update are marked with *

- Choice of background maps
    - Open Street Map
    - Google Map (Road, Satellite, Shaded, Hybrid)
    - Oracle Map
    - *Carto Positron
    - *Carto Dark
    - *Mapbox (requires map access key)
- Image Markers
    - Using local icons
    - Using base64 encoded icon image
    - Using a web URL
- Map Wrap Around or Repeat Background
- Auto zoom to displayed theme
- Feature Animation (Pulse)
- *Labels and Metrics support in Info window
- *Few other styling updates

Here is a snapshot depicting the new capabilities of the plugin:

Here is the video: ** Please note that this video is recorded using previous version of the plugin, so not all the new features described above are demonstrated in this video.

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!

 

Custom plugin for Pictocharts in Oracle Data Visualization

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. 

           

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!

     

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!

 

Oracle DV - New filtering options in Oracle DVD 12.2.2

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 :

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!

 

Upgrade OBIEE in your SampleApp V607 to 12.2.1.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. 

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!