A Guide to Embedding Oracle Analytics into Oracle APEX

Published in

Oracle Developers

16 min readOct 28, 2022

Oracle APEX, is a low-code development environment that makes it easy to create your own applications. At the recent Oracle Cloud World event (‘22), how APEX was used to rapidly create applications to help with the global pandemic was highlighted in one of the keynotes.

If you’ve landed on this page, you probably already know about APEX and you may be wondering why you’d want to embed Oracle Analytics Cloud (OAC) into APEX. In fact, at a conference in 2019, I was asked that exact question, as APEX already has a rich set of reporting and visualization capabilities with a lot of those visualizations based on Oracle JET (in both products).

My response? APEX and OAC are complimentary products with some overlap in the types of visualisations available but also offer the other product additional capabilities — resulting in a more comprehensive user experience for both data engineers and business end users.

Here are some examples I’ll describe in detail later:

Addition of OAC natural language generation or tag clouds to an APEX application.
Ability to use APEX faceted search to define filters for embedded OAC canvases.
The self-service data preparation, enrichment and ML integration in OAC can enhance the analytic capabilities of an application built in APEX.

I could list more, but overall, I think the use of these two products together is worth your consideration and this blog will provide details on how to get the best out of this combination.

Why the product version is important

I mentioned Oracle JET in the introduction to this blog. This is an important aspect of integrating APEX and OAC. Here is how you can find out what versions of JET each product is using.

For APEX, this is usually described in the product documentation. The versions I’ve been using to create this blog have been 21.2 (JET 11) and 22.1 (JET 12). The APEX documentation also describes how JET is used, with version 22.1 listing ‘chart’ region types as well ‘text field with autocomplete’ item types. This will impact on the method used to embed OAC content into APEX, since mixing different versions of JET on the same application page will potentially result in unpredictable outcomes.

In order to find out the version of JET that is being used by Oracle Analytics, while logged in to Analytics, open the browser developer tools console and execute the following:

requirejs('ojs/ojcore').version

I’ve been using the Sept ’22 and Nov ’22 versions of Oracle Analytics Cloud while writing this blog and the version of JET used is 11.1.0.

Update — the January 2024 release of OAC uses JET 15.0.2.

Oracle Analytics embedding scripts

After you have enabled the ‘Developer’ options in OAC (you set this in the Profile | Advanced menu options), you can view the developer area in OAC that includes the ‘Embed’ section.

Oracle Analytics Developer Embed Section

The link for embedding.js, offers two options, /standalone/ which is used when embedding into an application that doesn’t use Oracle JET or /jet/ which is used when the application does use Oracle JET. As we’ve seen from the APEX documentation, some aspects of APEX use JET and some don’t. Later we’ll see how this is a consideration when developing your APEX application and how you choose to embed the OAC canvases.

Authentication

APEX uses authentication schemes to secure applications with a default of Oracle APEX Accounts. Oracle Analytics Cloud, uses Oracle Identity Cloud Service (IDCS) authentication.

At this stage, you could either choose for your end users to log in to the APEX application, and then require them to also log in to embedded analytics content (that may be desired if you’d like to limit access to embedded analytics, within the APEX application) or, it’s more likely that you’ll want a seamless authentication experience where a user who logs into the APEX application will also be authenticated to the analytics content. This is achieved by creating ‘Web Credentials’ and an ‘Authentication Scheme’ within APEX that use the same Oracle IDCS as analytics. Here are some links (1)(2)(3) to articles that describe how to configure APEX for IDCS authentication.

If you still see a login prompt in the region where you have embedded analytics, and you are using the <oracle-dv> HTML embedding method, then you may need to add the following to the link to embedding.js:

<script src=""https://<instance>.analytics.ocp.oraclecloud.com/public/dv/v1/embedding/standalone/embedding.js?IDCS_OAUTH3LEGGED=true" type="application/javascript">
</script>

Embed a URL link to Oracle Analytics workbook

The easiest way to add Oracle Analytics content to an APEX application is to reference a workbook URL as follows:

