BI Publisher 11g + R (time series forecasting)

Всем привет!
Сегодня наконец появилось графоманское настроение и я хочу описать решение по использованию среды вычислений R в Oracle BI.
Думаю, не нужно говорить, что из себя представляет R.
Очевидно, что это крутая штука – раз компания Oracle включила ее в платную опцию Advanced Analytics (Oracle R Enterprise).
Крутая она хотя бы потому, что мировым научным сообществом под нее пишется огромное количество бесплатных библиотек, позволяющих анализировать ваши данные. Так в данном сообщении я продемонстрирую использование библиотеки forecast, позволяющей строить прогнозные модели данных временных рядов.
Библиотеку разработал и поддерживает профессор Rob Hyndman

Хочу заметить, что опция Oracle Advanced Analytics платная.
Да, она обеспечивает тесную интеграцию ваших данных (внутри БД Oracle) и вычислительного движка R, что существенно ускоряет обработку действительно больших объемов данных.
Но что если данных, которые нужно обрабатывать, у вас не очень немного. Или денег на оплату опции жалко…
В этом случае можно вполне обойтись интеграцией R-среды и вашей БД аналогично приводимому далее примеру.

А тут будет картинка для привлечения внимания.
А именно – результат работы в виде отчета Oracle BI Publisher, отображающего прогноз среднемесячной температуры на основании накопленной статистики температур.



Основная идея данного примера в том, что внутри БД Oracle с помощью PL/SQL пакета формируется файл с инструкциями R в файловой системе сервера БД;
затем с помощью Java Stored Procedure вызывается на выполнение утилита RScript с параметром-созданным файлом;
среди инструкций R внутри файла содержатся как обращения к БД с помощью специальной библиотеки ROracle,
так и логика по генерации прогнозных значений с помощью библиотеки forecast;
сгенерированные и записанные обратно в БД прогнозные данные визуализируются в Oracle BI Publisher.

Разобью пример на следующие шаги:
1) Загрузка в БД Oracle данных о среднемесячных температурах по регионам России.
2) Установка инструментальной среды R на сервер с БД Oracle. Инсталляция необходимых библиотек.
3) Создание R-скрипта, считывающего данные из БД, генерирующего прогноз и отображающего сформированные данные в виде графика.
4) Создание PL/SQL пакета, формирующего R-скрипт как файл в файловой системе сервера БД, запускающего R-скрипт.
5) Создание отчета в Oracle BI Publisher, визуализирующего сформированные R-скриптом прогнозы.

Документирую все шаги. Сообщение получается длинным, поэтому некоторые части текста скрыты спойлерами.

Загрузка в БД Oracle данных о среднемесячных температурах по регионам России

Открыть спойлер

1) Скачаем с сайта http://neacc.meteoinfo.ru/monitoringdata/7-gcos-stations
данные о среднемесячных температурах.
2) Откроем Excel (у меня стоит 2007 версия) и на вкладке "Данные" выберем импорт "Из текста".
В открывшемся диалоговом окне укажем путь до загруженного файла.


3) Импортируем данные в книгу Excel.





4) Создадим в БД Oracle таблицу для хранения загруженных данных

create table TEMPERATURE_DATA 
(
  observer_num varchar2(10), 
  year_num number,
  ind_1 number,
  ind_2 number,
  ind_3 number,
  ind_4 number,
  ind_5 number,
  ind_6 number,
  ind_7 number,
  ind_8 number,
  ind_9 number,
  ind_10 number,
  ind_11 number,
  ind_12 number
)

5) Вставим в ячейку O1 следующее значение:

=СЦЕПИТЬ("insert into TEMPERATURE_DATA(observer_num,year_num,ind_1,ind_2,ind_3,ind_4,ind_5,ind_6,ind_7,ind_8,ind_9,ind_10,ind_11,ind_12) values ('";A1;"','";B1;"','";C1;"','";D1;"','";E1;"','";F1;"','";G1;"','";H1;"','";I1;"','";J1;"','";K1;"','";L1;"','";M1;"','";N1;"');"

И протянем эту ячейку вниз


6) Копируем значения из столбца O (лучше автофильтром ограничить выборку по непустым значениям столбца А) и сохраняем в виде SQL-скрипта.
В начале скрипта следует указать:

