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.

DVD: Filtering Measures based on conditions

Within Analytics we can define different types of Measures like discrete (raw) Measures or percentage or ratio Measures. For discrete measures we can also define a base measure and derived measures. E.g. within the HR domain we can define a Headcount Metric as the base metrics and a male, female and diverse Headcount as the derived measure. When adding up all male, female and diverse it should be identical with the (overall ) Headcount. Another example is within the Procurement domain where we can define an invoice amount and a invoice paid and invoice unpaid derived measure:

image

This break-down allows to further understand how a certain metrics is comprised.

Within Oracle Data Visualization there are two possible options to achieve this filtering.

  1. Using the Filter operator
  2. creating a case when statement

Using the Filter operator

The first option is to use the Filter operator within the formula editor:

image

Within the formula editor, a measure can be defined from a list of available measures, but it’s also possible to define new measure using a count aggregation on a attribute column like Invoice Number and add a filter on the dimension e.g. Paid Yes or No.:

image

However, when using this Filter function with a newly defined measure (like above using a count aggregate on Invoice Number) the filter is not applied in DVD, as can be seen in below screenshot were the count for # of invoices is identical to the count of # of invoices paid:

image

This might need to be considered a Bug, since this should apply the filter to the defined measure.

When using an available measure from the drop down list, the filter is correctly applied. E.g. when using the Value measure (representing the amount of the invoice) the filter can be applied for paid/unpaid using the Payment date attribute:

image

and correct application of the filter on the data set:

image

Thus, the Filter operator may only correclty work when using a actual measure available from the drop-down list and not with newly defined measures “on-the-fly” in the formula editor.

The alternative is to use a case when statement:

Case when statement

Since, the Filter operator doesn’t evaluate the filter correctly, the other option is to use a case when statement that counts the number of invoices if an invoice has been paid like below:

image

This formula checks if a Payment date is set and if a payment date is set then it uses the invoice Number for counting:

COUNT(DISTINCT case when PAYMENT_DATE is not null 
then INVOICE_NO 
else NULL end)

This formula (based on the case when statement) applies the intended filter on Payment date correctly and allows to calculate the derived measure.

image

To conclude

To apply filter for a measure that is not available from the data-set (defined in the Prepare Tab as a measure) use the case when logic to calculate based on a condition.

If a measure is available you may also use the predefined Filter operator from the formula editor.

DVD: difference in NULL Value handling

When using DVD Version Version 12.2.5.3.0-20190529084035

image

there is a difference in the handling of NULL values between the Preparation Tab and the Visualization Tap:

image

Within the Prepare Tab the NULL values are interpreted as empty strings, meaning if no Value is provided within the Source (e.g. a Database Table or an Excel Spreadsheet) then it is interpreted as a field with no content like ‘’, but not as a NULL value.

Below formula checks for NULL values using the IS NULL operator, but since null values are being interpreted as empty string, the evaluation is 1 for all values (of Payment date column):

image

when changing above formula from IS NULL check to a comparison of an empty string ‘’, the formula shows the below results were content is classified into 0 and 1:

image

The above figure shows the evaluation of the field by performing a check with an empty string ‘’. But, when switching from Prepare Tab to the Visualize Tab, the results do not coincide! The check for an empty string is not consistently evaluated, as shown below:

image

when using the empty string check on the Visualization Tab, every content of the Payment date field is evaluate to 1 (a value being present).

The Visualize Tab requires the IS NULL check to check for NULL values:

image

To conclude

If you want to check for NULL values use the IS NULL or IS NOT NULL operator despite the results shown within the Prepare Tab. There are some Known Issues with the current version of DVD and the handling of null values as documented to some extend (the document is only in respect to Excel files, but above scenario is tested on Oracle Database) in this Known Issue document (from Sept ‘19).

Error with DVD and DVML when installing in directory with spaces

It is recommended to install Oracle DVD into a directory without any spaces. While the installation of DVD will work for a directory with spaces like “C:\Program Files\Oracle\Data Visualization Desktop”, the subsequent installation of Oracle DVML will not work for the same. When installing DVML after installing DVD to a directory without spaces, the following error will be thrown:

