Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

thepeis’srapODade wt orecte eee onda tTpe ATS SLOG SERLA

    PRAGMA EXCEPTION_INIT(not_found, -40203);
  BEGIN
    dbms_output.put_line('Start Drop RETWSP_CUST_CHURN_MODEL Tables');
    DBMS_DATA_MINING.DROP_MODEL('RETWSP_CUST_CHURN_MODEL');
    dbms_output.put_line('End Drop RETWSP_CUST_CHURN_MODEL Tables');
  EXCEPTION
  WHEN not_found THEN
    dbms_output.put_line('RETWSP_CUST_CHURN_MODEL not found');
  END; ------------- end drop RETWSP_CUST_CHURN_MODEL  --------------------------
  -- CREATE A SETTINGS TABLE
  --
  -- The default classification algorithm is Naive Bayes. In order to override
  -- this, create and populate a settings table to be used as input for
  -- CREATE_MODEL.
  --
  DECLARE ---------- start drop RETWSP_CUST_CHMDL_SETTINGS
    not_found EXCEPTION;
    PRAGMA EXCEPTION_INIT(not_found, -40203);
  BEGIN
    dbms_output.put_line('Start Drop RETWSP_CUST_CHMDL_SETTINGS Tables');
    EXECUTE IMMEDIATE 'DROP TABLE RETWSP_CUST_CHMDL_SETTINGS';
    dbms_output.put_line('End Drop RETWSP_CUST_CHMDL_SETTINGS Tables');
  EXCEPTION
  WHEN not_found THEN
    dbms_output.put_line('RETWSP_CUST_CHMDL_SETTINGS not found');
  END; ------------- end drop RETWSP_CUST_CHMDL_SETTINGS
  DECLARE ---------- start drop RETWSP_CUST_CHMDL_COST
    not_found EXCEPTION;
    PRAGMA EXCEPTION_INIT(not_found, -40203);
  BEGIN
    dbms_output.put_line('Start Drop RETWSP_CUST_CHMDL_COST Tables');
    EXECUTE IMMEDIATE 'DROP TABLE RETWSP_CUST_CHMDL_COST';
    dbms_output.put_line('End Drop RETWSP_CUST_CHMDL_COST Tables');
  EXCEPTION
  WHEN not_found THEN
    dbms_output.put_line('RETWSP_CUST_CHMDL_COST not found');
  END; ------------- end drop RETWSP_CUST_CHMDL_COST
  DECLARE ---------- start create table RETWSP_CUST_CHMDL_SETTINGS
    already_exists EXCEPTION;
    PRAGMA EXCEPTION_INIT(already_exists, -00955);
  BEGIN
    dbms_output.put_line('Start Create RETWSP_CUST_CHMDL_SETTINGS Tables');
    EXECUTE IMMEDIATE 'CREATE TABLE RETWSP_CUST_CHMDL_SETTINGS (
setting_name  VARCHAR2(30),
setting_value VARCHAR2(4000))';
    dbms_output.put_line('End Create RETWSP_CUST_CHMDL_SETTINGS Tables');
  EXCEPTION
  WHEN already_exists THEN
    dbms_output.put_line('Exception not found');
  END; ------------- end create table RETWSP_CUST_CHMDL_SETTINGS
  DECLARE ---------- Create RETWSP_CUST_CHMDL_COST Tables begins
    already_exists EXCEPTION;
    PRAGMA EXCEPTION_INIT(already_exists, -00955);
  BEGIN
    dbms_output.put_line('Start Create RETWSP_CUST_CHMDL_COST Tables');
    EXECUTE IMMEDIATE 'CREATE TABLE RETWSP_CUST_CHMDL_COST (