alter session set NLS_NUMERIC_CHARACTERS = '.,';

В конце:

Commit;

7) Запускаем SQL-скрипт. Тем самым достаточно просто заливаем внешние текстовые данные в БД (Денис, если ты это читаешь – спасибо за эту идею, часто использую ;) )

Установка инструментальной среды R на сервер с БД Oracle. Инсталляция необходимых библиотек

1) Скачиваем с http://cran.rstudio.com/ дистрибутив с инструментальной средой R и базовым набором библиотек.
У меня используется сервер БД Oracle с операционной системой Windows.
Поэтому я скачиваю дистрибутив под Windows.

2) Установим R учитывая битность платформы


3) Откроем установленную инструментальную среду R.
Как я уже говорил, ценность этого инструмента во многом определяется наличием огромного количества библиотек.
Загрузим две, которые потребуются нам в работе: forecast и ROracle
Сделать это можно как напрямую в среде R с помощью команды

install.packages("forecast")

Тогда потребуется указать зеркало, с которого будет производиться закачка библиотеки.
Очевидно, что данный метод предполагает наличие интернет соединения на сервере.


Если же доступа к интернету напрямую с сервера нет, то можно выкачать нужные пакеты и инсталлировать их вручную:
скачать zip-архив с пакетом-библиотекой (например, Windows binary пакета forecast с сайта http://robjhyndman.com/software/forecast/
и загрузить с помощью среды R через пункт меню "Пакеты" – "Установить пакеты из локальных zip-файлов")

Сложнее будет процесс установки пакета ROracle – данная библиотека нужна нам для получения и записи данных БД напрямую из R.
a) Скачаем дистрибутив пакета с http://cran.r-project.org/web/packages/ROracle/index.html
b) Изучаем инструкцию по инсталляции
c) Устанавливаем библиотеку DBI


Согласно пункту инструкции по установке "Windows - Oracle Client (Oracle home based client)" создаем
переменные окружения OCI_INC и OCI_LIB64 (либо OCI_LIB32 на 32-битной платформе)
В моем случае это:
OCI_INC = d:\app\product\11.2.0\dbhome_1\OCI\include
OCI_LIB64 = d:\app\product\11.2.0\dbhome_1\BIN

Не забываем проверить, чтобы в переменной окружения PATH был указан путь bin-каталога БД Oracle:
PATH = d:\app\product\11.2.0\dbhome_1\BIN;…
А также чтобы была корректно установлена переменная окружения ORACLE_HOME:
ORACLE_HOME = d:\app\product\11.2.0\dbhome_1

d) Качаем утилиту RTools (версия 3.1) по адресу http://cran.r-project.org/bin/windows/Rtools/ (она позволяет компилировать R-бинарники их исходных кодов, но нам она потребуется для компиляции и корректной установки библиотеки ROracle)
Устанавливаем RTools с параметрами по умолчанию.
По завершению установки добавляем в начало переменной окружения PATH пути до bin-директорий RTools и его компилятора, а также путь до bin-директории самой среды R:
PATH = c:\Program Files\R\R-3.0.3\bin\x64;c:\Rtools\bin;c:\Rtools\gcc-4.6.3\bin;…

e) Открываем командную строку, переходим в каталог с загруженным архивом исходником библиотеки ROracle (в моем случае это C:\TEMP)
И запускаем команду

R CMD INSTALL ROracle_1.1-11.tar.gz

f) Если все сделано правильно, то при выполнении команды в среде R

library(ROracle)

Не будет выдано никаких сообщений об ошибках.


Создание R-скрипта, считывающего данные из БД, генерирующего прогноз и отображающего сформированные данные в виде графика
Теперь мы готовы обратиться из среды R к БД Oracle за данными с помощью библиотеки ROracle,
с помощью библиотеки forecast сгенерировать прогнозные значения и визуализировать их в виде графика в той же среде R.

Для этого скопируем и вставим в окно среды R следующий набор команд:

