cancel
Showing results for
Did you mean:

Community Team Member
ARIMA Model Forecasting
ARIMA Model Forecasting Mean and Upper 95% Limit

## Overview

In this article, we tackle how to produce a robust forecast by using the Sisense and R integration. This use-case specifically focuses on a scenario where we only have data up until today.

#### Understanding the Nuance of R

For forecasting functionality, we must consider this law of the R integration:
The length of the vector of data we pass to R, determined by the X-axis, equals the vector of data we receive from R.
Length of vector out (n-out) = Vector In (n-in)
This raises a question, however:
Doesn’t a forecast return additional days to what we have in the X axis, and therefor n-in > n-out?

## High-Level Procedure

1. Data Modeling
1. Creating records for future dates
2. R Script Modification
1. Modifying the R script to slice the n-in vector to future and past components
2. Running the forecast on past values and returning forecasted values
3. Concatenating past and forecasted values for returning to Sisense

## Data Modeling Procedure

1. Create a csv or excel document with a single column called “nums” and values 1 – n. n represents how many days out you will forecast. We’ll call this document “extraDays”
2. Create a custom SQL table and create the following expression
`SELECT *, ‘base’ as type FROM “factData”UNION ALLSELECT * FROM (   SELECT ADDDAYS(a.Date, b.nums) AS newDate, -1 as Units, Company, ‘forecast’ as typeFROM (       SELECT   Company, MAX(Date) AS maxDate   FROM FactData   GROUP BY Company) aCROSS JOIN extraDays b) z`

## R Script Modification

With our data modeling done we will have a full vector of data incoming to R from Sisense. This data will include valuable historical data and temporary dummy future values, in the case above, -1. We will handle slicing the data to represent past values and future values with R vector slicing.
One convenient way to control how to select historical values is by creating a function that will accept the vector of records we run our analysis on, and another value that specifies how many days out we will forecast (matching the n values in the excel document)
`module <- function(fullArgs, forecastDays=30, frq=30){#function body}`
We will use the forecastDays variable for both setting our forecast and slicing our vector. This can be done using the following syntax:
`# Start of functiontruncArgs <- fullArgs[1:(length(fullArgs)-forecastDays)]`
We will run our analysis on a dataframe we build off truncArgs:
`df3<-data.frame(fcastValues=truncArgs)`
In this example we are running an ARIMA model and returning 30 days:
`mySeries = ts(df3[, c(‘fcastValues’)])library('forecast')tsSeries = ts(na.omit(mySeries), frequency=frq)decomp = stl(tsSeries, s.window="periodic")deseasonal_cnt <- seasadj(decomp)fit_w_seasonality = auto.arima(deseasonal_cnt, seasonal=TRUE)seas_fcast <- forecast(fit_w_seasonality, h=forecastDays)futureValues <- seas_fcast[["mean"]]`
We will combine our original truncArgs with our future values into a single vector and return this vector back to Sisense. Doing this ensures we maintain the Length of vector out (n-out) = Vector In (n-in) rule.
`resultSet <- c(truncArgs, Sisense_Result)return(resultSet)# End of function`
Save the R script on the server for it to be called during the widget query.

## Writing the Sisense Function

In Sisense we simply use the RDouble function and pass in the R function arguments as expected. In the R function defined above we included default values for arguments, so we do not need to include them as they default to 30.
`RDOUBLE(True,"save(args, file='C:/Users/user/Documents/Rlog/log.txt');source('C:/Users/user/Documents/Sisense/Rscript.R'); module (args[[1]]);",  [Average Units])`
We created a column in our data, so we can now use it to differentiate the forecast from past values by using the “break by” in the widget editor. This is how we achieved the green to orange shift in the first example picture.
Key Words
Forecast, Seasonal Forecast, Seasonal Decomposition, ARIMA forecasting, Forecasting with Sisense, Advanced BI Forecasting
Version history
Last update:
‎03-02-2023 08:27 AM
Updated by:
Contributors
Community Toolbox