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

Advertisements

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

Referencing Oracle BI System Session variables

Oracle BI (OBIEE) provides multiple categories of Variables, such as:

  • Session
  • Repository
  • Presentation
  • Global

These different categories of variables need to be referenced using a specific syntax. However, the required syntax depends on the category of variable and the context. Below are examples for referencing Session variables in Analysis/Reports and within the narrative View. This is often useful for debugging scenarios or for performing unit test when creating complex formulas.

To reference a Session Variable within a Report use: VALUEOF(NQ_SESSION.GROUP) like below:

image

To reference a Session Variable within a Narrative View use: @{biServer.variables[‘NQ_SESSION.GROUP’]} like below:

image

The complete code to reference the different Session variables (User, Group, Roles) is as follows:


[b]Derzeitiger User:[/b] @{biServer.variables['NQ_SESSION.USER']} [br/][br/]
[b]Derzeitiger Anzeigename:[/b] @{biServer.variables['NQ_SESSION.DISPLAYNAME']} [br/][br/]
[b]Derzeitiger Gruppen:[/b] @{biServer.variables['NQ_SESSION.GROUP']} [br/][br/]
[b]Derzeitiger Rollen:[/b] @{biServer.variables['NQ_SESSION.ROLES']} [br/]

For a exessive list of available Session variables check:

For general Overview check:

For short reference card check:

For Official Documentation check (11g):

Reference variable expressions within Oracle BI Answers

Variables such as System or Session can be referenced witin Oracle BI Answers. To reference a variable with a expression, the following syntax is available for referencing the Login User Name:


@{user.displayName}{default}

@{biServer.variables['NQ_SESSION.USER']}

This can be used within a Narrative View to display information about the current User or Session:

image

The following can be used as example:


[b]User Name:[/b] @{user.displayName}{default} <BR>
[b]User Name:[/b] @{biServer.variables['NQ_SESSION.USER']} <BR>
[b]Roles:[/b] @{biServer.variables['NQ_SESSION.ROLES']} <BR>
[b]Group:[/b] @{biServer.variables['NQ_SESSION.GROUP']} <BR>
[b]Group:[/b] @{biServer.variables['NQ_SESSION.WEBGROUPS']} <BR>

image

For further reference, check the official Documentation:

Caching in a High Availibility Cluster

As described in a previous article, the Oracle BI Server maintains a cache entry to improve the response time for further Analysis, which can be satisfied with the cached result. The cached result itself is a binary file, ending with “.tbl”.

Within a clustered Oracle BI environment, a Global Cache can be configured on a shared storage, but each Oracle BI instances will maintain it’s own “local” cached result.
For “regular” BI Answers and Dashboard usage, the cached results will not be propagated to other instances within the cluster. Only when using Oracle BI Agents with the cache seed as target, will be propagated from one instances to another.

Within the Official Documentation the following diagram is shown (e.g. for Version 12.2.1.2) to describe the Global Cache (path to be configured within Oracle BI Enterprise Manager) and the synchronization / propagation of cached results within the Cluster:

image

However, this only applies to Analysis run using Agents and with the target for seeding the Cache:

image

The propagation of cached results will be working for Reports and Dashboard Pages.

Once, an Agent has run to seed the Cache, the cached result (.tbl) file will be placed (propagated/pushed) to the Global Cache (on the shared storage) and other BI instances within the cluster will pull the cached result into their own local cache directory to satisfy further Analysis based on their polling intervals. For regular BI Answer requests, these caching files (.tbl) will not be placed (pushed) on the Global Cache (shared storage), nor will the other BI Servers nodes pull the cache file!

Example Scenario:

Thus, a Business User may be logged into instances 1 and based on his Analysis a cached entry will be created for further Analysis. However, this cached result will not be copied (or propagated) from instances 1 to instances 2. Hence, the same User may log into the system later that day and will be logged into instances 2 and will not received the previously cached result from instances 1. Only if the Analysis has been seeded via a Agent, the cache will be copied and propagated between both instances to satisfy the e.g. second Analysis.

This is also documented with the following Technote to correct/precise the Official Documentation:

Disabling the Query Cache for BICS

Similar to OBIEE the Query Cache can be disabled on Report Level within BICS.