"C:\Users\****\AppData\Local\Temp\BiPython_Framework.exe" is not recognized as command file.
Could Not Find C:\Users\****\AppData\Local\Temp\BiPython_Framework.exe
Error: Unable to access jarfile C:\PROGRA~1\Oracle\Data

This behavior is also describes in below Oracle Support Note:

DVML install fails for users with a space in Username Profile (Doc ID 2509892.1)

If the installation into the Program Files directories (like “C:\Program Files” or “C:\Program Files (x86)”) of Windows is intended, the following workaround can be applied to install DVD and DVML into a directory with spaces:

within script:

…\Oracle Data Visualization Desktop\install_dvml.cmd

add double quotes around below pathes (add double quotes shown in red below):

Line 20: … -jar %BI_PRODUCT_HOME%\modules\oracle.bi.dvdesktop.installer\rinstall.jar -download=true       …
Line 27: … -jar %BI_PRODUCT_HOME%\modules\oracle.bi.dvdesktop.installer\rinstall.jar -updateDVMLJSON=true

Afterwards the installation of DVML should run without errors.

Use of Date data type in Oracle DV

DV allows to make use of individual elements of any Date or Datetime column. Thus, there is no need to calculate the Year portion of a Date as an additional column. For this to work correclty the data type must also be of Date or Datetime on the Database. When importing a text (csv) file to a Oracle Database, a Date may be defined as a text (varchar) field by default. In order to make use of this DV Feature, the data type should be changed to Date data type when importing e.g. with SQL Developer. Otherwise, the field containing the Date may just be displayed with a blue “A” like any other attribute, but not with the purple “clock” to identify a true Date field.

image

DV: Displaying data as % (Percentage) Of other column in graphs…

To display data as percentage of, can be defined within the Visualization properties for Tables and pivot Tables. However, the percentage of does not exist for all Visualitzations such as Bar Graphs and thus requires the calculation of these percentage values. Other Visuals like Pie Chart or Donut do support to display the data as Percentage Of.

Taking the following baseline example with Car Accidents by Body Type and if drinking has been involved:

image The percentage Of can be displayed for each column using the Value Tap of the Visualization properties:

image

This will result in the following view for the same table (as above), showing the percentage of values:

image

The above setting will only show the percentage of with respect to the same column (distribution within the same column, as black arrow below) and not as percentage of Body Type (Green Arrow below)!

image

The property section, does have the possibility to change the relation for displaying the percentage to other columns, e.g. Body Type, but this is not calculating correctly within DV (no further inverstigation on this Issue):

image

As mentioned at the beginning, for some Graphs like Bar Graphs, there is no option to display the data as percentage of values, since this is not the best visualisation to represent distributions and thus the Bar Graphs will only show the absolute values, like:

image

In order to display the correct Percentage Of in a table or in a graph, it is need to calculated as Percentage Of before hand. The formula for calculating the percentage is:

percentage value = SUM (occurences)  / COUNT (overall)

image

In order to calculate the correct sum for the occurences, the formula for decoding the attribute field into 1 and 0 has to be done on the data preparation step (on the prepare tab). Otherwise, the SUM will not calculate correctly when using the “My Calculations”, since it will not apply the calculation on each row, but only on the aggregated result as clearly shown below with the bottom red box:

image

This is because the Case When Formula is not evaluated row-by-row, but only applied after aggregation. Hence, the decoding of values for calculating the percentage must be placed within the data preparation step:

image

After this preparation, the formula can be defined as shown below (Please note the multiplication with 1.00 to convert to a decimal number to represent the percentage):

image

This gives the intended result for correct Percentage Of values within a Bar Chart and Table View:

image

Connecting to Oracle Fusion Applications (FA) Cloud using Visual Analyzer(VA) and DV Desktop(DVD)

The following Article is written as of April 2018. Since, the Cloud is evolving rapidly, it may not apply in full, in future.

The aim of the Article is to demonstrate the possibilities of connecting to Oracle Fusion Applications (FA) Cloud (via OTBI) without any data extraction (ETL). Thus, only using declarative approaches offered by Visual Analzer (VA) of Oracle Business Intelligence Cloud Service (BICS) /  Oracle Analytics Cloud (OAC) and the downloadable version of VA, the Data Visualization (DV) Desktop (DVD). The following diagram re-iterates the scope of this Article:

