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

 

 

 

 

 

 

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

Upgrading or Installing Oracle analytics Model Admin Tool (RPD) for ADW

I want to use my Model Admin Tool (the RPD development tool) with ADW (Autonomous Data Warehouse). ADW uses client credentials (wallet). How to connect ADW and load metadata?

The A-Team covered quite nicely the topic of Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse so I will repeat it briefly and talk about the one thing you need to do when installing a new version of Admin client while you already have a working installation with Oracle ADW (Autonomous Data Warehouse)("upgrade").

New setup

1. Download or obtain the the ADW client credentials (wallet). You can find in in the ADW Service Console, under Administration.

 

2. Save the file and unzip it on you computer (outside the Model Admin Tool installation). In my case it was C:\Oracle\ADW

 

3. Install the Analytic Client Tool available here (in case of link malfunction , I try to keep update version of link in the "links" section of this blog, as "OAC developer Tools") on your computer, in my case it was C:\Oracle\Middleware\Oracle_Home5_9 or use existing installation.

4. Copy the files sqlnet.ora and tnsnames.ora from the wallet you unzipped earlier (in my case C:\Oracle\ADW/sqlnet.ora and C:\Oracle\ADW/tnsnames.ora)   to the new Client Tool installation under: domains\bi\config\fmwconfig\bienv\core (in my case C:\Oracle\Middleware\Oracle_Home5_9\domains\bi\config\fmwconfig\bienv\core).

5. In the copied sqlnet.ora file change the wallet location to the correct one in your case. (in my case it is: WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\ORACLE\ADW"))) )

6. Run the Model Admin Tool and create metadata, based on the standard Oracle interface - Oracle Call Interface (OCI). Use the content of the tnsnames.ora file you copied earlier. In the file they are 3 types of connections. I think the low type of connection is the proper one for analytics. Fill the User / Password for ADW. Select the tables from ADW...

7. In the connection pool itself we might want to set and use the ADW connection in OAC console and use Externalize Connection. See details at the "Using the Externalize Connection Option" section at the A-Team post.

Installing new version of Admin Tool - "Upgrade"

I installed a newer version of admin tool. In my case at C:\Oracle\Middleware\Oracle_Home6_0 .

Assuming my wallet is located at some shared location (as described in previous section) and I had an older version of the client working with ADW, all I have is to copy the 2 ora files from domains\bi\config\fmwconfig\bienv\core of the older installation. In my case I copied the sqlnet.ora and tnsnames.ora files from C:\Oracle\Middleware\Oracle_Home5_9\domains\bi\config\fmwconfig\bienv\core to C:\Oracle\Middleware\Oracle_Home6_0\domains\bi\config\fmwconfig\bienv\core.