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