actual_target_value           NUMBER,
predicted_target_value        NUMBER,
cost                          NUMBER)';
    dbms_output.put_line('End Create RETWSP_CUST_CHMDL_COST Tables');
  EXCEPTION
  WHEN already_exists THEN

    dbms_output.put_line('RETWSP_CUST_CHMDL_COST not found');
  END; ------------- Create RETWSP_CUST_CHMDL_COST Tables ends
  -- CREATE AND POPULATE A COST MATRIX TABLE
  --
  -- A cost matrix is used to influence the weighting of misclassification
  -- during model creation (and scoring).
  -- See Oracle Data Mining Concepts Guide for more details.
  --
  dbms_output.put_line('Start Insert records into RETWSP_CUST_CHMDL_COST');
  DECLARE ---------- sub-block begins
    already_exists EXCEPTION;
    PRAGMA EXCEPTION_INIT(already_exists, -00955);
  BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (0,0,0)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (0,1,1)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (0,2,2)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (0,3,3)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (1,0,1)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (1,1,0)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (1,2,2)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (1,3,3)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (2,0,3)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (2,1,2)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (2,2,0)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (2,3,1)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (3,0,3)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (3,1,2)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (3,2,1)';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_COST VALUES (3,3,0)';
    dbms_output.put_line('End Insert Records');
  EXCEPTION
  WHEN already_exists THEN
    dbms_output.put_line('RETWSP_CUST_CHMDL_COST not found');
  END; ------------- sub-block ends
  dbms_output.put_line('End Insert records into RETWSP_CUST_CHMDL_COST');
  -- Populate settings table
  DECLARE ---------- sub-block begins
    already_exists EXCEPTION;
    PRAGMA EXCEPTION_INIT(already_exists, -00955);
    v_stmt               VARCHAR2(4000);
    v_algo_name          VARCHAR2(100);
    v_algo_decision_tree VARCHAR2(100);
  BEGIN
    dbms_output.put_line('Start Populate settings table' || dbms_data_mining.algo_name);
    dbms_output.put_line('Start Populate settings table' ||
dbms_data_mining.algo_decision_tree);
    v_algo_name          := dbms_data_mining.algo_name;
    v_algo_decision_tree := dbms_data_mining.algo_decision_tree;
    v_stmt               := 'INSERT INTO RETWSP_CUST_CHMDL_SETTINGS (setting_name,
setting_value) VALUES (''' || v_algo_name || ''',''' || v_algo_decision_tree || ''')';
    dbms_output.put_line('Start Populate settings table v_stmt --' || v_stmt);
    EXECUTE IMMEDIATE v_stmt;
  EXCEPTION
  WHEN already_exists THEN
    dbms_output.put_line('Exception not found');
  END; ------------- sub-block ends
  DECLARE ---------- sub-block begins
    already_exists EXCEPTION;
    PRAGMA EXCEPTION_INIT(already_exists, -00955);
    v_table_name  VARCHAR2(100);
    v_matrix_cost VARCHAR2(100);
  BEGIN

    v_table_name  := dbms_data_mining.clas_cost_table_name;
    v_matrix_cost := 'RETWSP_CUST_CHMDL_COST';
    EXECUTE IMMEDIATE 'INSERT INTO RETWSP_CUST_CHMDL_SETTINGS (setting_name,
setting_value) VALUES' || '(''' || v_table_name || ''',''' || v_matrix_cost || ''')';
    dbms_output.put_line('End Populate settings table');
  EXCEPTION
  WHEN already_exists THEN
    dbms_output.put_line('Exception not found');
  END; ------------- sub-block ends
  ---------------------
  -- CREATE A NEW MODEL
  --
  -- Build a DT model
  dbms_output.put_line('Start Create Churn Model');
  DBMS_DATA_MINING.CREATE_MODEL( model_name => 'RETWSP_CUST_CHURN_MODEL',
mining_function => dbms_data_mining.classification, data_table_name =>
'cis_cust_attr_vw', case_id_column_name => 'CUSTOMER_ID', target_column_name =>
'CHURN_SCORE', settings_table_name => 'RETWSP_CUST_CHMDL_SETTINGS');
  dbms_output.put_line('End Create Churn Model');
  --------------------------
  -- DISPLAY MODEL SIGNATURE
  --
  column attribute_name format a40
  column attribute_type format a20
  SELECT attribute_name,
    attribute_type
  FROM user_mining_model_attributes
  WHERE model_name = 'RETWSP_CUST_CHURN_MODEL'
  ORDER BY attribute_name;
END p_chrun_model;
END pkg_odm_model;

Test ODM Model

DECLARE
  RUN_ID NUMBER;
BEGIN
  DBMS_OUTPUT.ENABLE;
  dbms_output.put_line('Churn Model Process starts');
  RUN_ID := 1001;
  pkg_odm_model.proc_churn_model( RUN_ID => RUN_ID );
  dbms_output.put_line('Churn Model Process ends');
END;

Notebooks

Data scientists can use the Innovation Workbench Notebook to create notebooks, which are collections of documentation, snippets of code, and visualizations. These notebooks are bundled with key python modules for machine learning, data mining, natural language processing, network analysis, and optimization solvers.

