Discover the best race of all time: learn analytics and machine learning with Red Bull on Oct 12, 2021

Discover the best race of all time - Learn Analytics and Machine Learning with Red Bull Racing Honda

Wednesday, October 12 at 9:00 BST / 10:00 CEST

Visit the following link to register:  

https://www.oracle.com/uk/events/red-bull-analytics/

 

 

Embedding Links to Oracle Analytics Cloud (Classic UI) in Oracle E-Business Suite

Thanks to Alex Ofenberg and Gali Gil from TAU and my colleague Tovit Leiba that guided me.  

In Oracle documentation here, we can see how to do "Embedding Links to Oracle Business Intelligence in Oracle E-Business Suite" (9.3 in Oracle® Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition). How to do something similar with Oracle Analytics Cloud (OAC)? It is very much the same, just be aware we are talking mostly about classic OAC UI (unlike the Self Sevice / DV UI).

 

To create a form function:

1. In EBS from the Application menu, select Function. The "Form Functions dialog" is displayed.

Each line in the function form is a link to specific analysis or Dashboard. The Function column is your internal Function name.

2. In the Properties tab of the menu, for the relevant function, select SSWA plsql function as Type

3. In  the "Web host" tab use the beginning of the OAC URL. For example "https://youroac-instance.analytics.ocp.oraclecloud.com"

4. In the "Web HTML" tab use the rest of the URL, starting with analytics.

For example my Dashboard URL is (can create this URL by using Dashboard Menu/Crete Prompted link): 

https://youroac-instance.analytics.ocp.oraclecloud.com/ui/analytics/saw.dll?Dashboard&PortalPath=%2Fshared%2FAll%2F_portal%2Fcsr&Page=page%201&Action=Navigate 

 That is converted to this (%2F = / and %20 = space)

https://youroac-instance.analytics.ocp.oraclecloud.com/ui/analytics/saw.dll?Dashboard&PortalPath=/shared/All/_portal/csr&Page=page%201&Action=Navigate

I use the first part of the URL as Web Host, skip the "/ui/" part and use the rest in Web HTML (HTML call).

In this case: analytics/saw.dll?Dashboard&PortalPath=/shared/All/_portal/csr&Page=page%201&Action=Navigate

 

That is all, except the following: You will probably want to modify the URL so it doesn't bring the OAC menu etc...

2 options:

1. The recommended one is to use PortalPages instead of Dashboard, in our case: 

analytics/saw.dll?PortalPages&PortalPath=/shared/All/_portal/csr&Page=page%201&Action=Navigate

 

 2. The older one of adding syndicate=Siebel to the URL (this is a bit more violent method)

analytics/saw.dll?Dashboard&PortalPath=/shared/All/_portal/csr&Page=page%201&syndicate=Siebel&Action=Navigate

One more thing people often add in the URL is the control of action options, For example Options=rmf

So Finally the Web HTML should be:

For dashboard csr, located at  /shared/All/_portal/csr and page 1 in the dashboard:

 analytics/saw.dll?PortalPages&PortalPath=/shared/All/_portal/csr&Page=page%201&Options=rmf&Action=Navigate

You can read more about the URLs here: http://obieeil.blogspot.com/2013/03/embeding-obiee-portalpages-vs.html

and here: http://obieeil.blogspot.com/2014/10/obiee-go-url-and-filter-parameters-old.html

Connecting Public OAC to private ADW using PAC

 I have an OAC (Oracle Analytics Cloud) instance that is defined as Public Network Access and an ADW (Autonomous DataWarehouse) that is defined on Private Subnet in aVCN (Virtual Cloud Network). I want my OAC to connect to that private ADW.

There are 2 methods I can use to access the private ADW:

1. Using RDG (Remote Data Gateway), used as a bastion server that has access to the private ADW. The RDG is listening to the OAC instance and returns encrypted query results from private or on-prem. databases to the OAC instance.

2. Using PAC (Private Access Channel)  I can configure on the OAC (at the moment only the Enterprise Edition).

In this post I will cover the second option of PAC.

If you want to skip the rest of this post, the important part, beyond the ATeam posts mentioned bellow is the understanding that the PAC DNS Zone to connect to ADW should be "adb.<region>.oraclecloud.com" (Thank You Gayathri Anand for this tip!), all the rest is just the detailed steps.

Some reading materials on the subject: 

Oracle Documentation

ATeam blog: enabling oracle analytics cloud private access channel  and Ateam blog: connecting to private data sources using oracle analytics cloud private access channel as part of the OAC PAC Series

 

Initial state: 

OAC Public Network Access

VCN, named bdahab_vcn configured with Private Subnet named Private Subnet-bdahab_vcn (those are easily configured manually or using a wizard).

ADW instance configured with the above VCN and Private Subnet:

In my case I didn't follow best practice and my OAC instance and ADW are in the same compartment. It doesn't really meter, as long they are in the same cloud region.

Unlike the example in Ateam blog: connecting to private data sources using oracle analytics cloud private access channel, in my case the PAC and the ADW are on the same subnet, I will talk about it later here.

Steps:

If your OAC instance doesn't have a PAC (Private Access Channel), create / configure a PAC.

When entering the Wizard the default is not enough:

We must add the specific DNS Zone of the ADW instance.

In the ADW setting check the Private Endpoint URL, and copy the suffix. This is the DNS entry we need. 

In my case it is: adb.eu-frankfurt-1.oraclecloud.com (the format is adb.<region>.oraclecloud.com)

Back in OAC PAC configuration, we add it as the DNS Zone

The first time configure phase of PAC might take over 30 minutes (updating it, if needed, is much faster).

 

 

The last network setup step is setting the Security List for Private Subnet.

Under: Networking >> Virtual Cloud Networks menu find the relevant network (bdahab_vcn in my case), click on it and enter the private subnet (Private Subnet-bdahab_vcn, in my case).

There, copy the Private Subnet IPv4 CIDR Block and edit or add a security list:

 

In that security list I will add one ingress and one egress rule. In both cases I use the ADW port (default 1522) and the Private Subnet CIDR block.

Ingress rule:

Egress Rule:

That is all.

 Testing:

Now I am ready to test. To connect the ADW I need it's wallet. It can be found in the ADW setup under Service Console >> Administration >> Download Client Credentials (Wallet)

Create a password for the wallet and download it.

Now in OAC I can create a new connection, with that wallet:

Create Connection

 

Select ADW

 

Fill the details and select the wallet zip file

After the connection successfully created, I can create datasets with it:

For working with RPD you might want to set the connectin in OAC under Console >> Connections.

First extract from the above mentioned ADW wallet zip file the the cwallet.sso file. While you are there check the tnsnames.ora file as well.

Before creating a connection upload the cwallet.sso as the wallet from the top right menu. 

Now we can create the connection based on the data in the tnsnames.ora file using the enable ssl option. You might want to refrain from using the high option of the tns in the file. It is usually less fitting concurrent analytics needs.

You might want to see additional info here: Ateam blog: Connecting Oracle Analytics Cloud Version 5.1+ to the Autonomous Data Warehouse (last sections) and Ateam blog: Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse to see detaied description of the last subject.

PAC and ADW om separate Private Subnets

If you created a separate Private subnets for ADW and for PAC, you should follow the guidlines of the blog here (Ateam blog: connecting to private data sources using oracle analytics cloud private access channel). The only difference from my case is creating egress rule in one security list and Ingress in another, while using the relevant IPV4 CIDR block values of the subnets.