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).

Link

Activate the Oracle Cloud after signing the contract (Order Document):

The Steps to initially activate the Oracle Cloud after purchasing are describes below:

https://docs.oracle.com/en/cloud/get-started/subscriptions-cloud/csgsg/activate-your-order-your-welcome-email.html

With the activation the customer chooses the Account Name (or actually Tenant Name) of their Service, that will be displayed in all subsequent URLs and in the background the Oracle Cloud is physically provided within the chosen Data Center region e.g. EMEA and the Cloud Console will be made available (via DNS configuration in the Oracle Cloud) with the specified Account Name and services like IDCS (Identity Domain Service) are started to maintain the User(s). The Person who conducts this initial activation is also the Cloud Service Administrator and from now on the only person to be able to add further Users as Cloud Service Administrators.

After completion of the steps from the activation link, the user receives another e-mail to his e-mail address with a provisioned user (from IDCS) and a new temporary password to change on the next login.

Provision individual Oracle Cloud Services, such as ADW

The steps to provision Services are described below:

https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/adwc/OBE_Provisioning_Autonomous_Data_Warehouse_Cloud_bak/provisioning_autonomous_data_warehouse_cloud.html

Actions after signing in:

https://docs.cloud.oracle.com/iaas/Content/GSG/Tasks/signingin.htm

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

Modelling the calculation of Install base for contracts/subscriptions

For subscription based business, it is important to correlate the new activation’s and ending contracts (churn) with the install base (for a given period). Typically, a customer will have one contract with a certain tariff, provider or product to describe the offered service for the subscription with an activation(start) date and a end date.

Below is an example of how a contract could look like:

image

From an analysis perspective, the result should present a diagram that provides information about the # of activation’s, # of churn (ending contracts) in relation to the # of install base contracts, like below:

image

Baseline for this scenario

For this scenario, a dimensional model with fact and dimensions will be used to describe the data. The contract entity is the fact, while the following other entities are the dimensions used to describe elements of the fact. E.g. the provider dimension contains more detailed information about the provider and the Unit Type and Unit Status dimension contain more detailed information about the device (unit) used for the subscription.

image

Modelling the calculation for # of Activation’s and # of Churn

Keeping in mind the entity for contracts, shown at the beginning, the measures # of activation’s and # of churn can (easily) be calculated by using a count function for the respective date attribute (on the fact) and the join with the time dimension will make sure that the calculation is done correctly for the time element (day level) and higher hierarchy levels (e.g. month/quarter/year).

On the physical model, the date attributes (start date/ end date) of the contract fact are joined with the calendar day of the time dimension:

image

within in the business model, the measures for # of activation and # of churn are added to the subscription fact based on the respective dates using the count function:

image

Once an users drags above elements into a analysis, e.g. at day level

image

the Oracle Analytics Cloud (OAC) will generate the query based on the metadata model (.rpd) like below:

image

This will also work in terms of aggregation levels e.g. for month level

image

Modelling the calculation for # of install base

While the calculation of the above metrics (activation and churn) is straightforward, the calculation for respective install base for a given period is not. This is because the query context for the calculation of the install base cannot be considered. The activation (and end date) can be linked to the time dimension (timeline) for the correct calculation of activation’s and churn. But, this will not work for calculating the install base as the context of the start and end period (of the query) cannot be linked to the timeline.

image

The above diagram shows that the activation and churn can be linked to the time context (timeline) by the respective date itself. But, this context is not available for calculating the valid contracts as install base for a period. To calculate the correct install base, the start date and end date of the contract would need to be linked to the requested query context e.g. install base as of 19.8.2019 (day level) or as of August 2019 (month level).

The only context that would be available would be the current date of the query using the current date (sysdate) to calculate the install base as of today (now). But, this would only provide a current AS-IS view, but not any historical data for a AS-WAS view:

CASE 
WHEN  contract start date <= current date AND 
contract expire date >= current date 
THEN 1 
ELSE 0 
END

The solution to is pre-calculate the install base on day level (for all dimensions, not only the time dimension), within another fact table for the install base.

image

This table could look like the following:

image

and some sample values:

image

This table with can be modeled as an additional logical table source within the BMM Layer of Oracle Analytics Cloud to provide the value of the install base for the given period and dimensions:

image

with the following column source definition for the metric:

image

and the following level definitions:

image

On the physical layer the table needs to be joined with the dimension, like:

image

when running an analysis within BI Answers for activation’s and install base:

image

Oracle Analytics Cloud will execute two queries (for each LTS) to create the combined result:

image

Once another (third) dimension is added to the query, the OAC will use the defined join e.g. inner join to produce the below result:

image

because of the join definition, within the BMM:

image

The resulting database query will also be more complex, as below:

image

Drill-down to contracts from metric

This approach will also allow to use support a drill-down from the metric to a detailed list report. Since, the number of activation’s is calculated on the detailed table, the drill-down from the metric will work easily to the detailed list. The detailed Report needs to be filtered by the higher level elements, which are passed down when a user drills to detail:

image

This drill down method will also work for the install base, which is effectively sourced from another table (LTS).

image

Of course, for this to work correctly, the pre-calculated values for the install base within the aggregation table needs to match with the list of detailed contracts to give the exact results for this context when drilling down.

Conclusion

It is not possible to create a formula within the metadata (.rpd) for calculating the install base. To provide the install base for analysis, the install base needs to be pre-calculated within a dedicated (aggregation) table e.g. during the ETL process. This table can be added to the subject area or business model as an additional LTS. This solution will also support drill-downs by passing the context of the metric as parameters to the detailed report to provide a detailed list of contracts.

Downloads

the .rpd can be downloaded here . The password is ‘Admin123’.

Create Oracle Database user with required privileges

Create Oracle Database user with required privileges

Use the following Oracle SQL to create a database user with the typical privileges to connect and work with the database:

CREATE USER dbusername IDENTIFIED BY dbuserpassword;
GRANT CONNECT TO dbusername;
GRANT CONNECT, RESOURCE TO dbusername;
GRANT UNLIMITED TABLESPACE TO dbusername;

Link

Oracle published results of Customer Support satisfaction survey

Oracle has just published the results of its support satisfaction survey:

https://oradocs-corp.sites.us2.oraclecloud.com/authsite/home/news/article/MySites-Home-News/CORE66BA77F48089483498F3DE2EEF3F8A77/?msgid=home-itk-customer-satisfaction-survey-july2019

And the results are better then expected with a score of 9.11 of 10 (target was 9.01) and an increase from 8.81 of the previous year.