Here is a list of some of the python packages that are bundled with AIF. These packages provide features that span data mining processes of from data exploration to data visualization.

Ensemble Machine Learning Algorithms with scikit-learn
Data exploration and analysis using Pandas; NumPy; SciPy
Data visualization using Matplotlib; Seaborn
Data storage using cx_Oracle
Graph algorithms using Networkx
Optimization using Gurobi Solver

The full list of python libraries bundled with AIF can be viewed from a notebook by running the following:

print("======================")
print("Installed Libraries:")
print("======================")
from pip._internal.operations.freeze import freeze
for requirement in freeze(local_only=True):
    print(requirement)
quit ()
pip list

Note that, due to security requirements, in some instances previously packaged libraries have been deprecated.

Data Studio graph analytics includes numerous built-in graph algorithms. Some of the classes of algorithms that it provides include:

Community Detection
Path Finding
Ranking
Recommendation
Pattern Matching
Influencer Identification

Invoking Python Code

Figure 21-12 Invoking Python Code

Connecting to a database using cx_Oracle

Database connection string should be fetched from environment variable. Refer to code in Figure 21-13 in red.

Cx oraclen

%python4]

import-cx Oracle] import-os4]

import: pandas-as-pd4]

dbwallet_entry=os.environ[‘PYTHON_RETWSP_DBALIAS’]4] print(dbwallet_entry)4l

con-=-cx_Oracle.connect(’/@’-+-dbwallet_entry)4

ver-=-con.version.split(”.“)4]

print(‘Version’)4q] print(ver)4] | query: =-“SELECT-*-FROM-rse_prod_hier- where-rownum:←5”9] df_ora-=-pd.read_sql(query,- con=con)4]

df_ora.iloc[O]4

| S%pythony from-gurobipy-import-* ] ] from-+rsecommon.analytics.gurobiimport-* ] ] try: 1 —instance-=rseenv .RseGurobiEnv.getinstance(){ -env-=instance.getGurobiEnv()]] m= Model(“mip1”,-env){] Model(“mip1”,-env){] 1 --#-Createvariables] x= m_.addVar(vtype=GRB. BINARY, -:name=“x”)4] -:name=“x”)4] —y-=m_.addVar(vtype=GRB.BINARY,-name=“y”)4] --z-=m.addVar(vtype=GRB. BINARY, -name=“z’“)4] -name=“z’“)4] 1 --#-Set-objective] —m_.setObjective(x-+-y+-2--z,-GRB.MAXIMIZE)4] 1 --#-Add-constraint:-x+:2 y-+-3-2-⇐4] om. addConstrx+-2 y4-3-* 2-⇐4,-“c0”)q addConstrx+-2* y4-3-* 2-⇐4,-“c0”)q y4-3-* 2-⇐4,-“c0”)q 2-⇐4,-“c0”)q 1 -—-#-Add-constraint:x+-y->=-1] -~m.addConstrx+-y-2=-1,-""c1”)] 1 -~m.optimize(){] 1 —forv-in-m.getVars(): ~—eprint{‘%s-%g’%(v.varName,v.x))4 1 -~print(‘Obj:-2og’-9om_.objVal)] 1 except-GurobiError-as-e:4] -~~print(‘Error-code-’+-str(e.errno)+-”:-”+-str(e.message)) 1 except AttributeError:4] -~~-print(‘Encol 11untered-an-attribute-error’)4] PR

Oracle JDBCa Sooraclen

Execute-PLSQLa %oracle] call-DBMS_^{OUTPUT.PUT_LINE(‘l-am-plsql-procedure,-use-call-procedure_name’);5}

