Connect OAC with ADW

To connect OAC with ADW requires two configurations. One connection for use with DV and one for use with OAC Answers & Dashboards.

To create both connections, first download the Security Wallet from ADW and create the Admin Password (if not already done).

Prepare configuration of ADW

Download Wallet:

1) Go to ADW instance and click DB Connection Button

image

3) click Download Wallet

image

which stores the .zip file to you local drive:

image

This .zip file can already be extracted, since the tnsnames.ora and the cwallet.sso file are needed for later steps:

image

Set Admin Password

If not already done navigate to the ADB Service Console

image

and click Administration and set the Admin password:

image

Configure Connection for ADW of OAC (Answers & Dashboards)

To configure the connection to ADW for Answers and Dashboards of OAC navigate to the OAC Service with a similar URL like below:

https://***tenant.name***.analytics.ocp.oraclecloud.com/dv/ui?pageid=home

and click on Console:

image

click on Connections under Configuration and Administration

image

click on Upload wallet on the right Burger Menu:

image

This allows to upload the previously downloaded wallet from the hard drive (you may need to acknowledge a virus scan warning before):

image

After uploading the Wallet successfully a success message will be displayed:

image

After click the Burger Menu on the right again the button will now display “Replace Wallet” instead of “Upload Wallet”:

image

After the Wallet has been uploaded, the connection can be defined using the Create Button on the right. Within this Menu its important to select “Host, Port and Service Name” from the Connect Using dropdown Menu and enable SSL.

image

The Host, Port and Service name can be retrieved from the extracted tnsnames.ora file of the Wallet. This file can be opened in any editor to check the values:

image

The Connection will be displayed in the list:

image

A new metadata model can now be created and uploaded to OAC using this connection.

Configure Connection for ADW of DV (Data Visualisation)

On the Homepage of OAC click Data and select Connection:

image

click ADW from the available connection types:

image

specify the connection details and upload the extracted sso credential files from the wallet (cwallet.sso):

image

The connection will be displayed in the list of connections:

image

After the connection is established, a Data Set can be created using this Connection to ADW.

choosing the right shape for OAC

when provisioning Oracle Analytics Cloud, different shapes are available to run the Service. For shapes based on only 1 OCPU auto scaling will not be available after the initial provisioning of the service. Thus, if the OAC service needs to support scaling after the initial creation a shape with at least 2 OCPUs should be selected:

image

show bars on dual axis

Oracle Analytics Cloud provides the Line Bar chart two display two Y (Vertical) Axis:

image

The second Axis uses a Line, but this can also be changed to a bar, by going to the Advanced Tab and edit below line (search for “riserType”) from “line” to “bar”:

<saw:column measureType=”y2″ riserType=”bar”>

image 

To provide the following Chart appearence:

image

80/20 rule, more like 40/80 rule..

The Pareto principle suggest that with 20% of effort already 80% of the result can be achieved. Translated to business management, it is often reported that 80% of sales come from 20% of clients.

The following case study shows a example flight from Washington to London Heathrow operated by BA:

image

Even though the prices shown are undiscounted trip prices and before and yield optimization to fill the plane, the give a rough indication.

In this case, 45% of the passengers (of the none economy classes) will contribute 84% of the revenue.

image

This shows that the 80/20 rule can sometimes be more like a 40/80 rule.

This can also be visualized with a stacked bar graph:

image

Within Oracle OAC, a dedicated Pareto graph is also available to show the relation between effort and results and the importance of the top contributors. This is definitely one of the patterns that each business should know about and beware of:

image

Link

OTBI direct database access disabled Oracle Fusion 19A

With the most recent available upgrade of Oracle Fusion Applications Cloud 19A, the direct database access with OTBI has been disabled by default by Oracle. The feature will be disabled within a grace period of 12-month:

This is also looged a Bug, since many customers relied on the feature:

Community discussion: R13.19A OTBI Direct Database Query Privilege Change

Fusion Application: Upgrade 19A What’s new (see chapter: OTBI DIRECT DATABASE QUERY PRIVILEGE IS DISABLED BY DEFAULT FOR
AUTHENTICATED USERS )

The reasons for this are security concerns from Oracle side and the currently only remaining alternative, is to use BI Publisher in cases of direct database access (as confirmed in the community discussion by Oracle, this method will not be restricted).

OAC

