After installing Oracle R Enterprise (ORE), set it up for advanced analytics as follows:
- Grant permissions.
% sqlplus / AS SYSDBA
SQL> GRANT RQADMIN to RQUSER;
- Go to P6 and create user with security permissions named rquser. The name of the P6 user needs to match the name used to install ORE.
- Run ETL to get the row level security working.
- Create table etl_aa, which contains the project_object_id for the projects that will be calculated.
CREATE TABLE etl_aa(project_object_id number(19));
- Insert the project_object_id for the selected project (the data below is an example based on sample data).
INSERT INTO etl_aa(project_object_id) values (4351);
INSERT INTO etl_aa(project_object_id) values (4352);
INSERT INTO etl_aa(project_object_id) values (4354);
INSERT INTO etl_aa(project_object_id) values (3577);
INSERT INTO etl_aa(project_object_id) values (4049);
INSERT INTO etl_aa(project_object_id) values (4301);
commit;
- Login as a SYSTEM user grant privileges to RQUSER to access STARUSER.
grant select on staruser.w_project_d to rquser;
grant select on staruser.etl_aa to rquser;
- Install R library glm2. It used in the script.
bash> ORE
> library(ORE)
> ore.connect(user="RQUSER", password="oracle", service_name="pdborcl", all=TRUE)
> install.packages("glm2")
- Create an R object (script under RQUSER).
bash> ORE
> library(ORE)
> ore.connect(user="RQUSER", password="oracle", service_name="pdborcl", all=TRUE)
> ore.scriptCreate(predict_projects ......................... <the full command is the attached file >
- Login as staruser and run the script:
SELECT * FROM table(rqEval(NULL, NULL, 'predict_projects'));
- The prediction results will be stored in RQUSER.ETL_AA_RESULTS.
- Bring RQUSER.ETL_AA_RESULTS to STARUSER:
- Create a database link from STARUSER to RQUSER:
CREATE DATABASE LINK DBLINKR
CONNECT TO RQUSER IDENTIFIED BY oracle
USING 'pdborcl';
- Create a database link from STARUSER to RQUSER:
- Bring over the entire ETL_AA_RESULTS table:
create table etl_aa_results as
select * from etl_aa_results@dblinkr;