‘opex’] builder=-session.newGraphBuilder(){] 1 //-create-afew-vertices] vi=-builder.addVertex(1).addLabel(“Person”).setProperty(“name”,-“Charles”).setProperty(“age”,-24)4] v2 =builder,addVertex(2).addLabel(“Person”).setProperty(“name”,-Susan”).setProperty(“age”,-36)4] v3=builder.addV ertex(3).addLabel(“Person”).setProperty(“name”,-“Philip”).setProperty(“age”,-22)4] v4—-builder.addV ertex(4).addLabel(“Place”).setProperty(“name”,-“McDonalds”)4] ertex(4).addLabel(“Place”).setProperty(“name”,-“McDonalds”)4] v5=-builder,addVertex(5).addLabel(“Place”).setProperty(“name”,-“Wendy’s”)4] v6=-builder,addV ertex(6).addLabel(“Place”).setProperty(“name”,-“Office”)4] v7 =builder,addV =builder,addV ertex(7).addLabel(“Car”).setProperty(“name”,-“VW-Passat”)]] v8 =-builder,addV ertex(8).addLabel(“Car”).setProperty(“name”,-“Toyota-Highlander”)4] v9-=-builder,addVertex(9).addLabel(“Place”).setProperty(“name”,-“Central-Park”)4] 1 //vertices-can-have-multiple-labels]] v4.addLabel(“Restaurant”)]] v5.addLabel(“Restaurant”)]] 1 //-create-a-few-edges] builder.addEdge(0,-v1,-v2).setLabel(“knows’“)4] builder,addEdge(1,v2,1).setLabel(“knows’)4] builder.addEdge(?,-v2,-v3).setLabel(“knows’)4] builder.addEdge(3,-v4,-v5).setLabel(“connected”)4] builder.addEdge(4,-v5,-v6).setLabel(“connected”)]] builder.addEdge(5,-v4,-v6).setLabel(“connected”)4] builder.addEdge(6,v1,-v7).setLabel(“owns’).setProperty(“since”,-1998)]] builder.addEdge(7,-v2,-v8).setLabel(“owns’).setProperty(“since”,-2003)]] builder.addEdge(8,-v1,-v6).setLabel(“worksAt”)4] builder.addEdge(9,-v2,-v6).setLabel(“worksAt”)4] builder.addEdge(10,-v9,-v4).setLabel(“connected”)]] //store-resulting-graph-in-a-variable:graph]graph] graph=-builder.build()s

Scheduling Jobs

Notebooks can be scheduled for automatic execution. To implement this, make a POST request to the using REST API call with the following payloads.

Execute once, immediately.

{
  "cronSchedule": "",
  "timeEnd": "",
}

Execute at a regular interval, and stop at a given date .

{
  "cronSchedule": "0/1440 * * * * *",
  "timeEnd": "2021-02-24T21:10:34.400Z",
  "id": "dsYVGG9Mvw"
}

Execute at a regular interval, indefinitely.

{
  "cronSchedule": "0/ 1440 * * * * *",
  " timeEnd": "",
  "id": "dsYVGG9Mvw"
   }

REST API Documentation

The REST API documentation details for request/response are located here:http:// datastudio.oraclecorp.com/docs/apidoc/swagger-ui.html#/

Example

Here is a REST API curl call example showing how to schedule a notebook.

Table 21-1 Example for Scheduling Notebooks

Seq. #	REST API Purpose	REST API Curl Request	REST API Curl Response
1	Fetch Authentication TokenPOST request	curl —location —request POST ’https://<IDCS_HOST>/oauth2/v1/ token’ —header ‘Authorization: Basic ’ — header ‘Content-Type: application/x- www-form-urlencoded’ —data- urlencode ‘grant_type=password’ — data-urlencode ’scope=urn:opc:idm:myscopes’ —data-urlencode ’username=’ —data- urlencode ‘password=‘	Response will have authentication token. To make subsequent request: { “access_token”: "", “token_type”: “Bearer”, “expires_in”: 3600}

Table 21-1 (Cont.) Example for Scheduling Notebooks

