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:
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:
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:
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.
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:
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:
Once an users drags above elements into a analysis, e.g. at day level
the Oracle Analytics Cloud (OAC) will generate the query based on the metadata model (.rpd) like below:
This will also work in terms of aggregation levels e.g. for month level
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.
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.
This table could look like the following:
and some sample values:
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:
with the following column source definition for the metric:
and the following level definitions:
On the physical layer the table needs to be joined with the dimension, like:
when running an analysis within BI Answers for activation’s and install base:
Oracle Analytics Cloud will execute two queries (for each LTS) to create the combined result:
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:
because of the join definition, within the BMM:
The resulting database query will also be more complex, as below:
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:
This drill down method will also work for the install base, which is effectively sourced from another table (LTS).
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.
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.
the .rpd can be downloaded here . The password is ‘Admin123’.
Oracle Analytics Cloud provides the Line Bar chart two display two Y (Vertical) Axis:
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”>
To provide the following Chart appearence:
For defining a logical hierarchy, the below error message is thrown within the Administration Tool:
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:
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:
Usage Tracking is back with OAC
the following article shows how to enable Usage Tracking with OAC
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)
Official press release:
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:
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.
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:
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:
Destination:Insight Oracle Analytics TV
Oracle Analytics Interactive Demo
Accenture’s Digital Boardroom with Oracle Analytics Cloud