image

The following Version and respective features are used as baseline for this Article (as available of April 2018)

 

Product Version
Oracle Fusion Applications Cloud / OTBI R13
BICS 18.1.6.0.0
OAC 18.1.3
DVD 12.2.4.2.0

Oracle Fusion Applications (FA) Cloud comes bundled with OTBI, which provides pre-built BI Subject Areas to access the data stored in FA:

image

However, OTBI does have some limitations when it comes to extensibility (no access to metadata model) and adding of additional data sources (limited to data connection of respective FA instance).

The Oracle Cloud offering for “stand-alone” analytics are BICS (and in the future OAC). Hence, there maybe the need to connect from BICS/OAC to Fusion Applications. This is possible by two general Options:

This Article only focuses on the latter three options (declaring the connection details without any data movement).

Connecting from DV Desktop to Oracle Fusion Applications (FA) via OTBI

DV Desktop represents a offline Version of Visual Analyzer and can be downloaded from OTN. Once  installed, it can be used to create a Connection to Oracle Fusion Applictions from the top right create Menu:

image

One of the Options from the “select connection type” list is “Oracle Application”:

image

This “Oracle Application” is the reference to Oracle Fusion Application’s Cloud via the OTBI URL !

image

It will not work with only the Oracle Fusion Application Cloud URL e.g. https://<IdentityDomain>.fs.em3.oraclecloud.com/homePage/faces/FuseWelcome , but only the respective OTBI URL e.g.: https://<IdentityDomain>.fa.em3.oraclecloud.com/analytics

If the URL is entered incorrectly, e.g. when using a trailing “/” it will not save the connection:

image

Once the correct OTBI URL, Username and Password are entered and saved, the connection is added to the list of available Data Connections:

image

As stated before: These “Oracle Applications” connections will only work with the respective OTBI URL, since the metadata (such as subject areas or Report from Catalog) is retrieved from this URL only. Once the connection is created, the Subject areas are available and the Analysis can be created in DVD:

image

Connecting from Visual Analyzer (VA) of BICS to Oracle Fusion Applications (FA) via OTBI

The equivalent of DVD for the web is Visual Analyzer (VA), which is part of OBIEE 12c as-well as BICS (and OAC). The same connection to Fusion Applications Cloud can also be created within VA of BICS. To do this, the VA Projects needs to be selected from the BICS (Classic) Home Page:

image

Within VA, click on “Add Connection” under the “Add new Data Source”:

image

And choose “Oracle Application” as connection type once again:

image

For the web version, the list of available data connections is also much smaller (only Oracle Applications and Oracle Database) compared to DVD. The connection details need to be entered in a similar way for VA e.g. as shown below:

image

After the Connection was saved successfully, the Connection can be used to create a Visual Analysis. However, VA of BICS will only provide the possibility to access saved Reports, but not the Subject area’s itself (compared to DVD)!

image

Connecting from Visual Analyzer (VA) of OAC to Oracle Fusion Applications (FA) via OTBI

The steps for connecting from VA of OAC to Oracle Fusion Applications (FA) are very similar to VA of BICS. Select VA from the OAC Homepage:

image

And create the connection in the same way. After successfully creation of the connection, it is listed as Connection in the Data list:

image

VA of OAC is not limited to create Analysis on predefined Reports (compared with VA of BICS). It is also possible to create an Analysis based on the subject area (similar to DVD):

image

The screenshot shows, the data preview from the Subject areas:

image

Important for Reports and Dashboards

(as of April 2018) The possibility of connecting from BICS/OAC to Oracle Fusion Applications (FA) Cloud is only available with VA. This is not (yet) available for Reports and Dashboards:

image

For Reports and Dashboards only a Database Connection can be added, but not a Oracle Applications connection.

Conclusion

  • Oracle Oracle Applications connection Type refers to the OTBI URL of Oracle FA
  • The possibility of connecting from BICS/OAC to Oracle FA is limited to VA
  • VA can be used to connect to OTBI, but cannot be used to connect to the underlying database
  • VA (via DVCS) can be used to connect to OBIEE .