However, the approach is slightly different to OBIEE, since BICS doesn’t provide the Pre or Post-fix box on the Advanced Tab of the Report Builder (Answers).

The below example shows the configuration and Behavior of disabling the Query Cache in a Step-by-step approach:

When creating a (simple) Query, a Cache entry is created for the returned result set:

image

For any subsequent Report, the Cache is analyzed if it can be used to fulfill the subsequent Report. E.g. when adding a Filter for Channel “Catalog”, the above Result set could be used:

image

Giving the following Result:

image

When analyzing the Query log, the Cache hit is also shown in the log itself:

image

In order to bypass the Query Cache, the Query Cache needs to be disabled for the respective Report by adding the following command (setting of internal server variable) into the Report:

SET VARIABLE DISABLE_CACHE_HIT=1;

To do this, the SQL displayed on the Advanced Tab of the Report Builder (Answers) needs to be copied to the clipboard and clicking the new Analysis button.

image

The previously copied SQL needs to be pasted in the popup Windows with the Disable Cache Hit command as prefix and the 0 needs to be removed from the pasted SQL:

image

The Presentation Server Cache can also by bypassed by checking the check-box at the top of the screen. When navigating to the Results tab of Answers, the Report is run again and the Query log can be checked again:

image

The Query log also shows that now the Query is not retrieved from the Cache, but retrieved from the Database.

A Technote also exist for further information on Oracle Support.

Understanding Oracle BI Server Cache

To enable the Oracle BI Server Cache, the Cache needs to be activated within the Oracle Enterprise Manager:
Unbenannt

Additionally, the NQSConfig file contains additional parameter to steer the entry and usage of the Oracle BI Server Cache:
Unbenannt6

If the Cache is enabled, the created Cache entries can be viewed on the hard disk, but also the BI Admin Tool. The Cache files are placed in:

C:\oracle\middleware\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\cache

Unbenannt5

These Cache entries hold the information about the logical SQL used to created the Cache and the returned result set for the query. The Cache Entries can also be viewed from the BI Admin Tool to provide further information about the respective Cache Entry and e.g. the last usage:

Unbenannt7

Some Reports/Analysis may not qualify for a creation of a Oracle BI Server Cache entry. This can also be seen in the Session log, with the following statement:

The logical query block fail to hits or seed the cache in subrequest level due to [[ only one subrequest ]]

In general, this is due to one of the following reasons given from the Official Documentation:

  • Noncacheable SQL element. If a SQL request contains CURRENT_TIMESTAMP, CURRENT_TIME, RAND, POPULATE, or a parameter marker, then it is not added to the cache.
  • Noncacheable table. Physical tables in the Oracle BI Server repository can be marked “noncacheable.” If a query references any noncacheable table, then the query results are not added to the cache.
  • Cache hit. In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache.
  • The exception is query hits that are aggregate roll-up hits. These are added to the cache if the NQSConfig.INI parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to YES.
  • Result set is too big.This situation occurs when you exceed the size set in DATA_STORAGE_PATHS, or if you have rows in excess of the number set in MAX_ROWS_PER_CACHE_ENTRY. See Section A.3.2, “DATA_STORAGE_PATHS” and Section A.3.3, “MAX_ROWS_PER_CACHE_ENTRY” for more information.
  • Query is canceled. This can happen by explicit cancellation from Oracle BI Presentation Services or the Administration Tool, or implicitly through timeout.
  • Oracle BI Server is clustered. Queries that fall into the ‘cache seeding’ family are propagated throughout the cluster. Other queries continue to be stored locally. Therefore, even though a query might be put into the cache on Oracle BI Server node 1, it might not be on Oracle BI Server node 2.

Additionally, one of the following cases may also prevent from entries being created within the Cache:

  • Caching is not enabled (CACHE=N in the NQSConfig.INI file)
  • The WHERE clause in the logical SQL is not semantically the same, or not a logical subset of a cached statement
  • the columns in the SELECT list not yet exist in the cached query, or they are not able to be calculated from them
  • It has not equivalent join conditions, so that the resultant joined table of any incoming query is not the same as (or a subset of) the cached results
  • If DISTINCT is used, the cached copy has to use this attribute as well
  • Aggregation levels are not compatible, since they either have to be the same or more aggregated than the cached query
  • No further aggregation (for example, RANK,MAX) can be used in the incoming query
    (sub request / sub Report)
  • Any ORDER BY clause has to use columns that are also in the cached SELECT list