Seq. #	REST API Purpose	REST API Curl Request	REST API Curl Response
2	Setup SessionGET request	curl —location —request GET ’<APP_HOST>/datastudio/v2/ sessions/user’ —header ’Authorization: Bearer ’	{ “username”: “<user_name>”, “permissions”: [ “graph_create”, “export_all”, “view_permissions_tab”, “import_notebook”, “view_dashboard_tab”, “view_credentials_tab”, “create_notebook”, “create_credential”, “view_interpreter_tab”, “delete_all” ], “authToken”: ""}
Note:T	he first two steps ab	ove are mandatory. Execute any of the	following steps.
3	Schedule NotebookPOST request	curl —location —request POST ’https://<APP_HOST>/datastudio/v2/ notebooks/schedule’ —header ’Authorization: Bearer ’ — header ‘Content-Type: application/ json’ —data-raw ’{ “cronSchedule”: "", “timeEnd”: "", “id”: “dsYVGG9Mvw”}’	{ “id”: “dsja4YWg”, “status”: “SUBMITTED”, “startTime”: null, “endTime”: null, “error”: null, “tasks”: []}
4	Schedule Notebook Paragraphs to execute immediatelyPOST request	curl —location —request POST ’https://<APP_HOST>/datastudio/v2/ notebooks/schedule’ —header ’Authorization: Bearer ’ — header ‘Content-Type: application/ json’ —data-raw ’{ “cronSchedule”: "", “paragraphs”: [ { “id”: “dsB0zM58” } ], “timeEnd”: "", “id”: “dsYVGG9Mvw”}’	{ “id”: “dsVBqlO3”, “status”: “SUBMITTED”, “startTime”: null, “endTime”: null, “error”: null, “tasks”: []}
5	Schedule Notebook to execute immediately.Execut e a notebook every five minutes.POST request	curl —location —request POST ’https://<APP_HOST>/datastudio/v2/ notebooks/schedule’ —header ’Authorization: Bearer ’ — header ‘Content-Type: application/ json’ —data-raw ’{ “cronSchedule”: ”/1440 * * * *”, “endDate”: “2020-12-17T14:10:34.400Z”, “id”: “dsYVGG9Mvw”}’	{ “id”: “dsja4YWg”, “status”: “SUBMITTED”, “startTime”: null, “endTime”: null, “error”: null, “tasks”: []}
6	Schedule Notebook Paragraphs.Execut e a notebook/ paragraph every five minutes.POST request	curl —location —request POST ’https://<APP_HOST>/datastudio/v2/ notebooks/schedule’ —header ’Authorization: Bearer ’ — header ‘Content-Type: application/ json’ —data-raw ’{ “cronSchedule”: ”/1440 * * * *”, “paragraphs”: [ { “id”: “dsB0zM58” } ], “timeEnd”: “2020-12-17T14:10:34.400Z”, “id”: “dsYVGG9Mvw”}’	{ “id”: “dsVBqlO3”, “status”: “SUBMITTED”, “startTime”: null, “endTime”: null, “error”: null, “tasks”: []}

To test, create a table and view records that are inserted in the table.

%oracle
insert into iw_test (first_name, last_name ) values (John, 'Doe')
%python
print('Hello World')

Verify using the following:

CREATE TABLE iw_test (
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(person_id)
);
select count(*) from iw_test;

REST API

Here is a REST API curl call example showing how to schedule a notebook.

Table 21-2 Example

REST API purpose	REST API curl request	REST API curl response
Fetch Authentication Token	curl —location —request POST ‘http:// bur00afq.us.oracle.com:26000/ datastudio/v2/sessions/login’ — header ‘Authorization: Basic b3Jhc2UxOnBhc3N3b3JkMQ==’ — header ‘Content-Type: application/ json’ —header ‘Cookie: JSESSIONID=cmWRE8eaMH1e7ClA 72Qx8QVxX3SKVMuEi1S_ErDgySh2 qInJRbdZ!1637160649’ —data-raw ’{“credentials”:“password1”,“principal” :“orase1”}‘	Response will have authentication token. To make subsequent request:authToken”:“de6460f5- e36c-4592-b2c3-6ef18268e6c5”
Schedule Notebook	curl -X POST -H ‘Content-Type: application/json’ -H ‘x-auth-token: de6460f5-e36c-4592- b2c3-6ef18268e6c5’ -i ‘https:// :/datastudio/v2/ notebooks/schedule’ —data ’{ “cronSchedule”: “0/5 * * * * *”, “endDate”: “2020-02-24T21:10:34.400Z”, “id”: “dsZgvK3G”}	{“id”:“dsbBVLgL”,“status”:“SUBMITTE D”,“startTime”:null,“endTime”:null,“err or”:null,“tasks”:[]}

Restful Service

RESTful Services allow for the declarative specification of RESTful access to the database. They are created by configuring a set of Uniform Resource Identifiers (URIs) to a SQL query or anonymous PL/SQL block. The set of URIs is identified by a URI template.

To create a RESTful service:

1. Select SQL Workshop RESTful Services.

Zip File Contents

The ZIP file must contain a pair of files for each table to be loaded, a .dat file and a .ctx file.The data file can have any name, with the .dat extension. The context file must match the name of the data file, but with the .ctx extension. If a data file is provided without a .ctx file, then the data file will be ignored.

For example, to load a table called STG_WKLY_SLS, you require a file called STG_WKLY_SLS.dat to hold the data to be loaded and a file called STG_WKLY_SLS.dat.ctx to describe the details regarding the load.

No support exists for directory names inside the zip file, so if they are provided they will be ignored. For example, you cannot send directory1/STG_WKLY_SLS.dat and directory2/ STG_WKLY_SLS.dat because they will end up overriding each other when the zip is extracted.

