Oracle Underground BI & Dataviz

Monday, August 10, 2020

Embedding Oracle Analytics Dynamically - Part I

Oracle Analytics visualizations can easily be embedded into external websites, custom portal, applicative pages.... We will start exploring this capability in this blog entry. We will be creating our own custom website and we include Oracle Analytics content as a part of it. We will also explore the various steps involved to do this embedding of Analytics content. Let us start with a bare minimum HTML code for our website displaying the name and a few texts.

To understand the embedding context from a technical perspective, the functionality is supported by an underlying Javascript file that needs to be hosted on your destination website. Depending on the technology used in the client web portal, one of the following embedding modes is used:

Our objective is to leverage a public OAC instance and to embed its Sample Project visualizations into our own website. The first step for us will be to include the javascript file providing the embedding functionality inside the <head> tag of our website. In this case since our website is a simple HTML file, the embedding mode will be standalone mode.

We can find reference to this script as well as it's address directly from within our own Analytics canvas, by clicking on the top right hamburger menu, selecting option developer and once there, clicking the Embed tab

This tab will show us all the urls we need to use in our own website html to embed our content

So we could simply copy the urls for the various canvas provided above in the embed tab of the developer menu, and simply paste them in our webpage html as is.

But we will go into a bit more details in our example. Embedding Oracle Analytics content is done through HTML API called <oracle-dv> tag. We need to include this tag as a part of our HTML code to enable embedding. Before we delve into the details of the code, following are the list of attributes that are supported for <oracle-dv> HTML tag.

project-path: Specify project in repository to render.
active-page: Optional and options to choose are Canvas or Narrate
active-tab-id: Optional. Specify the id of the canvas.
filters: Optional.
project-options: Optional. Below are the options you can pass:

bShowFilterBar: Values should be true or false;Shows or hides filter bar.
bDisableMobileLayout: Values should be true or false; Disables or enables mobile layout.

We will be using project-path attribute to embed the Analytics content into our website.

Now coming back to the code, the next step is include <oracle-dv> tag and set the project-path attribute to the path of the project that we want to embed. Now our HTML code for the website should look something like this.

Also included towards the bottom of the HTML code are the Knockout bindings. We are using the knockout model's declarative bindings to assign the value to the project-path attribute. Knockout bindings are generally executed either in a body onload handler or in a <script> tag after the <oracle-dv> tag. In this case we have used a <script> tag after the <oracle-dv> tag.

We are now one step away from including Oracle Analytics content into our website. The final step is to include the domain name of our website into the list of safe domains in the OAC instance from which we want to embed an Analytics project.

Since we are hosting the website on a localserver, we have added an entry for 127.0.0.1 along with the port number. If we want to do embedding on a public web server, add the domain name/ ip address of the web server to the list of safe domains. A refresh of the OAC instance is needed for the safe domains to kick in and now our website should look something like this

The rest of this blog will now be focused on how to pass filters from our website to the embedded Analytics contents.

As mentioned before, <oracle-dv> supports filter attributes. In order to pass filters to the embedded content, we need to construct filter object with the following attributes.

sColFormula: Specify the three-part column formula of the column to be used as a filter. This MUST BE three parts. Create a project within DV using the column, then go to the Developer menu item and look up the column formula to get the three part forumula.
sColName: Specify a unique name for this column. This must be non-empty.
sOperator: One of in, notIn, between, lessOrEqual, greaterOrEqual. in and notIn apply to List filters. between, lessOrEqual, greaterOrEqual apply to numeric filters
isNumericCol: Whether or not this is a numeric filter or a list filter. Values should be true or false.
isDateCol : Flag indicating whether date column - values should be true or false and is a mandatory attribute - Note: This is set to true only for date and not for year, month,quarter etc. If this flag is set, fill aDisplayValues attribute with the date/s.
bIsDoubleColumn: Whether or not this column has double column values behind the display values. Values should be true or false.
aCodeValues: When bIsDoubleColumn is true, this array will be used.
aDisplayValues: When bIsDoubleColumn is false, this array will be used for filtering and for displaying values within the UI.

We will be passing filter values on the Order Priority Column from our website to the embedded content. The HTML code should look like the one shown below.

We are passing the value "Critical" on Order Priority column to act as a filter to the embedded DV content and our website will render the DV content with the context of the filter passed through our HTML code.

In the above pic, we can see the filter on the Order Priority column with the value "Critical" and all the visualizations in the DV canvas has re-rendered based on the filter. Similarly you can create an array of filter objects to pass filters on multiple columns.

To make our website user interface more intuitive, a drop down with various values of Order Priority column has been created. On the selection of a particular value from the drop down, appropriate filters are passed on the Order Priority column to the embedded content.

In the next blog, we will focusing on how to use data actions to pass values from the embedded DV content to our website. Thanks for reading this blog !

Monday, July 20, 2020

Custom Scripts in Oracle Analytics Desktop - Geocoding

