Using Forecast Function in Oracle DV / OBIEE 12c

Oracle DV and OBIEE 12c offer a right click interaction to include forecast data in many visualizations.

But it also let users manually configure and edit Forecast functions as custom calculations. This Forecast function is based on a R script ran by Oracle BI Server, and comes with various options and parameters enabled. For example, it allows to define the number of periods to forecast, the type of forecasting model to use (Arima, ETS) and what specific parameters for this model (error type, seasonality, trending, Box Cox, Damping...) as well as what output data to produce (value, confidence levels high and low bounds). All these options combine in a range of various possibilities for calculating the most appropriate forecast information for the use case.

A free pre-built DV Destkop project showing various combinations of these options has been posted on the Oracle Technology Network Dataviz example page here (scroll to project example 'Forecast Syntax Examples') and this YT video gives a very brief introduction of how to use this function :

The FORECAST() Logical SQL Function takes a measure parameter e.g. revenue and a variable list of time dimensions. Optional column aliases can also be used. The default FORECAST XML (filerepo://obiee.TimeSeriesForecast.xml) script file on your server or laptop can be overridden by specifying a new one in the options string. 
Syntax : FORECAST( <measure_expr>, (<time_dimension_expr>), <column_name> , <options>, [<runtime_binded_options>])

- measure_expr represents the measure, e.g. revenue data to forecast.
- time_dimension_expr the time dimension to forecast. One or more columns may be provided.
- column_name represents the output column name for forecast.
- options is a string list of name/value pairs separated by ';'

Option Name
numPeriods	the number of periods to forecast	Integer
predictionInterval	the confidence for the prediction	Integer (1 to 99)
modelType	the model to use for forecasting	ARIMA, ETS
useBoxCox	if TRUE use box cox transformation	TRUE, FALSE
lambdaValue	the Box-Cox transformation parameter. Ignored if NULL or FALSE.	TRUE, FALSE
trendDamp	(ETS model). if TRUE, use damped trend, ie reduce effect of recent trends.	TRUE, FALSE
errorType	(ETS models) : controls how the nearest prior periods are weighted in the output	additive('A'), multiplicative('M'), automatic('Z')
trendType	(ETS models) : controls how the effect of trend is modeled in the output	None('N'), additive('A'), multiplicative('M'), automatic('Z')
seasonType	(ETS models) : controls how seasonal effects are affecting the model outputs.	None('N'), additive('A'), multiplicative('M'), automatic('Z')
modelParamIC	Information criterion to be used in comparing and selecting different models and select the best model.	'ic_auto', 'ic_aicc', (corrected Akaike IC),'ic_bic‘ (Bayesian IC), 'ic_auto'(default)

- runtime_binded_options is an optional comma separated list of runtime binded colums and options.


Some Examples from the DVD Project :
Selecting prediction confidence interval, and showing low end and high end bounds

ETS vs ARIMA : what are the differences ?
                             

Playing with ETS Trending and Seasonality parameters, what does it mean :

Find out more by downloading the examples from our public page here (scroll to project example 'Forecast Syntax Examples').

Are you an Oracle Analytics customer or user?

We want to hear your story!

Please voice your experience and provide feedback with a quick product review for Oracle Analytics Cloud!