Although you can provide multiple files to be loaded in a single zip, none of them can be for the same table as another load. The load truncates the data in the table to be loaded, so if the same table is targeted by multiple files, then only the latest file will be loaded. Because this data is truncated prior to each load, you should follow a pattern in which these tables are the staging tables to be used to then populate data in other tables. In this way, the data is verified, which allow for the merging of data, via updates or inserts. This is not possible if you loaded directly to your final table.

Context File Details

The .ctx file provide options for including details that describe the data load. These details are similar to the options used by SQLLoader, so refer to documentation about the SQLLoader for any additional details about concepts noted here.

Here is an example of a context file.

#TABLE#SLS_WKLY_SLS#
#DELIMITER#|#
#COLUMN#WEEK_DATE#DATE(10) "YYYY-MM-DD"#
#COLUMN#PRODUCT_KEY#
#COLUMN#LOCATION_KEY_FILLER#BOUNDFILLER#
#COLUMN#SLS_AMT#
#COLUMN#LOCATION_ID#"some_db_package.lookup_location_id(:LOCATION_KEY_FILLER)"#
#COLUMN#LOAD_DATE#"sysdate"#

In the above example contextual file:

The #TABLE## line is required in order to specify the name of the table to be loaded.
The #DELIMITER## line is optional, and if not provided, then a | will be considered the default value.
The table to be loaded will be loaded via a Direct Path load (for efficiency sake). It will truncate the table prior to loading the table. This means that you cannot provide multiple files in a single transmission for the same table.
The columns can be wrapped in ” ” if necessary.
The data in the file must be provided as CHARACTERSET AL32UTF8.
For the column specifications, the columns must be listed in the order in which they appear in the file.The format is #COLUMN#THE_COLUMN_NAME#Any special load instructions#
Note that for Date columns, you must provide a proper date specification with the format that you are providing the data in.The example above for WEEK_DATE illustrates how to specify the date in a YYYY-MM-DD format.
For numeric columns, you do not normally require any additional load instructions.
If you are providing data that must be used as a BOUND FILLER during the load of another column, then you must specify BOUNDFILLER, just like you would in SQL*Loader.
The LOCATION_ID example above illustrates how you can refer to a FILLER column, which is then used as a parameter to a function, which then returns the actual value to load to the column.
If you want an value such as the system date/time to be loaded, then you can specify “sysdate” as shown above for LOAD_DATE column.
When you are populating columns that are loaded via a special expression (such as LOCATION_ID and LOAD_DATE above), be sure to provide them last in the context file.
When loading data character data, it maybe be necessary to specify the column like this: CHAR(50). This is commonly required by SQL*Loader when advanced character sets are used.
The examples shown above for LOCATION_KEY_FILLER and LOCATION_ID are not usual/simple use cases, but are supported. For a better understanding of how that works, refer to the documentation for SQL*Loader.

Data Load Feedback

When the data load is complete, a zip file named ORASE_IW_DATA_extract.zip will be uploaded to Object Storage and it will contain the log and bad files for the data load.

Invoking the Data Load

In order to invoke the load process, you must use the POM application to invoke the following adhoc process:

RSE_LOAD_IW_FILES_ADHOC.

This controls the execution of all these steps. Once the load has been completed, you should be able to use the data as necessary inside the Innovation Workbench. See “Process Orchestration and Monitoring” for additional details.

Custom Data Exports

You can export data that has been created within the Innovation Workbench workspace. You first configure the table to be exported. A process is then executed that exports the table data to a text file. The data is then gathered into a zip file, and the zip file is moved to the Object Storage for retrieval using File Transfer Service (FTS).

Required Configuration

Using the Data Management menu option (described in Oracle Retail Science Cloud Services User Guide ), you can manage the tables to be exported, using the formatting shown in Table 21-3. Once in the screen, select the table RSE_CUSTOM_EXP_CFG. Then, add rows and make adjustments to existing rows as required in order to define the tables to be exported.

Table 21-3 Export Table Formatting

