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

Advertisements

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

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

fixing error: NQSError 43113 table is functionally dependent upon level

For defining a logical hierarchy, the below error message is thrown within the Administration Tool:

image

table is functionally dependent upon level but a more detailed child level has associated columns from the same table or a more detailed table

The logical Hierarchy looks like this:

image

The Issue, with this hierarchy is, that the month level has automatically been added to the logical and physical table by defining a join between this dimension and the fact table. After removing this automatically added key from the logical and physical table, the error is resolved:

image

Link

Oracle Announces new branding for its Analytics Products and Services

Announcement by Oracle of it’s new branding and strategy around Analytics. With Oracle Analytics Cloud (OAC), Oracle Analytics Server (as on-Premise Version of the Cloud) and Oracle Analytics for Applications (new pre-built Analytical content comparable with BI Applications, but easier cloud setup and fewer customization possibilities e.g. no access to .rpd)

Blog posts:

Official press release:

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