7 Installing R and Oracle R Enterprise for External Logical SQL Functions

The external Logical SQL functions such as EVALUATE_SCRIPT, FORECAST, and CLUSTER feed input data to the standalone R-process or to Oracle R Enterprise. Therefore, to create analyses that include these functions, you must install either the R or Oracle R Enterprise external engine in your environment.

R is a widely used environment for statistical computing and graphics and can be used with many different data sources including external files or databases. Oracle R Enterprise is installed specifically for use with the Oracle Database, and makes the open source R statistical programming language and environment ready for use by Oracle BI EE.

See Advanced Analytics External Logical SQL Functions in User's Guide for Oracle Business Intelligence Enterprise Edition for more information about using these functions in analyses.

7.1 Installing R and R Packages

To create analyses using the advanced analytics external Logical SQL functions, you must install R and the required R packages.

If you are using Oracle Database, then see Installing Oracle R Enterprise and Required R Packages on the Oracle Database for more information.

7.1.1 Before You Begin the Installation

You need to install the 3.1.1 R version distributed with Oracle BI.

You can find the R installer in the following Oracle BI environment location:

ORACLE_HOME/bi/bifoundation/advanced_analytics/r-installer.zip

The distributed R installation supports Linux (OLE 6 and OLE 7) and Windows (7 and 8).

7.1.2 Installing R and R Packages on Linux Platforms

Use the procedures in this section to install R and the R packages on Linux platforms.

See Before You Begin the Installation for general prerequisite information.

Before you perform the installation, note the following important information and required tasks:

  • Run RInstaller.sh as root or using the sudo command. See the README.txt that is included in r-installer.zip for more information.

  • Locate proxy.txt in the RInstaller directory and edit it to include the proxy server details.

  • For OLE 7, before you install the Oracle R distribution, you need to install the TexLive and TexInfo RPMs.

    • The required RPM versions are: texlive-epsf-svn21461.2.7.4-32.el7.noarch.rpm and texinfo-tex-5.1-4.el7.x86_64.rpm.

    • Download the RPMs and install them using rpm -ivh <rpm_name>.

    • You must install the RPMs in this specific order: texlive and then texinfo.

To Install R on Linux Platforms:

Note:

You must run the RInstaller in the following operation as a Root user.

Open a command line, navigate to the installer's location, and enter the following to decompress and run the distributed R installer:

bash-4.1$ unzip r-installer.zip 
bash-4.1$ cd RInstaller
bash-4.1$ ./RInstaller.sh
Usage ./RInstaller { install | installpackages | uninstall }
bash-4.1$ sudo ./RInstaller.sh install

To Install R Packages on Linux Platforms

For Linux platforms, after you have installed R, then from the same command line, run the following command to download and install the required R packages (forecast, randomForest, RJSONIO, and matrixcalc). Running this command also installs the OBIEEAdvancedAnalytics R package. The installer uses the proxy information from proxy.txt to download the packages from CRAN.

bash-4.1$ sudo ./RInstaller.sh installpackages

7.1.3 Installing R and R Packages on Windows

Use the procedures in this section to install R and the R packages on Windows.

See Before You Begin the Installation for general prerequisite information.

Before you perform the installation, locate the proxy.txt file in the RInstaller directory and edit it to include the proxy server details.

7.1.3.1 To Install R on Windows:

  1. Locate and open NQSConfig.INI. In the ADVANCE_ANALYTICS_SCRIPT section, update the R_EXECUTABLE_PATH property to point to the R executable path. For example:

    R_EXECUTABLE_PATH = "C:/Program Files/R/R-3.1.1/bin/x64/R";

  2. Using a zip utility, decompress r-installer.zip.
  3. If you have not already done so, then go to the RInstaller directory, locate proxy.txt, and edit it to include the proxy server details.
  4. To run the installer, go to the RInstaller directory where you unzipped r-installer.zip, locate and then run ./Rinstaller.bat install in a command-line session.

7.1.3.2 To Install R Packages on Windows:

After you have installed R, then from the same command line, run the following command to download and install the required R packages (forecast, randomForest, RJSONIO, and matrixcalc). Running this command also installs the OBIEEAdvancedAnalytics R package.

.\RInstaller.bat installpackages

7.2 Installing Oracle R Enterprise and Required R Packages on the Oracle Database

