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

Downloading a rpd from OAC Thin Client Modeller

The web modeller or Thin Client Modeller was introduced with BICS and OAC respectively, to allow a more agile metadata modelling. The thin Client modeller does not provide the same level of modelling functionality compared to the “classic” .rpd metadata model. Additionally, with the current release of OAC only one modelling approach is supported. Thus, only the thin client model or the classical model can be deployed as metadata at a time.

Hence, the might be a scenario for switching from the thin client modeller (web modeller) to the classic .rpd model to support more complex metadata modelling.

A .rpd file can be generated from OAC when downloading a Snapshot and extracting the .bar file. The .bar file can be downloaded from the Snapshots area of the OAC Console:

image

When clicking on a option menu right to the Snapshots, the Console allows  to download the snapshot as .bar file. Once the .bar file is downloaded (after assining a password to protect the downloaded file), the file can be opened with a Archive Tool such as 7z.

image

The .rpd data model can be opened like any other classical metadata model using the BI Administration Tool. This will contain system generated Objects and Names:

image

OBIEE: BY clause for group by

Within Oracle BI Answers (and only within BI Answers; not within the rpd metadata model), the BY clause can be used in conjunction with a Aggregate function to specify the level at which the metric should be aggregated (group by).

As an Example, the following Analysis displays the shipped amount per Year and product type:

image

Within certain scenarios, it may be useful to add a level of aggregation at a particular level within a Report. This can be achieved with the BY clause. This clause can be added with a aggregation function such as MIN, MAX, SUM etc. within the BI Answers formula editor:

image

SUM("Base Facts"."7- Shipped Amount" BY "Time"."T05 Per Name Year")

This, creates a group by for the measure at the level of year, regardless of other aggregation levels intended by the Report e.g. at the level of year and produt type:

image

However, this clause should only be used with caution for possible incorrect or unintended results.

Within the .rpd, the concept of Level-Based Measures can be used to achieve the same Results.

differences between OBIEE Admin Tool (rpd) and web modeler

The Oracle BI Cloud Service provides the possibility to upload metadata models (via lift & shift) created with the OBIEE Admin Tool (rpd file), but also comes with a new web modeler to create metadata models directly within the web UI. These web model files are not compatible with the rpd file and are stored internally as json files. The BICS instance is also limited to an either or approach. Once, a rpd is uploaded as a model e.g. containing the data source connections, the web modeler can not be used anymore.

image

Thus, the following article lists the differences between the metadata model created within the rpd and the web modeler:

· Authorization Blocks for populating variables are not available with web modeler

· The web modeler only supports a single Physical Table /View for a logical table (Fact or Dimension) since no LTS (Logical Table Sources) are available.

· Since no LTS is available, it is not possible to use the Content Tab of the LTS to specify a Where Clause to be applied to limit the Rows.

· Since there is no concept for LTS, the Aggregate navigation configuration is not possible within web modeler to make use of aggregated tables which contain fewer records for increasing performance

· after Renaming of Presentation Tables/Columns Aliases are set within rpd, but not in web modeler, hence a Report referencing the column will be invalid

· No renaming wizard in web modeler to quickly change the physical table names into meaningful business names.

· Star schema (ER Diagrams) are not shown when using the web modeler and only within the OBIEE Admin Tool

· Cross Subject area Analysis does only seem to work with rpd lift and shift. When using web modeler the option of “adding other Subject areas” is always greyed out. This influences the modelling of the Models, since it is possible to create a united Model having all required tables. However, this “united” model maybe a duplicate and must be created from scratch again.

· Joins between Fact and Dimension Tables may only be based on a single criteria. Joins can’t be specified using a second criteria using the AND logic. And Joins can only be Inner Joins.

Obviously, the above list is non exhaustive and should only provide a first level information, since any BICS instance is limited to only one approach.