Users can interactively develop R scripts using the R IDE, and then deploy the script as an R function to the database where it can be invoked either from R or SQL. Embedded R execution facilitates application use of R scripts with better performance and throughput than using a client-side R engine. From the same R script you can get structured data, an XML representation of R objects and images, and even PNG images. Executing R scripts from SQL enables integration of R script results with OBIEE, Oracle BI Publisher, and other SQL-enabled tools for structured data, R objects, and images.
R Script Execution through RPD
Here is how you can integrate an image generated from a R script in to a OBIEE Dashboard.
Integration with OBIEE RPD
• Invoke R calculations from OBIEE
– Define ORE-based SQL query via RPD table definition
– Embedded R script execution
– Reference R scripts from database table repository
• Retrieve results from R
– Structured tabular output
– Graphic PNG stream output
Load the script into the Oracle Database R Script Repository of ORE via any SQL interface
-Run the code below in Toad/ SQL developer (while connected to your database.)
res <- 1:10
plot(1:n, rnorm(n), pch= 21,
bg = “red”, cex = 2)
Preparing the Repository
Step 1: Open the BI admin tool and create a new repository or you can use an existing rpd for this purpose. I have used an existing rpd in offline mode. Here I have used the tennis successful–rorcl rpd for integrating into OBIEE. Here we can change the Data Source Name by double clicking on the connection pool in its properties.
Step 2: Then we create a Session Variable and an initialization block for the for the request purpose for the BI answers for the call to these data that we are about to generate from the script. In the Session Variable Initialization Block Data source we set the default initialization string to select 10 from dual; and the connection pool to the default connection pool that you are using. And the default initializer to 10 in the Session Variable dialog box. Then click on OK for all dialogs.
Step 3: After creating the session variable we then create a new physical table for the image that we want to show in the analysis. You can create the new physical table by right-clicking on the schema from where you want to call the function. For that purpose we want to add the select statement in the default initialization string that is enabled when you select the Table Type as select. In the initialization string you Take the value of the Session Variable and then call the function script that we have created in the database. Then in the columns tab of the Physical Table we create the ID and Image as type INT and LONGVARBINARY respectively and the length of the image being 32000.
After that we move on to the key column and set the key name to key and the columns to the ID. Then we click OK for all the Dialogs.
Step 4: After setting the key to the table we are now done with the physical layer. Now we move in to Business Model and Mapping Layer.
Here in the Business model and mapping layer we will have to drag and drop the whole RandomRedDots Column from the Physical layer to the Logical Layer. In the logical layer we have to first duplicate the table and then we have to join the table with the duplicate to maintain the star schema.
Step 5: After joining the columns now we set the Sort order and and Descriptor ID in the general tab of the original logical column RandomRedDots to ID and click OK on the dialog boxes.
Then in the Logical Table Source(LTS) we change the mapping for the Image in the expression builder of the Column mapping in the LTS. We first double click on the RandomRedDots LTS present inside the sources folder of RandomRedDots table in the Logical Layer. Then we click on the Column mapping tab and then click on the Edit Expression button to set the expression in the image below. Now click OK in all the dialogs.
Step 6: After setting the required fields now our work in the Logical Layer is complete and now we drag and drop the RandomRedDots from the business model and mapping layer to the Presentation layer. Then we check for the consistency in the rpd and save the file.
Step 7: After saving the rpd file we deploy it depending on which version of the admin tool you have created the rpd. I have deployed it in the Enterprise Manager of the OBIEE 11g. After the successful deployment of the rpd we are now ready to see our integrated image in the OBIEE answers.
Step 8: Sign in to your analytics providing your credentials.
After signing in to the OBI analytics click on the administration button present on the top right corner of the start page and then you can see the link to “Reload Files and Metadata” under the Maintenance and Troubleshooting. Doing this will reflect the changes of the rpd in the answers.
Step 9: Now click on the new button on the toolbar and then click analysis and select the subject area where you have added the table in which your image and id columns are present in my case which is the Tennis Tournaments as you have seen in the previous steps. After selecting the subject area now you are redirected to the analysis page where you can drag and drop from the tables the image column and the ID column in to the selected columns pane. And then you can click on the results tab to see the output of your work done so far. The compound layout shows the image that you have extracted from the database in PNG format.
Step 10: You can now save this analysis and then create a new dashboard prompt following the same method as before. Here you create a Variable prompt by clicking on the green plus icon and selecting the variable prompt when asked.
Here in the below dialog box choose the Request Variable in the Prompt for and write the name of the Session Variable that you have created in the rpd in the next box. And then select the choice list values as Custom Values and add your values to it. Then in the Options select the default selection as specific column values and their you can specify a value then click OK.
Step 11: Then we create a dashboard and then add the analysis and prompt for it to show the values that will be changed when we change the prompt values.
First create a new dashboard and select a location then click OK. After the dashboard is created then add the analysis and the prompt to the dashboard page. Click on save then click Run i.e. the green play button on the top right corner of the dashboard.
As you can see we have successfully integrated the R Script PNG image inside the OBIEE answers… !!!