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.

NetSuite, SuiteAnalytics Connect and OAC - Oracle Analytics Cloud

When Working with NetSuite you have various internal Search and Reports options. They are often sufficient for the Analytics requirements and hide quite well the complexity of the database behind the scenes. Sometimes we might want more. One option is to use ETL, see here for example. Other might prefer connecting to NetSuite data directly. It might be because we want to combine NetSuite live data with additional resources or because we just want more analytical capabilities.

Oracle Analytics Cloud (OAC) has an NetSuite connection since OAC 5.5. This YouTube video shows the simple part of connection creation, yet this is only part of the picture.

I would like to thank Tomer Yudelevitz, who patiently helped me discovering most of the described bellow.

To connect to NetSuite from OAC you need  SuiteAnalytics Connect enabled on NetSuite. This is a paid option. It is not extremely expensive, but not free.

NetSuite Steps:

Once you have SuiteAnalytics Connect...

1. Under Setup -> Enable Features, turn on  SuiteAnalytics Connect.

2. To see the connections details, go to  Home page -> Settings portlet, click on “Set Up SuiteAnalytics Connect”.
You will see a screen similar to the following, it provides important information for later connectivity requirements, with few problems.

 

a. The better data source, since 2019 is NetSuite2.com (NetSuite.com is older version) 

b. Role ID 3 is Administrator. We can't use it with Netsuite2.

From NetSuite documentation we find that the following list of Roles not allowed with Netsuite2.com:

* Administrator
* Full Access (Deprecated)
* Roles requiring Two-Factor Authentication (2FA)
* Roles accessing the Connect Service with IP restrictions
* SuiteAnalytics Connect - Read All Permission

3. To set up SuiteAnalytics Connect permissions to a role:
    a. Navigate to Setup > Users/Roles > User Management > Manage Roles.
    b. Click Customize next to the name of the role for which you would like to add the SuiteAnalytics Connect permission.
    c. Click the Setup tab under the Permissions tab.
    d. Add the SuiteAnalytics Connect permission- full
    e. Click Save.
Use this role to set up the connection to the new data source to see if it works.

 4. You might want just to create a new Role in NetSuite for SuiteAnalytics Connect. If you do it, please consider it to be a name without spaces, due to a little annoying bug in OAC at the moment (OAC 5.9).

 

OAC Steps:

 In OAC create a new Connection and select NetSuite.

Fill the parameters based on what we have seen in the NetSuite part:

 

 

It should be something like this (when Role ID depends on your settings):

Now we can create a new data source:

When you do it,  you can see your NetSuite Role name and select views:

 As I mentioned, while I'm writing this blog there is bug in OAC (OAC 5.9) with spaces in NetSuite role name (it should be fixed soon). As a result we can preview the data source data, but can't continue after you add the data source. Workarounds are using NetSuite role name without spaces or generating select statements instead of selecting views.

 

 

Extra Tips:

Unfortunately, I don't know of any simple way to convert existing NetSuite content to SQL. As a result, some digging is required. 

They are many views in the system. Most are easy to understand, such as  Account or Transaction, other are less, such as CUSTOMLIST292. We might also be interested in discovering the joins and keys of the views, as well of getting the most from existing NetSuite content such as searches.

They are few tools that can assist us:

1. The NetSuite data dictionary including connections between tables, (the links bellow are with transaction example): 

    The Analytics Tables/Views: https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2020_2/analytics/record/transaction.html

    The General schema Browser: https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2021_1/odbc/record/transaction.html.

2. There are various tips how to extract code from NetSuite searches like this one: https://chrome.google.com/webstore/detail/netsuite-search-export/gglbgdfbkaelbjpjkiepdmfaihdokglp?hl=en-US, this chrome add-on lets you export a saved search as Suitescript code. From this script it's easy to see the physical column names and formulas. This add-on of field explorer might help as well https://chrome.google.com/webstore/detail/netsuite-field-explorer/cekalaapeajnlhphgdpmngmollojdfnd. See more about them here: https://www.abaci-us.com/chrome-extensions-netsuite/

3. There is a view named Customlists that has all the readable names of the custom tables (so we can understand what CUSTOMLIST292 is):

 

If you have more tips and ideas, especially about converting NetSuite content to SQL, please update here.

P.S.

Apparently there is formal NetSuite documentation about SuiteAnalytics Connect here.