While in OAC, with the canvas open that you’d like to embed, copy the URL.
In APEX, create a new static content region, select URL for the type, click on ‘Attributes’ then choose ‘iFrame’ for ‘Inclusion Mode’.
Paste the URL you copied from OAC into the ‘URL’ field and optionally set any iFrame attributes, e.g., the width and height.

The view of the canvas that you see in APEX will depend on a number of factors that can be tailored to your requirements.

If you have configured APEX to use IDCS authentication, and then login to the APEX app as a user with OAC Author application role, you may see the full authoring UI embedded into APEX. It’s possible to change this by adding the following to the embedded OAC URL:

&viewermode=true

If you login to APEX as a user who has OAC viewer application role, then you’ll see the viewer experience.

There are some settings, that have been introduced in the Sept ’22 and Nov ’22 releases of OAC that can tailor this experience further:

In the OAC profile setting, Advanced menu option, the ‘Open Workbooks as Viewer’ toggle. By default this is enabled, so if you do have DV Author role, then you would see the ‘viewer’ mode content in APEX. This provides a better user experience for embedded content.
Whether you have any story pages defined in the ‘Present’ section of the OAC UI.
If you have story pages, then you can change the interactivity settings, context menu actions and whether the header bar is shown or not.

Not showing the header bar and showing the viewer user view provides the most seamless embedding experience.

Passing Parameters on a Workbook URL

The September ’23 release of OAC has further increased the options for embedding a workbook URL, through the use of parameters and the ability to pass parameters on an OAC workbook URL.

This video provides an overview of that new capability:

But how can this be used within APEX? This method is the easiest way to embed analytics content interactively within an APEX application page, and I’ll describe examples of passing from multiselect and also faceted search.

In OAC, I’ve created a workbook that uses data from the APEX NY Schools data set. I have a filter on ‘BOROUGH’ and I have also defined a parameter (called pBOROUGH) on this column. This video provides an overview of using parameters in OAC workbooks:

As an example, here is a Workbook URL that I’ve copied from OAC, making sure that it includes /project.jsp/ in the path. If you don’t see /project.jsp/ in the path, then open the workbook from the catalog using the menu option to open in a new tab and you should then see it.

https://<OAC-INSTANCE>.analytics.ocp.oraclecloud.com/ui/dv/ui/project.jsp?pageid=visualAnalyzer&reportpath=%2F%40Catalog%2Fshared%2FEmbed%2FAPEX%2FAPEX%20NYC%20Schools%20Event%20Specific%20Borough

If you see ‘&reportmode=full’ in the URL then remove it for a better user experience in APEX.

Add the following to the URL: ‘&viewermode=true&noheader=true’

In order to pass the parameter to the workbook, we need to add values for the ‘&p1n’ which refers to the parameter name (as defined in the OAC workbook parameters section) and the ‘&p1v’ which refers to the parameter value. However, when we use this in APEX, and wish to interactively pass values to the embedded workbook, we will be obtaining the value of Bourough from the APEX UI selectors.

This is what our URL looks like before we can start using in APEX:

https://<OAC-INSTANCE>.analytics.ocp.oraclecloud.com/ui/dv/ui/project.jsp?pageid=visualAnalyzer&reportpath=%2F%40Catalog%2Fshared%2FEmbed%2FAPEX%2FAPEX%20NYC%20Schools%20Event%20Specific%20Borough&viewermode=true&noheader=true&p1n=pBOROUGH&p1v=

APEX Multiselect

In the case of an APEX multiselect we can first define a static content region with the following HTML code:

Where is the ‘src’ you may be wondering? We add the following to the ‘Execute when page Loads’ section to obtain the selected values of bourough and then pass those values to the workbook URL and then the iFrame referenced by getElementById:

vBoroughName = apex.item(“P15_SELECT_BOROUGH”).getValue();