Oracle Analytics is a comprehensive unified platform offering services including self-service visualizations, powerful data-prep tool, advanced analytics and augmented analytics.

In this blog, we are going to explain the use of leveraging custom-scripts using Oracle Analytics Desktop (OAD) and few tips and tricks to install additional custom Python packages that do not come by default with the Python engine.

Let us take the following scenario : a user has a dataset with an address column and needs to plot these address on a map visualization. In technical terms, he needs a geo-coding capability which converts valid addresses to corresponding Lat & Longs which can then be used for plotting on a map. .

One way for the user to achieve that would be to leverage Spatial Studio application (free with Oracle Database). Spatial Studio is a self-service web application that makes it easy for users to create interactive maps and perform spatial analysis on their business data. User just needs to upload his dataset into Spatial Studio and the geocoding service can then be invoked to find the corresponding Lat/Longs. Once done, the user can simply visualize his dataset in Oracle Analytics platform.

But the user has another option to achieve the same result : using Custom Scripts in OAD.

Oracle Analytics Desktop comes with in an in-built Python engine. This Python engine is wrapped around an internal layer called DVML which needs to be installed for a user to run custom scripts on OAD.

Search for Install DVML and install the application

As of OAD 5.6, DVML uses Python version 3.5.2. OAD expects a particular format for a script to be accepted and executed by it's Python engine. This should be an .xml file with well-defined inputs, outputs and options tags. We can break down the process of creating a custom Python script on OAD into 2 simple steps

Have the script working on a standalone Python editor
After completing the first step, wrap the script in the form of an XML wrapper by properly defining the input and output tags and also by making sure that the resultant dataframe of the Python script matches with the output structure defined in the XML.

Coming back to our scenario where a user wants to perform geocoding, this functionality can easily achieved in Python through a package called geopy.

A custom python script can simply call the geocode function from this package, using the Address column as argument. If user creates such a custom Python script and uploads it into OAD, he can then simply invoke it from an Oracle Analytics Data Flow by using the "Apply Custom Script" node. This will let the OAD's Python engine to execute the geocoding script. But at this point, when the dataflow is executed the user is likely to see an error...

Checking the logs it becomes clear that geopy package was not found by DVML python engine. This is an indication that the geopy package needs to be installed as a part of DVML. When a user installs DVML, it comes with default set of commonly used Python packages like pandas, numpy, scikit-learn, etc. But how to install new python packages into DVML ?

Installing Python packages into DVML

Here are the steps to be followed to install Python packages into DVML on a MS Windows machine : search for DVML Runtime and choose the option "Open File Location".

This will open the folder which has the shortcut to DVML. To get the actual location as to where the DVML files are installed, right click on the short-cut and choose the options "Open File Location".

If the default path was chosen during the initial DVML installation (MS Windows machine), the DVML files will be under this directory : C:\Program Files\DVMLRuntime\PCbuild\amd64

Open cmd prompt and run it as an administrator. Navigate to the above path and type in the following command : python -m pip install geopy

This should install the geopy package as part of DVML.

If you encounter any issues in the above step you can try this alternative approach : search for DVML Runtime and open it as an administrator. This open up a Python prompt where a user can type in python commands. Type the following commands to install python packages.

from pip._internal import main

main.main(["install","geopy"])

In both approaches, replace geopy with the package you want to install and it should install the package into DVML. Once the user has imported all the relevant packages, these packages can be imported and used in the OAD custom scripts. Restarting the OAD and running the geocoding dataflow should now result in a successful execution.

In this approach of geocoding, the user needs not switch between applications or do multiple upload or download of files to get the resultant output. The user can stay in OAD, run the Python script and get the resultant output.

Anything that you can do in standalone Python editor like a Jupyter notebook is possible in OAD by using Dataflows and custom scripts.

As of today, custom scripts import are only applicable to desktop or On Prem (OAS) versions of Oracle Analytics. The cloud version OAC is being worked on to support similar capability in the near future.

Geocoding Script Details

As evident from the above pic, geopy package was imported into the script along with few other packages. There are many providers who offer the geocoding capability. Some of them include Google, OpenStreetMap, Bing, etc. In order to use Google or Bing service, the user should sign up on their platform and get a valid API key to pass it along the geocode request. OpenStreetMap offers geocoding service called Nominatim which offers free geocoding service without the need for the API key. However it requires a user_agent to be passed. This can be the App id of the application or just an email address.

In this script, I have used the Nominatim geocoding service and have used my email address as the user agent. Also another aspect about the Nominatim geocoding service is that there a limit on the number of requests it can serve at a moment. Hence I have used the ratelimiter package to send only one row per second to the Nominatim server. A place name will be passed to the Nominatim geocoding service which results in the address and the Lat/Long coordinates of the place. The results are then passed into the output dataframe.

Thanks for reading the blog and you can download the geocoding custom script here for your future references.