Tuesday, July 5, 2016

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').

1 comment:

Gihan Srimal said...

I got this error after configured R.

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:OI2DL65P


State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)




State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. Error(s): Error in obiee.Forecast.timeDimSeq(dat, numPeriods, predictionLevel, modelType, : could not find function "forecast" (HY000)




SQL Issued: SELECT 0 s_0, "Sample Sales Lite"."Time"."Per Name Month" s_1, "Sample Sales Lite"."Base Facts"."Revenue" s_2, FORECAST("Sample Sales Lite"."Base Facts"."Revenue",("Sample Sales Lite"."Time"."Per Name Month"),'forecast','modelType=arima;numPeriods=%1;predictionInterval=70;',5) s_3 FROM "Sample Sales Lite" FETCH FIRST 65001 ROWS ONLY

Please help me to solve this if you can.

Post a Comment