library(ROracle)
library(forecast)
drv <- dbDriver("Oracle")
con <- dbConnect(drv,"DWH","DWH","ORCL")
rs <- dbSendQuery(con, "
with t as
 (select t.* from TEMPERATURE_DATA t where t.observer_num = 26063 and t.year_num >= 2000)
SELECT measure
  FROM (select t.year_num, 1 as month_num, t.ind_1 as measure
          from t
        union all
        select t.year_num, 2 as month_num, t.ind_2
          from t
        union all
        select t.year_num, 3 as month_num, t.ind_3
          from t
        union all
        select t.year_num, 4 as month_num, t.ind_4
          from t
        union all
        select t.year_num, 5 as month_num, t.ind_5
          from t
        union all
        select t.year_num, 6 as month_num, t.ind_6
          from t
        union all
        select t.year_num, 7 as month_num, t.ind_7
          from t
        union all
        select t.year_num, 8 as month_num, t.ind_8
          from t
        union all
        select t.year_num, 9 as month_num, t.ind_9
          from t
        union all
        select t.year_num, 10 as month_num, t.ind_10
          from t
        union all
        select t.year_num, 11 as month_num, t.ind_11
          from t
        union all
        select t.year_num, 12 as month_num, t.ind_12 from t)
 ORDER BY year_num, month_num
")
data <- fetch(rs)
p_startYear <- 2000
p_startMonth <- 1
p_endYear <- 2008
p_endMonth <- 12
p_numOfPredictedMonths <- 12

dataTS <- ts(data$MEASURE, start=c(p_startYear, p_startMonth), end=c(p_endYear, p_endMonth), frequency=12) 
dataFit <- HoltWinters(dataTS)
forecastFit <- forecast(dataFit, p_numOfPredictedMonths)
plot(forecastFit)

где строка

con <- dbConnect(drv,"DWH","DWH","ORCL")

содержит обращение к tns-записи ORCL из файла tnsnames.ora, соответствующего прилинкованной БД с помощью переменных окружения.


Замечу, что для обратной загрузки данных в Oracle следует выполнить следующие команды R:

forecastData <- data.frame(MEASURE = forecastFit$mean, MONTH_KEY = c(time(forecastFit$mean)) )
dbWriteTable(con, "TEMPERATURE_FORECAST", forecastData, append = TRUE)

Где первая команда генерирует набор данных (таблицу) из 2 столбцов – столбца MEASURE с прогнозными значениями вложенного ряда mean переменной forecastFit и столбца MONTH_KEY.
А вторая команда – загрузка набора данных в таблицу TEMPERATURE_FORECAST по открытому ранее соединению с БД с опцией Append (догрузки).

Создание PL/SQL пакета, формирующего R-скрипт как файл в файловой системе сервера БД, запускающего R-скрипт
Итак, теперь мы умеем из R среды обращаться к БД Oracle за данными, на основании полученных данных строить прогнозы временных рядов
(я специально не углубляюсь в детали библиотеки forecast – на сайте автора очень подробно все документировано) .

Но основной сущностью рассматриваемой здесь интеграции является БД Oracle.
Именно в ней хранятся исходные данные и туда же должны загружаться обработанные с помощью R (прогнозные) данные.
Значит, мы должны уметь из Oracle формировать R-скрипты с нужными наборами команд как файлы в файловой системе,
запускать эти скрипты на выполнение и удалять их по завершению работы.

Для этого я предлагаю использовать следующие пакеты:
PKG_UNLOADER (копия пакета Unloader Тома Кайта)
и
PKG_JAVA_COMMON_TOOLS (набор java stored procedure по java source fndjavafile)

Вот так будет выглядеть PL/SQL-код, генерирующий R-скрипт и запускающий его на выполнение:

declare
  l_dir_path       varchar2(512);
  l_file_separator varchar2(4);
  l_rows           number;

  l_query varchar2(32000);

  l_command varchar2(512);
  l_output  DBMS_OUTPUT.chararr;
  l_lines   INTEGER := 1000;

BEGIN

  --вычислим путь к директории TEMP_DIR
  select DIRECTORY_PATH
    into l_dir_path
    from all_directories
   where DIRECTORY_NAME = 'TEMP_DIR';

  l_file_separator := PKG_JAVA_COMMON_TOOLS.get_file_separator;

  --создадим универсальный R-скрипт для генерации прогнозных значений
  l_query := 'select 
''
library(ROracle)
library(forecast)
drv <- dbDriver("Oracle")
con <- dbConnect(drv,"DWH","DWH","ORCL")
rs <- dbSendQuery(con, "
with t as
 (select t.* from TEMPERATURE_DATA t where t.observer_num = 26063 and t.year_num >= 2000)
SELECT measure
  FROM (select t.year_num, 1 as month_num, t.ind_1 as measure
          from t
        union all
        select t.year_num, 2 as month_num, t.ind_2
          from t
        union all
        select t.year_num, 3 as month_num, t.ind_3
          from t
        union all
        select t.year_num, 4 as month_num, t.ind_4
          from t
        union all
        select t.year_num, 5 as month_num, t.ind_5
          from t
        union all
        select t.year_num, 6 as month_num, t.ind_6
          from t
        union all
        select t.year_num, 7 as month_num, t.ind_7
          from t
        union all
        select t.year_num, 8 as month_num, t.ind_8
          from t
        union all
        select t.year_num, 9 as month_num, t.ind_9
          from t
        union all
        select t.year_num, 10 as month_num, t.ind_10
          from t
        union all
        select t.year_num, 11 as month_num, t.ind_11
          from t
        union all
        select t.year_num, 12 as month_num, t.ind_12 from t)
 ORDER BY year_num, month_num
")
data <- fetch(rs)
p_startYear <- 2000
p_startMonth <- 1
p_endYear <- 2008
p_endMonth <- 12
p_numOfPredictedMonths <- 12

dataTS <- ts(data$MEASURE, start=c(p_startYear, p_startMonth), end=c(p_endYear, p_endMonth), frequency=12) 
dataFit <- HoltWinters(dataTS)
forecastFit <- forecast(dataFit, p_numOfPredictedMonths)
forecastData <- data.frame(MEASURE = forecastFit$mean, MONTH_KEY = c(time(forecastFit$mean)) )
dbWriteTable(con, "TEMPERATURE_FORECAST", forecastData, append = TRUE)
'' as txt from dual';

  l_rows := PKG_UNLOADER.run(p_query      => l_query,
                             p_town       => '',
                             p_tname      => '',
                             p_mode       => 'truncate',
                             p_dbdir      => 'TEMP_DIR',
                             p_filename   => 'R_forecast',
                             p_separator  => '',
                             p_enclosure  => '',
                             p_terminator => '',
                             p_ctl        => 'NO',
                             p_header     => 'NO');

  --вызываем созданный ранее R-скрипт (необходимо включить путь до Rscript в PATH)
  l_command := 'Rscript "' || l_dir_path || l_file_separator || 'R_forecast.dat"';

  PKG_JAVA_COMMON_TOOLS.host_command(l_command);
  --для отладки    
  --DBMS_OUTPUT.get_lines(l_output, l_lines);
  --FOR i IN 1 .. l_lines LOOP
  --  DBMS_OUTPUT.put_line(l_output(i));
  --END LOOP;    

  --удалим R-скрипт
  l_rows := PKG_UNLOADER.remove(p_dbdir    => 'TEMP_DIR',
                                p_filename => 'R_forecast.dat');
end;
/

После его выполнения запрос к таблице с прогнозными значениями возвращает следующие данные:



Создание отчета в Oracle BI Publisher, визуализирующего сформированные R-скриптом прогнозы
Наконец сведем все шаги воедино.
В виде отчета BI Publisher, который с помощью before report триггера будет выполнять PL/SQL код по генерации прогнозных данных;
визуализировать сформированные данные в виде графика;
с помощью after report триггера очищать сформированные данные.

Так как отчет может выполняться параллельно несколькими пользователями – следует разграничивать используемые в нем данные.
Для этого создадим sequence в БД.

create sequence R_FORECAST_SEQ;

Пересоздадим таблицу с результатами (порядок следования столбцов важен!):

create table TEMPERATURE_FORECAST
(
  measure       VARCHAR2(50),
  month_key     NUMBER,
  sess_key      NUMBER,
  observer_num  NUMBER    
)

Также немного усложним отчет – будем одним отчетом генерировать прогнозы для различных станций наблюдений за погодой.
Т.е. параметрами отчета будут станции наблюдения и год начала и окончания наблюдений.

Создадим PL/SQL пакет PKG_R_FORECAST с функциями before_report и after_report.

create or replace package PKG_R_FORECAST as

  P_YEAR_BEG      varchar2(30);
  P_YEAR_END      varchar2(30);
  P_OBSERVER_NUM  varchar2(30);

  function get_sess_key return integer;

  function before_report(P_YEAR_BEG      in varchar2,
                         P_YEAR_END      in varchar2,
                         P_OBSERVER_NUM  in varchar2) return boolean;

  function after_report return boolean;

end PKG_R_FORECAST;
/
create or replace package body PKG_R_FORECAST as

  G_SESS_KEY integer;

  function get_sess_key return integer as
  begin
    return G_SESS_KEY;
  end get_sess_key;

  function before_report(P_YEAR_BEG      in varchar2,
                         P_YEAR_END      in varchar2,
                         P_OBSERVER_NUM  in varchar2) return boolean as  
  
    l_dir_path       varchar2(512);
    l_file_separator varchar2(4);
    l_rows           number;
  
    l_query varchar2(32000);
  
    l_command varchar2(512);
    l_output  DBMS_OUTPUT.chararr;
    l_lines   INTEGER := 1000;
  
  BEGIN
  
    G_SESS_KEY := R_FORECAST_SEQ.Nextval;
  
    --вычислим путь к директории TEMP_DIR
    select DIRECTORY_PATH
      into l_dir_path
      from all_directories
     where DIRECTORY_NAME = 'TEMP_DIR';
  
    l_file_separator := PKG_JAVA_COMMON_TOOLS.get_file_separator;
  
    --создадим универсальный R-скрипт для генерации прогнозных значений
    l_query := 'select 
''
library(ROracle)
library(forecast)
drv <- dbDriver("Oracle")
con <- dbConnect(drv,"DWH","DWH","ORCL")
rs <- dbSendQuery(con, "
with t as
 (select t.* from TEMPERATURE_DATA t 
   where ((coalesce(null, ' || P_OBSERVER_NUM || ') is null) or (t.observer_num in (' || P_OBSERVER_NUM || '))) 
     and t.year_num >= ' || P_YEAR_BEG || '
     and t.year_num <= ' || P_YEAR_END || ')
SELECT observer_num, measure
  FROM (select t.observer_num, t.year_num, 1 as month_num, t.ind_1 as measure
          from t
        union all
        select t.observer_num, t.year_num, 2 as month_num, t.ind_2
          from t
        union all
        select t.observer_num, t.year_num, 3 as month_num, t.ind_3
          from t
        union all
        select t.observer_num, t.year_num, 4 as month_num, t.ind_4
          from t
        union all
        select t.observer_num, t.year_num, 5 as month_num, t.ind_5
          from t
        union all
        select t.observer_num, t.year_num, 6 as month_num, t.ind_6
          from t
        union all
        select t.observer_num, t.year_num, 7 as month_num, t.ind_7
          from t
        union all
        select t.observer_num, t.year_num, 8 as month_num, t.ind_8
          from t
        union all
        select t.observer_num, t.year_num, 9 as month_num, t.ind_9
          from t
        union all
        select t.observer_num, t.year_num, 10 as month_num, t.ind_10
          from t
        union all
        select t.observer_num, t.year_num, 11 as month_num, t.ind_11
          from t
        union all
        select t.observer_num, t.year_num, 12 as month_num, t.ind_12 from t)
 ORDER BY observer_num, year_num, month_num
")
data <- fetch(rs)
p_startYear <- ' || P_YEAR_BEG || '
p_startMonth <- 1
p_endYear <- ' || P_YEAR_END || '
p_endMonth <- 12
p_numOfPredictedMonths <- 12
for (i in 1:length(unique(data$OBSERVER_NUM))) { 
   iterObserver <- unique(data$OBSERVER_NUM)[i]
   iterObserverData <- data[data$OBSERVER_NUM == iterObserver,]
   iterObserverDataTS <- ts(iterObserverData$MEASURE, start=c(p_startYear, p_startMonth), end=c(p_endYear, p_endMonth), frequency=12) 
   iterObserverDataFit <- HoltWinters(iterObserverDataTS)
   iterObserverForecastFit <- forecast(iterObserverDataFit, p_numOfPredictedMonths)
   iterObserverForecastData <- data.frame(MEASURE = iterObserverForecastFit$mean, MONTH_KEY = c(time(iterObserverForecastFit$mean)) )
   iterObserverForecastData["SESS_KEY"] <- ' || G_SESS_KEY || '
   iterObserverForecastData["OBSERVER_NUM"] <- iterObserver
   dbWriteTable(con, "TEMPERATURE_FORECAST", iterObserverForecastData, append = TRUE)
}
'' as txt from dual';
  
    l_rows := PKG_UNLOADER.run(p_query      => l_query,
                               p_town       => '',
                               p_tname      => '',
                               p_mode       => 'truncate',
                               p_dbdir      => 'TEMP_DIR',
                               p_filename   => 'R_forecast_' || G_SESS_KEY,
                               p_separator  => '',
                               p_enclosure  => '',
                               p_terminator => '',
                               p_ctl        => 'NO',
                               p_header     => 'NO');
  
    --вызываем созданный ранее R-скрипт (необходимо включить путь до Rscript в PATH)
    l_command := 'Rscript "' || l_dir_path || l_file_separator ||
                 'R_forecast_' || G_SESS_KEY || '.dat"';
  
    PKG_JAVA_COMMON_TOOLS.host_command(l_command);
    --для отладки    
    --DBMS_OUTPUT.get_lines(l_output, l_lines);
    --FOR i IN 1 .. l_lines LOOP
    --  DBMS_OUTPUT.put_line(l_output(i));
    --END LOOP;    
  
    --удалим R-скрипт
    l_rows := PKG_UNLOADER.remove(p_dbdir    => 'TEMP_DIR',
                                  p_filename => 'R_forecast_' || G_SESS_KEY ||
                                                '.dat');
  
    return true;
  
  END before_report;

  function after_report return boolean as
  
  BEGIN
  
    delete from TEMPERATURE_FORECAST t where t.sess_key = G_SESS_KEY;
  
    COMMIT; --!!!!
  
    return true;
  
  END after_report;

end PKG_R_FORECAST;
/

Создадим сам отчет в BI Publisher




Текст запроса набора данных MAIN:

with t as
 (select t.*
    from TEMPERATURE_DATA t
   where ((coalesce(null, :P_OBSERVER_NUM) is null) or
         (t.observer_num in (:P_OBSERVER_NUM)))
     and t.year_num >= :P_YEAR_BEG
     and t.year_num <= :P_YEAR_END),
t_src as
 (SELECT observer_num,
         year_num || lpad(month_num, 2, '0') as month_key,
         measure
    FROM (select t.observer_num,
                 t.year_num,
                 1              as month_num,
                 t.ind_1        as measure
            from t
          union all
          select t.observer_num, t.year_num, 2 as month_num, t.ind_2
            from t
          union all
          select t.observer_num, t.year_num, 3 as month_num, t.ind_3
            from t
          union all
          select t.observer_num, t.year_num, 4 as month_num, t.ind_4
            from t
          union all
          select t.observer_num, t.year_num, 5 as month_num, t.ind_5
            from t
          union all
          select t.observer_num, t.year_num, 6 as month_num, t.ind_6
            from t
          union all
          select t.observer_num, t.year_num, 7 as month_num, t.ind_7
            from t
          union all
          select t.observer_num, t.year_num, 8 as month_num, t.ind_8
            from t
          union all
          select t.observer_num, t.year_num, 9 as month_num, t.ind_9
            from t
          union all
          select t.observer_num, t.year_num, 10 as month_num, t.ind_10
            from t
          union all
          select t.observer_num, t.year_num, 11 as month_num, t.ind_11
            from t
          union all
          select t.observer_num, t.year_num, 12 as month_num, t.ind_12 from t)),
t_rslt as
 (select f.observer_num,
       trunc(f.month_key) ||
       lpad(round((f.month_key - trunc(f.month_key)) * 12 + 1), 2, '0') as month_key,
       round(to_number(f.measure,
                 '9999999999D99999999999999999999999999999',
                 'NLS_NUMERIC_CHARACTERS=''.,'''),2) as measure
  from TEMPERATURE_FORECAST f
   where f.sess_key = PKG_R_FORECAST.get_sess_key
   )
select s.observer_num, s.month_key, s.measure, 'Source' as measure_type
  from t_src s
union all  
select to_char(r.observer_num),
       r.month_key,
       r.measure,
       'Forecast' as measure_type
  from t_rslt r