Column Name	Data Type	Example	Comments
TABLE_NAME	Character(30)	IW_RESULTS_TABLE	The name of the table to be exported.
FILE_NAME	Character(80)	iw_results_table.csv	The name of the file to be created, excluding any directory names.
DESCR	Character(255)	Results table of XYZ Calculation	Any description to describe the table.
COL_DELIMITER	Character(1)	, (comma)	The character to use as a delimiter between columns.
COL_HEADING_FLG	Character(1)	Y	A Y/N value to indicate if the export should include a heading row (Y) that contains the names of the columns, or not (N).
DATE_FORMAT	Character(30)	yyyy-MM-dd HH:mm:ss	The format to use for any date columns. This format must be a valid format for Java date handling.
ENABLE_FLG	Character(1)	Y	A flag to indicate if this table should be exported (Y) or not (N) This flag can be used to temporarily disable an export, without the need to remove it completely.

Invoking the Export

In order to begin the export process, you must use the POM application to invoke the following AdHoc process:

RSE_IW_EXPORT_FILES_ADHOC / job: RSE_IW_EXPORT_FILES_ADHOC_JOB.

This process controls the execution of the steps to export the data. Once the export has been completed, a zip file named ORASE_IW_EXPORT_extract.zip is created. All files will be named according to the names specified in the RSE_CUSTOM_EXP_CFG table. See “Process Orchestration and Monitoring” for additional details on how to execute a Standalone/ AdHoc Sample Extensibility Use Caseprocess.

Sample Extensibility Use Case

Clients can use IW’s framework to address use-cases unique to their business requirements.

Here is one possible example of IW’s extensibility.

A customer wants to expose AIF-cleansed sales transaction data from AI Foundation to a thirdparty system, using an integration software for connecting applications, data, and devices. This enables their other applications to access the exact calculations/AIF-cleansed data that they require.

IW can be extended as outlined below in order to address this use-case:

Load sales data into AI Foundation using the existing nightly sales data load routine.

Oracle Application Express

(2) space_planner_user

Please select a workspace from the list below = = Retailer(RETWSP_QA_1) Workspace Administrator Last login 6 minutes ago Sign Out

   job_class          =>  'RETAILER_WORKSPACE_JOBS',
   comments           =>  'Retailer workspace churn model job');
END;
/

Schema Objects

Database objects owned by the various schemas in AIF applications are available for the advanced analyst to use. Here are some examples:

Table 21-4 Schema Objects

Table Name	Description
rse_cal_hier	This table is used to hold all calendar hierarchies. Examples are the normal calendar hierarchy, and can also contain an alternate hierarchy for the fiscal calendar.
rse_prod_hier	This table is used to hold all product hierarchies. Examples are the normal product hierarchy, and can also contain an alternate category hierarchy.
rse_loc_hier	This table is used to hold all location hierarchies. Examples are the normal organizational hierarchy, and can also contain an alternate hierarchy for trade areas.
rse_prod_loc_status	Status of the item at this location for this time frame. A-Active; I- Inactive; C-Discontinued; D-Deleted
rse_ret_lc_wk_a	This table contains aggregate sales data for the dimensions of a location and a week.
rse_sls_lc_wk_a	This table contains aggregate sales data for the dimensions of a location and a week.
rse_sls_pr_lc_cs_wk	This table contains aggregate sales data for a Product, Location, Customer Segment and Week. The SLS_PR columns represent the metrics for that week that were on promotion, while the other metrics represent the sales metrics while the item was not on promotion.
rse_sls_pr_wk_a	This table contains aggregate sales data for the dimensions of a product and a week.
rse_sls_ph_lc_wk_a	This table contains aggregate sales transaction data for different product hierarchy/levels, at the store location/week dimension.
rse_sls_pr_lc_wk	This table contains aggregate sales data for a Product, Location, and Week. The SLS_PR columns represent the metrics for that week that were on promotion, while the other metrics represent the sales metrics while the item was not on promotion.
rse_sls_txn	This table contains sales transaction data.
rse_prod_attr	This is the table that holds product attributes.
rse_prod_attr_grp	This is the table used to load the associations of CM Groups to product attributes.
rse_prod_attr_grp_value	This is the table used to load the associations of CM Groups to product attributes and its values
rse_prod_attr_grp_value_map	This is the table used to load the associations of CM Groups to product attributes, group values and actual product attribute values
rse_like_loc	This is the table used to load the like stores for CMGroup or Category.
rse_hier_level	This table defines the various levels for all the hierarchies.

Table 21-4 (Cont.) Schema Objects