As an example, the following (main) Report:

cache main report

Is based on the results of another Analysis, which also uses an Aggregation:

cache sub report

The main Report is executed, but no Cache Entry is created:

cache monitor

This can also be analyzed from the Session log for the SQL for this statement:

session log

Since, the Sub Report also contains an Aggregation, no Cache Entry will be created for the Report. This can be seen in the Session log (may be required to set log level to 3 or even 4 to see below message):

cache message

To allow the Oracle BI Server to create a Cache Entry, the Analysis needs to be re-written. E.g. the Sub Report needs to be removed and needs to be part of the (main) Report. In the above case, by creating the Aggregate in the (main) Report and by the “BY” clause within the formula to specify the Group element.

Once, a Cache Entry is created for one User, it may also be shared to fulfill a request from another User. This can be seen from the Oracle BI Admin Tool within the Cache Manager Screen:

cache usage

Sometimes Users are also mislead in terms of Cache usage since Oracle BI provides two cache systems:

And even if the Oracle BI Presentation Server Cache is not enabled (by default not enabled) the Oracle BI Presentation Server still has a Cursor Cache which can be viewed from the Session Monitor (http://localhost:9704/analytics/saw.dll?Sessions) and which can not be disabled and only purged manually from the Session Monitor “Cancel All Cursor” Button.

Understanding Oracle BI EE Presentation Server Cache(s)

Within the Oracle BI System, multiple components provide a caching mechanism to re-use previously executed Reports to improve query performance of subsequent requests. The following diagram provides an Overview of the main Architecture and main caching components:

Oracle BI Server Cacheing cropped

By default, the Oracle BI Presentation Server Cache is not actived. However, the Oracle BI Presentation Server additionally provides a Oracle BI Presentation Server Cache which can not be deactived. This “Cursor” Cache can (only) be viewed from the Session Monitor Administration Page http://localhost:9704/analytics/saw.dll?Sessions:

session monitor

Thus, even when the Oracle BI Presentation Server Cache is not actived, subsequent execution of the same Analysis maybe retrieved from the Oracle BI Presentation Server Cursor Cache which may often mislead the perception of cacheing.

For the first execution of an Analysis a new Cursor and Cursor Cache maybe created eventually:

cache monitor

A subsequent execution of the same Report will return instantly (even with no Presentation Server Cache configured) since the Report can be retrieved from the Presentation Server Cursor Cache. No additional Cursor will be created and only the time of execution will be updated for the previous Cursor.

cache monitor 2

As said before, the Cursor Cache can not be disabled and the Cursor Cache can only be purged manually from the Session Monitor. However, a Report/Analysis can be foreced to bypass the Presentation Server Cursor Cache (and Presentation Server Cache) by checking the bypass Presentation Server Cache option on the Advanced Tab of a Analysis.

bypass

The disk directory of the Cursor Cache is not documented by Oracle, but they are stored under the query cache folders of:

C:\oracle\middleware\instances\instance1\tmp\OracleBIPresentationServicesComponent\coreapplication_obips1\querycache_0

Unbenannt2

Which places a result set file for the respective Presentation Server Cursor Cache:

Unbenannt4

To active the regular Presentation Server Cache, the instanceconfig.xml needs to be edited. The instance config can typically found here (for 11g):

C:\oracle\middleware\instances\instance1\config\OracleBIPresentationServerComponent\coreapplication_obips1

To active the Caching the following xml elements need to be added to the file:

<ServerInstance>
  <Cache>
    <Query>
      <MaxEntries>100</MaxEntries>
      <MaxExpireMinutes>60</MaxExpireMinutes>
      <MinExpireMinutes>10</MinExpireMinutes>
      <MinUserExpireMinutes>10</MinUserExpireMinutes>
    </Query>
  </Cache>
<ServerInstance>

After restarting the Presentation Server, the Cache Files will be created in the following directory:

C:\oracle\middleware\instances\instance1\tmp\OracleBIPresentationServicesComponent\coreapplication_obips1\obis_temp

Summary: Even with no Presentation Server Caching enabled, the Presentation Server Cursor Cache will cache result sets to fulfill subsequent Analysis requests from the Client.