Additionally, for OAC, the direct database access feature as also been removed from the default permissions of the logged in User (Authenticated User). But, using the Manage Privileges Pages, the permission can still be assigned for Users who require the feature:

How to create a direct database access is also described in a previous post.

Downloading a rpd from OAC Thin Client Modeller

The web modeller or Thin Client Modeller was introduced with BICS and OAC respectively, to allow a more agile metadata modelling. The thin Client modeller does not provide the same level of modelling functionality compared to the “classic” .rpd metadata model. Additionally, with the current release of OAC only one modelling approach is supported. Thus, only the thin client model or the classical model can be deployed as metadata at a time.

Hence, the might be a scenario for switching from the thin client modeller (web modeller) to the classic .rpd model to support more complex metadata modelling.

A .rpd file can be generated from OAC when downloading a Snapshot and extracting the .bar file. The .bar file can be downloaded from the Snapshots area of the OAC Console:

image

When clicking on a option menu right to the Snapshots, the Console allows  to download the snapshot as .bar file. Once the .bar file is downloaded (after assining a password to protect the downloaded file), the file can be opened with a Archive Tool such as 7z.

image

The .rpd data model can be opened like any other classical metadata model using the BI Administration Tool. This will contain system generated Objects and Names:

image

OAC: specifying the Connection string within rpd model

The following is a step-by-step guide to determine the Connection string for Oracle Analytics Cloud and Oracle Database Cloud.

When logging into your Oracle Cloud Service using your identity Domain, a similar page is being displayed:

image

Click on the Action Menu of the Database Cloud Service and select “Open Service Console”.

This Page shows details about your Cloud Services such as OCPUs, Storage or Memory:

image

click on the Database Service Name to get to the Details Page:

image

This reveals the Public IP of the Oracle Cloud Database and the required Service Name.

Within the Oracle BI Administration Tool, the connection string needs to be created in the Data Source Name Field:

image

Using the following format:

image


(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PDB1.xxx.oraclecloud.xxx)))

After modelling the data and saving the .rpd file, the Model can be uploaded to the OAC Cloud:

image

Essbase in OAC and Oracle EPM (SaaS) Cloud

The first offerings from Oracle for EPM within the Cloud have been the SaaS Subscriptions for:

  • FCCS (Financial Consolidation and Close Cloud)
  • TRCS (Tax Reporting Cloud)
  • ARCS (Account Reconciliation Cloud)
  • EPBCS (Enterprise Planning and Budgeting Cloud Service)

Since March 2017, Oracle also Offers a PaaS Cloud Service with Oracle Analytics Cloud (OAC) which can complement the SaaS Cloud Offering, since it provides a Essbase in the Cloud Service (EssCS).

image

The main purpose of OAC is to provide BI as-well as Essbase (EssCS), within a single Cloud Offering (since Essbase was not available with BICS) for Reporting purposes. It is also supported to migrate on-Premise Essbase cubes into EssCS.

Within EPBCS, Essbase (technology) is also used for Planning purposes. To provide these Planning capabilities, EPBCS not only consists of Essbase cubes, but also relational tables and UI Templates for the Business User to enter the planning data. Since, EPBCS is a SaaS service, the underlying Essbase Cube is not accessible directly and can only be accessed with certain exposed interfaces, whereas the Essbase Cubes from EssCS of OAC is completely accessible, since it is a PaaS service on dedicated instance for a particular customer.
Former Hyperion Planning Applications should be migrated into EPBCS, since this is the equivalent Cloud offering for Planning.

For Reporting purposes of Planning cubes, these can be migrated from EPBCS to EssCS of OAC using EPM automate for extraction and importing via the CLI (Command Line interface). The EPM (SaaS) Cloud also offers a Reporting Cloud Service with EPRCS with a focus on narrative Reporting and XBLR Reports for external stakeholders. EPRCS can source data from EPBCS, FCCS, TRCS, ARCS as well as EssCS.

Accessing data in Oracle Fusion Applications Cloud for Analytics

The following diagram shows the high-level options to access data within Oracle Fusion Applications (FA) Cloud for Analytical purposes:

Please note: that this diagram is of April 2018 and may not apply in full in future.

For more details on how to connect to Oracle Fusion Applications (FA) Cloud using Visual Analyzer (VA) and DV Desktop (DVD), please reference the previous article.

The details for connecting to FA using ODI (with BI Applications) or BI Data-sync will be described in future articles.