Table Name	Description
rse_hier_type	This table defines the available hierarchies for use within the RSE applications.
rse_fake_cust	Table for specifying customers who are considered as fake customers. A fake customer is a customer who purchases too many transactions to be considered a single customer. Examples are generic store cards.
rse_loc_src_xref	This table contains integration ID information that enables interaction with other systems, using IDs that other systems can accommodate for the Location Hierarchy.
rse_prod_src_xref	This table contains integration ID information that enables interaction with other systems, using IDs that other systems can accommodate for the Product Hierarchy.
rse_log_msg	This table contains messages logged while database, batch or business processing.
w_party_per_d	This table contains customer data and its attribute.
cis_cust_attr_vw	Customer Attributes - This view provides basic customer attributes.
cis_cust_trans_attr_vw	Customer Transaction Attributes - This view provides attributes for customer transactions.
cis_cust_trans_ph_attr_vw	Customer Transaction Attributes - This view provides product attributes for customer transactions.
cis_custseg_attr_exp_vw	This view provides an export of the attributes that define a segment.
cis_custseg_cat_attr_exp_vw	This view provides an export of the product attributes that define a segment.
cis_custseg_cust_exp_vw	This view provides the members for an exportable set of segments.
cis_custseg_exp_vw	This view provides an exportable set of clusters for customer segmentation.
cis_sls_cust_cal_a	This table contains aggregate customer sales data for a configurable level of the calendar hierarchy. The table is to be partitioned by Calendar and is also be suitable for sub partitioning by Customer using a Hash Partition strategy, so that subsequent uses can operate within the confines of a given Hash partition.
cis_sls_cust_ph_cal_a	This table contains aggregate customer sales data for a configurable level of the calendar hierarchy, for a selected set of product hierarchies. The table is to be partitioned by Calendar and is also be suitable for sub-partitioning by Customer using a Hash Partition strategy, so that subsequent uses can operate within the confines of a given Hash partition.
cis_sls_ph_a	This table contains aggregate sales data for all product hierarchy members of a configured hierarchy type and level. This can be used to identify the Top Categories for use by things like Customer Segmentation.
cis_cluster_set_exp_vw	This view provides an exportable set of clusters to send to Cat Man.
cis_store_cluster_exp_vw	This view provides an exportable set of clusters for stores.
cis_store_cluster_mem_exp_v w	This view provides the members with an exportable set of segments.
cis_store_cluster_prop_exp_vw	This view provides an exportable set of clusters for stores.
cis_cluster_sls_ph_lc_a	This table contains calendar level aggregates for the various clusters. The table is to be partitioned by Calendar.

Table 21-4 (Cont.) Schema Objects

Table Name	Description
cis_cluster_summ_level_attr	These are metrics generated at cluster/attribute/business object level. There are metrics that are generated at that level such as centroid.
cis_prod_attr_loc_share	This table contains aggregate sales data for product attribute values as well as the share that these values are with respect to the total sales for the product hierarchy, calendar, location. The share value is a configurable value, which can either be based on sales units, sales amount or profit amount.

In this guide

Guide: AI Foundation Implementation Guide
Previous: 20 Batch Processing
Next: 22 Process Orchestration and Monitoring

A Appendix: Innovation Workbench Workshop — AI Foundation Implementation Guide · shares CHURN_SCORE, CREATE_MODEL, DBMS_DATA_MINING, DROP_MODEL

Oracle Retail Knowledge Base

Explorer

21 Innovation Workbench

Test ODM Model

Notebooks

Invoking Python Code

Connecting to a database using cx_Oracle

Scheduling Jobs

Execute once, immediately.

Execute at a regular interval, and stop at a given date .

Execute at a regular interval, indefinitely.

REST API Documentation

Example

REST API

Restful Service

Zip File Contents

Context File Details

Data Load Feedback

Invoking the Data Load

Custom Data Exports

Required Configuration

Invoking the Export

Sample Extensibility Use Case

Schema Objects

Table 21-4 (Cont.) Schema Objects

In this guide

Graph View

Table of Contents

Backlinks

Oracle Retail Knowledge Base

Explorer

21 Innovation Workbench

Test ODM Model

Notebooks

Invoking Python Code

Connecting to a database using cx_Oracle

Scheduling Jobs

Execute once, immediately.

Execute at a regular interval, and stop at a given date .

Execute at a regular interval, indefinitely.

REST API Documentation

Example

REST API

Restful Service

Zip File Contents

Context File Details

Data Load Feedback

Invoking the Data Load

Custom Data Exports

Required Configuration

Invoking the Export

Sample Extensibility Use Case

Schema Objects

Table 21-4 (Cont.) Schema Objects

In this guide

Related chapters

Graph View

Table of Contents

Backlinks