Forecasting in Oracle using the Power of SQL

You have many options for analyzing your data, for example in R. But Oracle Database and SQL can do a lot of directly. So is specialized tools like R or good old SQL preferable? We use Forecasting as an example of an analytic task, showing our experiences with R, ORE and SQL - guess which is faster!

When it comes to performing analytics all we seem to hear about is that we need to use R. But what if we could do it in SQL? Can SQL perform many of the analytical tasks our organisation needs? To borrow a phrase from Barack Obama, “Yes we can”. Yes, we can use SQL to perform many of the analytical techniques our business users and analysts want to perform.

But the biggest challenge is how can you do it. Oracle comes with over 280+ statistical and analytic functions but many of us are only aware of a few or a good few of these.

In this presentation we will present a case study from a real life scenario where R was used to perform forecasting of product sales. When using this approach we encountered many challenges! To overcome these we looked to using the power of SQL to perform the same task. We do so with some surprising results.

Our presentation will bring you on a journey of what we did, what we discovered and why we wanted to share our discoveries with you.

Additionally we will also discuss how you can expand the, already extensive, analytic capabilities of the Oracle Database by running R as part of the Oracle Database using Oracle R Enterprise.

(Joint presentation by Kim Berg Hansen and Brendan Tierney)

The session will talk about and demo:

• Using R with ROracle driver to extract data for time series analysis, do the forecasting and visualization very simply for single time series, returning results to the database, performance problems of doing this in loops for many time series.
• Do time series analysis in SQL using analytic functions, scaling this method by doing single SQL with analytic partition by to perform many time series forecasts in bulk (parallel if need be.)
• Visualizing the SQL forecast via various tools like APEX, Forms, OBIEE, etc.
• How to expand the analytic capabilities of the Oracle Database using Oracle R Enterprise.

(Joint presentation by Kim Berg Hansen and Brendan Tierney)