Monday, September 6, 2021

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" (), 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:

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.



Wednesday, August 18, 2021

Creating Custom SQL in RPD with "With clause" - Error

 In Oracle Analytics (OBI / OAS /OAC) we can define Model sources in RPD as tables/views, select statements or stored procedures. Customer wanted to use select statement as data source, and in that select to use a "With" clause. For example:


From the Model tool we can select "View Data" and see data perfectly. When creating analysis or projects on top of this we get the error:

 [nQSError: 17001] Oracle Error code: 32034, message: ORA-32034: unsupported use of WITH clause at OCI call OCIStmtExecute. 

 or, if using OAC RDG (Remote Data Gateway)

[nQSError: 77031] Error occurs while calling remote service DatasourceService. Details: [JDSError : 115] Invalid Database Name - Cause: ORA-32034: unsupported use of WITH clause Action: Please refer to logs for more Details (HY000)

 

The full error text in my case is:

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)
State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)
State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)
State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 32034, message: ORA-32034: unsupported use of WITH clause at OCI call OCIStmtExecute. (HY000)
State: HY000. Code: 17010. [nQSError: 17010] SQL statement preparation failed. (HY000)
SQL Issued: SELECT 0 s_0, "test"."select_with"."Channel" s_1, "test"."select_with"."Month" s_2, "test"."select_with"."Prod_category" s_3, "test"."select_with"."Amount_sold" s_4, "test"."select_with"."Quantity_sold" s_5 FROM "test" FETCH FIRST 500001 ROWS ONLY

Checking the sessions its clear why this happens. Oracle Analytics tends to add "With" clause  in to the SQL it generates. The Database does allow a With clause within With clause and returns ORA-32034.


To control the generated SQL we have few interesting options in the RPD Model physical features.

  • PERF_PREFER_INTERNAL_STITCH_JOIN
  • PERF_PREFER_MINIMAL_WITH_USAGE
  • FULL_OUTER_JOIN_SUPPORTED
  • WITH_CLAUSE_SUPPORTED

See here for more. 

In our case we only care about PERF_PREFER_MINIMAL_WITH_USAGE.

Now Publish / Sign In the RPD and we are done.

The analysis is created without additional With clause.


Kurt Wolf had similar issue few years back that required a bit more changes: https://www.kpipartners.com/blog/bid/82442/Using-WITH-in-Physical-Tables-Opaque-Views

 

 

 

 

 

 

Sunday, May 23, 2021

OAC - Oracle Analytics Cloud 6.0 new features

We can see the list of new features of OAC 6.0 in the documentation here and on YouTube here. Impressive list with revolutionary data preparation features.

General Features

Reload data sets on a schedule - Schedule data reloads for any data set with one or more tables set to automatically cache data. See video here.

data set menu with "Live" setting:

data set menu with "Automatic Cache" setting:


 

 

Hierarchy navigation for Subject Area and Essbase and EPM data sources - You can drill up and down the hierarchy tree in pivot and table visualizations. You can't use hierarchies in filters but you can use a hierarchy level in a filter. See the video here to see full set of options.

 


 Customize auto-refresh in visualizations - Set the frequency at which the data automatically refreshes in visualizations on a canvas (it is in the canvas features). See video here.

Visualization Features

 On-canvas filter types - Select various on-canvas filter types such as Top Bottom N and Relative Time. See video here (includes following 2 topics).


 



On-canvas  date filters - Use the on-canvas date selector and the relative time filter for date filters.


On-canvas  measure filters - Use the on-canvas measure selector and the top and bottom time filter for measures.

Improved sort options - Use the Sort dialog to configure sorts and clearly see the sort order and any sort conflicts (Beyond the click sort option). See video here (covers the next 2 topics as well).

 


 

Export individual visualizations - Export from an individual visualization in a project. Specifically, this allows data export from a visualization embedded in another application or web page.

Tooltips on visualization titles - Enter your own tooltip text to display when you hover over a visualization title.

 

Time series forecasting - Perform time series analysis forecasting directly from data flows. 

There is the regular Time Series option:

 

There is Oracle data Mining Based Time Series. See video here.