document.getElementById(“OACembed”).src = “https://<OAC-INSTANCE>.analytics.ocp.oraclecloud.com/ui/dv/ui/project.jsp?pageid=visualAnalyzer&reportpath=%2F%40Catalog%2Fshared%2FEmbed%2FAPEX%2FAPEX%20NYC%20Schools%20Event%20Specific%20Borough&viewermode=true&noheader=true&p1n=pBOROUGH&p1v=” + vBoroughName;

APEX Faceted Search

In this case, the static region is defined in the same way as the multiselect example.

But the way we get the src is different. In this case, we’re going to execute JavaScript using a dynamic action when the facet we’re using as a parameter changes, i.e. in this case Borough. We’re doing a replace on the vBouroughName returned from the faceted selection to ensure we have comma “%20” separated values. You may need to experiment with this aspect depending on the type of data you are working with.

vBoroughName = apex.item(“P16_BOROUGH”).getValue();

pBoroughName = vBoroughName.replace(/:/g,”%2C”);

pBoroughName = pBoroughName.replace(/ /g,”%20");

document.getElementById(“OACembedFacet”).src = “https://<OAC-INSTANCE>.analytics.ocp.oraclecloud.com/ui/dv/ui/project.jsp?pageid=visualAnalyzer&reportpath=%2F%40Catalog%2Fshared%2FEmbed%2FAPEX%2FAPEX%20NYC%20Schools%20Event%20Specific%20Borough&viewermode=true&noheader=true&p1n=pBOROUGH&p1v=” + pBoroughName;

The introduction of parameters and the ability to pass parameters on the OAC workbook URL provides the easiest way to interactively embed analytics content into APEX. If you’re after more control over the embedded analytics, such as the ability to define filters, then the next section on using the JavaScript embedding framework will cover that.

Using Oracle Analytics JavaScript embedding

Using the Oracle Analytics JavaScript embedding framework can enable a more integrated user experience for analytics content in an APEX application. This is where the version of Oracle JET being used by APEX and OAC can be important, in addition, whether you have any APEX components that use JET (e.g., APEX charts) on the application page can potentially result in unexpected behaviour (e.g, some OAC visualisations may not fully render or there may be CSS clashes, especially if using /standalone/ path).

OAC and APEX on same JET version

In this scenario, set the following at the APEX page level ‘JavaScript’ field, note the [require jet] in addition to using the /jet/ path to embedding.js:

Note that the usual requirejs dependencies and knockout apply bindings may not be required for you to see the embedded analytics.

You will then add the <oracle-dv> tag in an APEX static content region, in the ‘Source’ field:

If you use the /standalone/ path to embedding.js, then you don’t need to use the [require jet] in the JavaScript section.

OAC and APEX on different JET versions

If different JET versions are present, it then depends on what other APEX items are on the same application page as the one you wish to embed OAC content. If the APEX items on the application page don’t use JET, then you can use the /jet/ path, but it still may be the case that not all OAC visualisation types will render.

In general, the most reliable way to embed analytics given the possibility of different JET versions being present is to use an iFrame. I’ve written about this method in this blog, including how to pass parameters into the iFrame using windows messaging from APEX UI controls such as selectors and faceted searches which is where the real benefits of using OAC and APEX together can be realised.

Data sources

Ideally, both OAC and APEX should use the same data source. It’s easy to get data into both OAC and APEX, either using XLS files that are uploaded into a DB table, in the case of APEX or by creating data sources in OAC either from uploaded XLS or by creating connections to a DB and then creating a suitable data source.

Using the same data source will most likely require a connection from OAC to the schema that APEX uses to store application data. For my examples, I’ll be using an APEX application built using data on NYC Schools from this live lab.

I will assume that the data has been uploaded according to the live lab and the APEX application has been created. You will also need to have a connection and datasource created in OAC to the schema used by APEX for the application. You’ll be able to discover this using the SQL Workshop in APEX. If you are not able to get the connection wallet for the DB used by APEX, then you could upload the NYC Schools data directly to OAC.

The key requirement is that both OAC and APEX are able to access the same data set, whether that is stored in the same place or not.

Creating user experiences that add value

In this section, I’ll describe how the combination of OAC and APEX can add value to a users experience. I’ve defined ‘value’ as OAC doing something that might not be available out of the box with APEX (e.g. adding natural language narratives) and vice versa (e.g., using APEX faceted searches with embedded analytics).

Add OAC TAG cloud and NLG to an APEX app

This animation illustrates the user experience we’re going to build, both using an iFrame and without an iFrame.

As a pre-requisite, I’ll assume that you’ve created a datasource in OAC that is using the same data as the APEX application and that you’ve created a workbook in OAC that shows a Tag Cloud and Natural Language Generation (NLG) visualisations. I’ve copied the application page ‘NYC Highschools Report’ from the live lab APEX app and we will add the OAC tag cloud and NLG visualisation to that page.

Embedding without using an iFrame

Add a select list item to the application page. This will get a list of values from a SQL statement and the selection will be stored in P7_SELECT_BOROUGH (you may have a different item name) :

select distinct BOROUGH d, BOROUGH r
from NYC_HIGHSCHOOLS
order by 1

In the settings for the select list, for ‘Page Action on Selection’ choose ‘Submit Page’.
Add a static region to the application page to host the embedded OAC content:

Use the following HTML code within this static region, note that the active-tab-id number has been obtained from the OAC developer | embed menu in my workbook that’s being used for the embed. The items in {{ }} are known as observables and will be populated using code that will be defined at the application page level:

<div style='width:1024px; height:800px' ><oracle-dv id='Filter'
             project-path='{{projectPath}}'
             active-page='canvas'
             active-tab-id='2'
             project-options='{"bDisableMobileLayout":true, "bShowFilterBar":false}'
             filters='{{filters}}'>
          </oracle-dv>

At the application page level, add code similar to this (appropriate for your instance and the OAC user who owns the data set (in the path for ‘sColFormula’):

In the File URLs, is the link (/jet/) to embedding.js for the OAC instance, and the parameter [require jet]. This example is using the three-legged auth parameter but please refer to earlier in this blog about configuring your APEX instance to use IDCS authentication:

[require jet]
https://<OAC-instance>-ia.analytics.ocp.oraclecloud.com/public/dv/v1/embedding/jet/embedding.js?IDCS_OAUTH3LEGGED=true

In the ‘Function and Global Variable Declaration’ section, there is code to assign the value of the P7_SELECT_BOROUGH defined earlier to a JavaScript variable:

let vBoroughName = apex.item("P7_SELECT_BOROUGH").getValue();

In the ‘Execute When Page Loads’ section, the observables are defined for the <oracle-dv> tag as well as the filter JSON used by the embedded OAC canvas. The ‘sColFormula’ can be obtained from the OAC workbook developer | JSON menu option:

requirejs(['jquery', 'knockout', 'obitech-application/application', 'ojs/ojcore', 'ojs/ojknockout', 'ojs/ojcomposite',  'jet-composites/oracle-dv/loader'],
function($, ko, application) {function model() {
                        var self = this;
                        self.projectPath = ko.observable('/@Catalog/shared/Embed/APEX/APEX NYC Schools');
                        self.filters = ko.observableArray([{
                          'sColFormula': "XSA('<data-set-owner>'.'APEX NYC HIGHSCHOOLS').\"NYC_HIGHSCHOOLS\".\"BOROUGH\"",
                          'sColName': 'BOROUGH',
                          'sOperator': 'in',
                          'isNumericCol': false,
                          'isDateCol': false,
                          'bIsDoubleColumn': false,
                          'aCodeValues': [],
                          'aDisplayValues': [vBoroughName]}]);
                     }
 application.setSecurityConfig("oauth_3legged");
 ko.applyBindings(new model());});

This is executed when the selector for P7_SELECT_BOROUGH is used (due to the ‘Page Submit’ option), obtaining the value into the variable vBoroughName and then passing that into the filter JSON for the embedded OAC canvas. The ‘aDisplayValues’ is expecting an array, so the value passed (vBoroughName) needs to be enclosed in [ ] brackets.

At this point, run the application and test that changing selector values changes the borough shown in the APEX table report and embedded OAC content.

Embedding using an iFrame

If you have different versions of JET in use (as described earlier) then you most likely need to use an iFrame to embed OAC canvases into an APEX application. The following illustrates this type of deployment for an application page:

The key difference with using an iFrame is that all the OAC embedding code i.e., the link /standalone/ to embedding.js, the <oracle-dv> tag, the requirejs dependencies and knockout applybindings are all contained within the iFrame. The iFrame then acts as a re-usable block that can be embedded in multiple application pages, with minor changes if needed as described later in this blog. The mechanism to pass filter values into the iFrame is contentWindow.postMessage.

The APEX static region that includes the OAC embedded content includes HTML code that defines the iFrame using the srcdoc parameter:

<iframe  id='OACiFrameEmbed' width="1024" height="800" srcdoc="<!DOCTYPE HTML><html dir='ltr'><head><meta http-equiv='Content-Type' content='text/html; charset=utf-8'><script src='https://<OAC-INSTANCE>-ia.analytics.ocp.oraclecloud.com/public/dv/v1/embedding/standalone/embedding.js' type='application/javascript'></script></head><body><div style='width:1024px; height:800px' ><oracle-dv id='Filter'project-path='<PATH-TO-EMBEDDED-WORKBOOK'active-page='canvas'active-tab-id='1'project-options='{&quot;bDisableMobileLayout&quot;:true, &quot;bShowFilterBar&quot;:false}'filters='{{filters}}'></oracle-dv></div><script>requirejs(['jquery','knockout','ojs/ojcore', 'ojs/ojknockout', 'ojs/ojcomposite', 'obitech-application/application', 'jet-composites/oracle-dv/loader'],function($, ko, application) {function model() {var self = this;self.filters = ko.observableArray([{'sColFormula': &quot;XSA('<OAC-USER-ID>'.'APEX NYC HIGHSCHOOLS').\&quot;NYC_HIGHSCHOOLS\&quot;.\&quot;BOROUGH\&quot;&quot;,'sColName': 'BOROUGH','sOperator': 'in','isNumericCol': false,'isDateCol': false,'bIsDoubleColumn': false,'aCodeValues': [],'aDisplayValues': ['Select Borough']}]);window.addEventListener('message', (event) => {if ((typeof event.data === 'string' || event.data instanceof String) && event.origin == 'https://<APEX-DOMAIN>') {self.filters([{'sColFormula': &quot;XSA('<OAC-USER-ID>'.'APEX NYC HIGHSCHOOLS').\&quot;NYC_HIGHSCHOOLS\&quot;.\&quot;BOROUGH\&quot;&quot;,'sColName': 'BOROUGH','sOperator': 'in','isNumericCol': false,'isDateCol': false,'bIsDoubleColumn': false,'aCodeValues': [],'aDisplayValues': [event.data]}]);}}, false);}ko.applyBindings(new model);});</script></body></html>"></iframe>

Since the srcdoc parameter value is enclosed in double quotes, then any double quotes used within the srcdoc code need to be escaped to "

This is required for the project-options and sColFormula. Also, the domain that is passing the message into the iFrame needs to be specified at the point the message is passed in addition to the listener in the iFrame. For these examples, that domain is the APEX instance.

The APEX selector, has ‘Submit Page’ as the ‘Page Action on Selection’. In order to pass the selected value into the iFrame, set code such as the following for the ‘Execute when Page Loads’ page level property. The setTimeout is to avoid any race conditions that may occur:

vBoroughName = apex.item("P9_SELECT_BOROUGH").getValue();
setTimeout(() => { document.getElementById(‘OACiFrameEmbed’).contentWindow.postMessage(vBoroughName, ‘https://<APEX-INSTANCE>.adb.us-ashburn-1.oraclecloudapps.com’); }, 2500);

Changes needed to enable a multi-select

If you want to enable users to select more than one borough, here are the changes you need to make.

Modify the selector, P9_SELECT_BOROUGH so that the ‘Page Action on Selection’ is ‘None’.

Add a button to the page, I’ve placed it below the selector item itself on my application page.

Give this button a name / label of ‘Apply’.
Set the ‘Behavior’ to ‘Submit Page’.

One key difference when dealing with multiple selections is that it is now an array object that is being passed to the embedded Oracle Analytics canvas. As such, there are some changes needed to the code in the iFrame, compared to the single select use case.

Since an array of selected values is now being passed to the iFrame instead of a single value. This results in a change to the ‘if’ statement to check for the event.data being an array.
The [ ] are no longer required for the value passed to ‘aDisplayValues’ (compared to the single select) since event.data is an array.

if ((Array.isArray(event.data)) && event.origin == 'https://<APEX-INSTANCE>.adb.us-ashburn-1.oraclecloudapps.com') {self.filters([{'sColFormula': &quot;XSA('<USER>'.'APEX NYC HIGHSCHOOLS').\&quot;NYC_HIGHSCHOOLS\&quot;.\&quot;BOROUGH\&quot;&quot;,'sColName': 'BOROUGH','sOperator': 'in','isNumericCol': false,'isDateCol': false,'bIsDoubleColumn': false,'aCodeValues': [],'aDisplayValues': event.data}]);}}, false);

Using APEX faceted search to filter embedded analytics

Adding embedded analytics (in an iFrame) to an APEX page that contains a faceted search is very similar to the previous embedding scenarios.

Add a static region to the application page.
The iFrame code used for the static region is the same as for the multi selection use case, since a faceted search will also pass an array.
Change the ‘Attributes’ of the search item to ‘Batch Facet Changes’ to ‘On’. This will provide the user with an ‘Apply’ button when making facet selections.
Add a ‘Dynamic Action’ to the ‘Search’ item on the APEX page and set the Event to ‘Facets Change [Faceted Search]’.
For the ‘True’ case, set the ‘Action’ to ‘Execute JavaScript Code’ and use this sample, modified for your own use case. In this sample, the P8_BOROUGH is obtained from the faceted search, and since the output has the form <ITEM>:<ITEM>:<ITEM> etc., there is a line of code to split the output into an array. This array is then passed to the iFrame using ‘postMessage’.

vBoroughName = apex.item("P8_BOROUGH").getValue();aBoroughArray = vBoroughName.split(":");setTimeout(() => { document.getElementById('OACiFrameEmbed').contentWindow.postMessage(aBoroughArray, 'https://<APEX-INSTANCE>.adb.us-ashburn-1.oraclecloudapps.com'); }, 2000);

Summary

Using Oracle Analytics (OAC) with APEX can add value to your applications. In this blog I’ve described the importance of the JET version as well as provided links to descriptions of how to configure APEX to use the same Oracle Identity Cloud Service as OAC to provide a seamless experience.

Using iFrames (in particular the ‘srcdoc’ parameter) to embed the analytics content, offers a way to deal with the possibility of different JET versions being present, by sandboxing the analytic content.

It is still possible to pass parameters to the embedded iFrame using the ‘postMessage’ method. The general approach is that the iFrame contains the self-contained analytics embedding code — links to embedding framework, HTML for the <oracle-dv> embed tag, requirejs dependency management and observable definitions.

The definition of the filters applied to the embedded content is defined as an observable array and differences are required depending on if the use case is a single select (add [ ] brackets around the event.data) or multi-select / faceted search (don’t require [ ] brackets as event.data is an array).

The other key aspect of this type of embedding is when the parameter values are chosen by the end user there needs to be a way to send these values to the iFrame by either using the APEX page level ‘Execute when Page Loads’ parameter or by using a Dynamic Action that is invoked when the faceted search values change.

Acknowledgements

Thanks for reading, I hope this blog has been useful and provided some ideas of how you can embed Oracle Analytics in your own projects. I’d like to acknowledge Arun, Matt, Lior, Jackie and Roel for their help and assistance with APEX.

If you’re curious about the goings-on of Oracle Developers in their natural habitat, come join us on our public Slack channel! We don’t mind being your fish bowl 🐠