Oracle BI EE uses the R engine included in Oracle R Enterprise instead of R. Oracle BI EE can use the Oracle R Enterprise colocation option, where the data can reside in the Oracle R Enterprise database. (In the non-colocation option, the data does not reside in the Oracle R Enterprise database.)

See Before You Begin the Installation. If you are using databases other than the Oracle Database, then see Installing R and R Packages.

7.2.1 Before You Begin the Installation

Oracle BI EE requires that you install Oracle R Enterprise version 1.4 or 1.4.1.

Table 7-1 ORE Configuration and Support Matrix

Oracle R Enterprise Version Oracle R Enterprise Distributed R Version Oracle Database Version

1.4

3.0.1

3.1.1

11.2.0.3

11.2.0.4

12.1.0.1

1.4.1

3.0.1

3.1.1

11.2.0.3

11.2.0.4

12.1.0.1

7.2.2 Installing Oracle R Enterprise and R Packages

Use the procedures in this section to install Oracle R Enterprise 1.4.1 on the Oracle database and to install the required R packages on Oracle R Enterprise.

To Install Oracle R Enterprise on the Database

A sample user such as ruser or rqueser is used for R Execution on the database.

To install Oracle R Enterprise 1.4.1 on the database, follow the installation instructions in Oracle R Enterprise Installation and Administration Guide, Release 1.4.1.

For information about the steps in a typical installation of Oracle R Enterprise on a Linux server and a Windows client, see A Sample Installation of Oracle R Enterprise in Oracle R Enterprise Installation and Administration Guide.

To Install R Packages on Oracle R Enterprise

  1. After you have installed Oracle R Enterprise, locate and copy r-installer.zip onto the Oracle R Enterprise host.
  2. Decompress the r-installer.zip.
  3. If you have not already done so, then go to the RInstaller directory, locate proxy.txt, and edit it to include the proxy server details.
  4. Open a command line, and as an Oracle user, run the following command to install the required R packages (forecast, randomForest, RJSONIO, and matrixcalc).
    NEW_ORACLE_HOME/bin/ORE -f r-package-install.R

7.2.3 Configuring Oracle R Enterprise to Work with Oracle BI EE

After you have installed Oracle R Enterprise and the R Packages, the next step is to configure Oracle R Enterprise.

To Configure Oracle R Enterprise

  1. Open your instance of Oracle BI Presentation Services and click Administration. In the Administration page, click Issue SQL. The Issue SQL dialog displays.
  2. In the Issue SQL dialog, enter and issue the following stored procedures:
    call NQSRegisterScript('filerepo://obiee.Clustering.xml')
    call NQSRegisterScript('filerepo://obiee.RegressionCreateModel.xml')
    call NQSRegisterScript('filerepo://obiee.RegressionScoreModel.xml')
    call NQSRegisterScript('filerepo://obiee.Outliers.xml')
    call NQSRegisterScript('filerepo://obiee.Regression.xml')
    call NQSRegisterScript('filerepo://obiee.RegressionCorrelation.xml')
    call NQSRegisterScript('filerepo://obiee.TimeSeriesForecast.xml')
    
  3. Locate and open NQSConfig.INI and in the ADVANCED_ANALYTICS_SCRIPT section make the following updates:
    • In the CONNECTION_POOL property, specify the name of the connection pool to be defined in the repository.

      For example, suppose the database name is ORE and the connection pool name is ORECP. Then in the CONNECTION_POOL property, you enter:

      ORE.ORECP

      Note:

      The schema user defined in the connection pool ORECP should have RQADMIN privileges which can be granted via GRANT RQADMIN TO <SCHEMA_USER>.

    • If the ORE database and the source data are located in the same database, then you can improve the speed of the R execution by setting the CONNECTION_POOL property to the source database connection pool.

    • In the TARGET property, set the value to "ORE". For example, TARGET="ORE".

  4. Test the configuration by running an Oracle BI Server query. For example:
    SELECT 
    "A - Sample Sales"."Time"."T02 Per Name Month" Month1,
    "A - Sample Sales"."Base Facts"."1- Revenue" target,
    FORECAST("A - Sample Sales"."Base Facts"."1- Revenue", ("A - Sample
    Sales"."Time"."T02 Per Name Month" timeMonth),
    'forecast','numPeriods=7;predictionInterval=90;) 
    FROM "A - Sample Sales" order by 1;