Graph analytics - Enhance your visualizations of network graphs using powerful property graph analytics. For example, you can compute the shortest path between two vertices or identify connected vertices in the graph. See the videos Shortest Path Graph Function in Oracle Analytics (1/4), PageRank Graph Function in Oracle Analytics (2/4), Sub-Network Graph Function (3/4), Graph Clustering Function in Oracle Analytics (4/4)


 

Improved home page search - Enter advanced search commands in the search bar to tailor your search results for exact matches, multi-term matches, and field-level matches. See docs here.


Map layers in non-joined data sets - Create map layers in a visualization where the data between the layers isn't joined. See the video here. The bellow screen is from it.

Average aggregation in Waterfall visualizations - When you add a Detail to a Waterfall visualization, the weighted average is calculated and displayed in the Bridge report. See video here.

 

Data Sources Features

Connect to remote / general JDBC data sources - Analyze data from remote data sources using custom JDBC drivers (through Data Gateway). Extends existing data sources list. See video here, docs here, my post here.

Connect to Google BigQuery - Analyze data from Google BigQuery data sources.See video here.


Data Preparation Features  😎

Data sets with multiple tables - Perform self-service data modeling with data sets by adding multiple tables to a data set from one or more relational data source connections. That includes ability to import existing relations from database. See example:

 

The multi-table datasets, still can be joined with other datasets on project level, as before. 

There is some interface change as well:


See videos: Multi Table Datasets in Oracle Analytics (1/5), Blending Multi Table Data-Sets in Oracle Analytics (2/5), Hybrid DataSets in Oracle Analytics (3/5), Multi Table Datasets in Oracle Analytics Dataflows (4/5), Multi-Table Datasets for Analytics Publisher Reports (5/5)

Custom knowledge - Extend the system knowledge in Oracle Analytics by adding your own enterprise-specific reference data. This increases Oracle Analytics' ability to automatically discover and classify semantic types of interest and to offer more relevant enrichment  recommendations. See video here.



Data quality insights - Explore your data, and use a visual overview of your content to assess and improve data quality. (+ smarter attribute / measure discovery). See video here.

 


Frequent Item Set function supports association rules - Create more powerful Frequent Item Set analyses using enhanced algorithms. See video here.


 

Oracle machine learning supports Feature Extraction - Apply a registered Oracle machine learning Feature Extraction model's view to a data flow.


Pixel-Perfect Reporting Features (Publisher)

Deliver pixel-perfect reports to Object Storage - Administrators can configure Object Storage as a delivery channel for Publisher. In your scheduled jobs and bursting queries, you can choose to deliver reports to the configured Object Storage. See video here.

Pixel-perfect reports support data sets with multiple tables - Report authors can use data sets with multiple tables to create pixel-perfect reports.See video: Multi-Table Datasets for Analytics Publisher Reports (5/5).



Oracle Analytics Cloud (OAC) and generic JDBC

With the new release of Oracle Analytics Cloud (OAC) 6.0 there are many great features (see my post here). One of them is the option of Generic JDBC driver. See the YouTube video here. Lets talk briefly about it.

OAC supports a long list of data sources both as part of the data model (RPD) and as a connection created for data sets (see the list here). Since OAC is a managed service, users can't install anything they want on the server. Yet, somehow there are often requests for one more data source that is not on the list. 

In OAC 6.0 we can now use your own JDBC driver for any data source you like. How Is it done? Using the Remote Data Gateway (RDG) solution Oracle provides with OAC. 

The RDG download is available here. The Linux installation is extremely simple and the configuration is few simple steps of enabling the security tokens from the local RDG server to OAC service and is described here.

Now the JDBC JAR file deployment is simply copying the JAR file to <install_location>/Oracle/Middleware/Oracle_Home/domain/jettybase/thirdpartyDrivers as described in Oracle documentation here, and restarting the RDG.

All that is left is to create a new JDBC connection in OAC. Please note, you don't see JDBC connection option in OAC unless you have remote Gateway configured.






For driver class, see the driver class name contained in the JAR file. 

You can disregards the little documentation bug that refers to User Name / Password of specific data source (instead of generic JDBC source) in the documentation.

 

 A nice detailed example (for Teradata) is available from Oracle A-Team (not actually using JDBC, yet doing similar process): https://www.ateam-oracle.com/configuring-oracle-analytics-cloud-oac-with-data-gateway-to-connect-to-teradata