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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.