| Oracle® Retail Advanced Science Cloud Services Implementation Guide Release 17.0 E95345-03 |
|
 Previous |
 Next |
| Oracle® Retail Advanced Science Cloud Services Implementation Guide Release 17.0 E95345-03 |
|
Previous |
Next |
Science Innovation Workbench is a workspace that provides read-only access to the application data objects and clean data by using Oracle APEX. This extension is a workspace for advanced analytics users to add new implementations by using Oracle Advanced Analytic (Oracle R/ODM) algorithms that are implemented as SQL/PLSQL functions.
This appendix provides instructions for using the interface.
Open Analytics, understand why, what and how
Innovation Workbench helps in extending and delivering 20 percent of these features. It provides tools to learn, discover, and innovate existing application features.
Innovation Workbench helps answering questions such as:
Which customers are likely to leave?
What is customer feedback?
Who are the prospect customers?
With Innovation Workbench, the following new components are available:
Retailer schema (see green slice below)
Retailer workspace
SQL Workshop
Application Builder
The Retailer Schema has read-only access to Application Schema. All permissions are controlled using database permissions.
Roles for the analyst, scientist, developer. and integration.
Oracle Developer identifies which objects can be accessed.
Administrator manages users.
Retailer Developer uses data shared, extend, explore and mine data.
As part of Innovation Workbench, a retailer developer has the following roles:
Business Analyst, explores data, gains insights
Data Scientist, discovers patterns in data mining using Oracle R/ODM
Developer, develops applications and shares insights via UI and web services
Key components
The SQL Workshop provides tools to view and manage database objects.
The Application Builder provides tools to create reports and charts using wizards and page designers.
Login using http:// < url>
Login/Password
This takes you to the application.
Select Innovation Workbench
Select Mange Innovation Workbench
This takes you to Retailer Workspace where you can login with same credentials login/password.
Retailer Workspace has a dedicated schema and work area to do full stack development.
Dedicated Schema for retailer
Granting and revoking database object privileges
Dedicated service plan and consumer group for retailer workspace
Access to database utilities and scheduling for long-unning transactions
SQL Workshop
Application Builder
In this exercise you will browse the Application schema objects using SQL Workshop.
Access Read Only
Cleansed
Aggregated
Filtered
Sample
Inputs and Outputs
Note that this dedicated schema is only associated with the application Schema and cannot be extended to any other new schema.
Data
This SQL was saved so you can type sql in the command and execute and save it.
Using this you can
Upload Scripts
Create Scripts
Edit Scripts
Load data
Use ETL feeds
Use system feeds whereever possible. If application feed is not available then ReST objects can be used.
Data can be loaded to the retailer schema using the ReST API in JSON format. These must be batched from the client end while uploading data into application.
SQL Workshop ' SQL Command
Create table that will hold Customer Reviews
DROP TABLE retwsp_cust_prod_rev_stg;
CREATE TABLE retwsp_cust_prod_rev_stg(
json_str clob
);
DROP SEQUENCE retwsp_cust_prod_rev_seq CREATE SEQUENCE retwsp_cust_prod_rev_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; reviewerID varchar2(255) NOT NULL,
DROP TABLE retwsp_customer_product_review;
CREATE TABLE retwsp_customer_product_review
(
reviewid NUMBER NOT NULL,
reviewerID varchar2(255) NOT NULL,
asin varchar2(255) NOT NULL,
reviewerName varchar2(255) NOT NULL,
helpful varchar2(255) NOT NULL,
reviewText varchar2(4000) NOT NULL,
overall number(2, 0) NOT NULL,
summary varchar2(255) NOT NULL,
unixReviewTime NUMBER(10) NOT NULL,
reviewTime VARCHAR2(255) NOT NULL
);
Copy and paste the above content and click Run.
Create ReST API, which allows you to load customer reviews in the above table.
Click Create.
Here is the code to populate the JSON data to the relational table.
Declare
customer_review_lblob blob;
customer_review_cblob clob;
begin
customer_review_lblob := :body;
customer_review_cblob := wwv_flow_utilities.blob_to_clob(customer_review_lblob);
-- insert into stage table
insert into retwsp_cust_prod_rev_stg values (customer_review_cblob);
-- move to target table
insert into retwsp_customer_product_review (reviewid, reviewerID, asin, reviewerName, helpful, reviewText, overall, summary, unixReviewTime, reviewTime)
with review_data
as (select json_str from retwsp_cust_prod_rev_stg)
select retwsp_cust_prod_rev_seq.nextval, j.*
from review_data rd,
json_table(rd.json_str, '$[*]'
columns (reviewerID varchar2 path '$.reviewerID'
,asin varchar2 path '$.asin'
,reviewerName varchar2 path '$.reviewerName'
,helpful number path '$.helpful'
,reviewText varchar2 path '$.reviewText'
,overall number path '$.overall'
,summary varchar2 path '$.summary'
,unixReviewTime number path '$.unixReviewTime'
,reviewTime varchar2 path '$.reviewTime'
)
) j;
delete from RETWSP_TMP_CUST_PROD_RW;
commit;
end;
Provide the essential elements, including name, URI prefix, and template.
Select the method. In this case it is POST, since the data is getting updated.
Populate the name for the RESTful Services Module. URI Prefix as customer and URI Template/reviews. The / is required.
Add resource handler method - POST and source type - PL/SQL
Declare
customer_review_lblob blob;
customer_review_cblob clob;
begin
customer_review_lblob := :body;
customer_review_cblob := wwv_flow_utilities.blob_to_clob(customer_review_lblob);
-- insert into stage table
insert into retwsp_cust_prod_rev_stg values (customer_review_cblob);
-- move to target table
insert into retwsp_customer_product_review (reviewid, reviewerID, asin, reviewerName, helpful, reviewText, overall, summary, unixReviewTime, reviewTime)
with review_data
as (select json_str from retwsp_cust_prod_rev_stg)
select retwsp_cust_prod_rev_seq.nextval, j.*
from review_data rd,
json_table(rd.json_str, '$[*]'
columns (reviewerID varchar2 path '$.reviewerID'
,asin varchar2 path '$.asin'
,reviewerName varchar2 path '$.reviewerName'
,helpful number path '$.helpful'
,reviewText varchar2 path '$.reviewText'
,overall number path '$.overall'
,summary varchar2 path '$.summary'
,unixReviewTime number path '$.unixReviewTime'
,reviewTime varchar2 path '$.reviewTime'
)
) j;
delete from retwsp_cust_prod_rev_stg;
commit;
end;
Post customer reviews using following the data http://<url>
This posting can be done using POSTMaN or any ReST Client (such as java application posting data into Retailer Schema)This step requires PostMan or a client that will push reviews. Download and Install PostMan. If it cannot be installed, go to the next step.https://www.getpostman.com/apps
Select * from retwsp_customer_product_review;
These methods must be used judiciously, with smaller datasets.
Prepare Data
Key components that can be done for refining data are Create Train and Test dataset
The followings SQL provides how stratified split is used that can preserve the categorical target distribution in the resulting training and test dataset
Key items below are
target column. It must be categorical type.
case id column. It must contain unique numbers that identify the rows.
input data set. {percent of training dataset} - percent of training dataset. For example, if you want to split 60% of input dataset into training dataset, use the value 60. The test dataset will contain 100%-60% = 40% of the input dataset. The training and test dataset are mutually exclusive.
Train Datsasets
create or replace view retwsp_rf_cust_attr_test_vw as (SELECT v1.* FROM ( -- randomly divide members of the population into subgroups based on target classes SELECT a.*, row_number() OVER (partition by EDUCATION_BCKGND_CODE ORDER BY ORA_HASH(CUSTOMER_WID)) "_partition_caseid" FROM retwsp_rf_cust_attr_vw a) v1, ( -- get the count of subgroups based on target classes SELECT EDUCATION_BCKGND_CODE, COUNT(*) "_partition_target_cnt" FROM retwsp_rf_cust_attr_vw GROUP BY EDUCATION_BCKGND_CODE) v2 WHERE v1.EDUCATION_BCKGND_CODE = v2.EDUCATION_BCKGND_CODE -- random sample subgroups based on target classes in respect to the sample size AND ORA_HASH(v1."_partition_caseid", v2."_partition_target_cnt"-1, 0) <= (v2."_partition_target_cnt" * 60 / 100));
Test Datasets
create or replace view retwsp_rf_cust_attr_test_vw as (SELECT v1.* FROM ( -- randomly divide members of the population into subgroups based on target classes SELECT a.*, row_number() OVER (partition by EDUCATION_BCKGND_CODE ORDER BY ORA_HASH(CUSTOMER_WID)) "_partition_caseid" FROM retwsp_rf_cust_attr_vw a) v1, ( -- get the count of subgroups based on target classes SELECT EDUCATION_BCKGND_CODE, COUNT(*) "_partition_target_cnt" FROM retwsp_rf_cust_attr_vw GROUP BY EDUCATION_BCKGND_CODE) v2 WHERE v1.EDUCATION_BCKGND_CODE = v2.EDUCATION_BCKGND_CODE -- random sample subgroups based on target classes in respect to the sample size AND ORA_HASH(v1."_partition_caseid", v2."_partition_target_cnt"-1, 0) <= (v2."_partition_target_cnt" * 40 / 100));
Now we have customer reviews we will reviews, build text to find document frequencies, show them in charts. And we will further extract key text for each review
This script can be found under SQLWorkshop ' SQL Scripts [Text Mining Feature Extraction]
-------------------------------------------------------------Build Text and Token --------------------------------------------
DECLARE
v_policy_name VARCHAR2(4000);
v_lexer_name VARCHAR2(4000);
BEGIN
v_policy_name := 'RETWSP_POLICY';
v_lexer_name := 'RETWSP_LEXER';
ctx_ddl.drop_preference(v_lexer_name);
ctx_ddl.drop_policy(
policy_name => v_policy_name
);
ctx_ddl.create_preference(
v_lexer_name,
'BASIC_LEXER'
);
ctx_ddl.create_policy(
policy_name => v_policy_name,
lexer => v_lexer_name,
stoplist => 'CTXSYS.DEFAULT_STOPLIST'
);
END;
/
DROP TABLE retwsp_feature;
CREATE TABLE retwsp_feature
AS
SELECT
'REVIEWTEXT' "COLUMN_NAME",
token,
value,
rank,
count
FROM
(
SELECT
token,
value,
RANK() OVER(
ORDER BY value ASC
) rank,
count
FROM
(
SELECT
column_value token,
ln(n / COUNT(*) ) value,
COUNT(*) count
FROM
(
SELECT
t2.column_value,
t1.n
FROM
(
SELECT
COUNT(*) OVER() n,
ROWNUM rn,
odmr_engine_text.dm_policy_tokens(
'RETWSP_POLICY',
reviewtext
) nt
FROM
retwsp_customer_product_review
) t1,
TABLE ( t1.nt ) t2
GROUP BY
t2.column_value,
t1.rn,
t1.n
)
GROUP BY
column_value,
n
)
)
WHERE
rank <= 3000;
SELECT
*
FROM
retwsp_feature;
create table retwsp_review_df as
SELECT
*
FROM
(
SELECT
nested_result.attribute_name,
COUNT(nested_result.attribute_name) count_val
FROM
(
WITH
/* Start of sql for node: RETWSP_CUSTOMER_PRODUCT_REVIEW */ "N$10001" AS (
SELECT /*+ inline */
"RETWSP_CUSTOMER_PRODUCT_REVIEW"."ASIN",
"RETWSP_CUSTOMER_PRODUCT_REVIEW"."REVIEWERID",
"RETWSP_CUSTOMER_PRODUCT_REVIEW"."OVERALL",
"RETWSP_CUSTOMER_PRODUCT_REVIEW"."REVIEWTEXT"
FROM
"RETWSP_DEMO_1"."RETWSP_CUSTOMER_PRODUCT_REVIEW"
)
/* End of sql for node: RETWSP_CUSTOMER_PRODUCT_REVIEW */,
/* Start of sql for node: Build Text */ "N$10002" AS (
SELECT /*+ inline */
"REVIEWERID",
"ASIN",
"OVERALL",
odmr_engine_text.dm_text_token_features(
'RETWSP_POLICY',
"REVIEWTEXT",
'RETWSP_FEATURE',
NULL,
50,
'IDF'
) "REVIEWTEXT_TOK"
FROM
"N$10001"
)
/* End of sql for node: Build Text */ SELECT
*
FROM
"N$10002"
) output_result,
TABLE ( output_result.reviewtext_tok ) nested_result
GROUP BY
nested_result.attribute_name
)
ORDER BY count_val DESC;
select * from retwsp_review_df;
-------------------------------------------------------------Feature Extraction and Feature Comparison --------------------------------------------
-- Create the settings table
DROP TABLE RETWSP_ESA_settings;
CREATE TABLE RETWSP_ESA_settings (
setting_name VARCHAR2(30),
setting_value VARCHAR2(30));
DECLARE ---------- sub-block begins
already_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(already_exists, -00955);
v_stmt VARCHAR2(4000);
v_param_name VARCHAR2(100);
v_param_value 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_nonnegative_matrix_factor);
v_param_name := dbms_data_mining.algo_name;
v_param_value := dbms_data_mining.ALGO_NONNEGATIVE_MATRIX_FACTOR;
v_stmt := 'INSERT INTO RETWSP_ESA_settings (setting_name, setting_value) VALUES (''' || v_param_name || ''',''' || v_param_value || ''')';
dbms_output.put_line('Start Populate settings table v_stmt --' || v_stmt);
EXECUTE IMMEDIATE v_stmt;
v_param_name := dbms_data_mining.prep_auto;
v_param_value := dbms_data_mining.prep_auto_on;
v_stmt := 'INSERT INTO RETWSP_ESA_settings (setting_name, setting_value) VALUES (''' || v_param_name || ''',''' || v_param_value || ''')';
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
/
create view RETWSP_CUST_PROD_REVIEW_VW as (select reviewid, reviewtext from RETWSP_CUSTOMER_PRODUCT_REVIEW);
DECLARE
v_xlst dbms_data_mining_transform.TRANSFORM_LIST;
v_policy_name VARCHAR2(130) := 'RETWSP_POLICY';
v_model_name varchar2(50) := 'RETWSP_ESA_MODEL';
BEGIN
v_xlst := dbms_data_mining_transform.TRANSFORM_LIST();
DBMS_DATA_MINING_TRANSFORM.SET_TRANSFORM(v_xlst, 'REVIEWTEXT', NULL, 'REVIEWTEXT', NULL, 'TEXT(POLICY_NAME:'||v_policy_name||')(MAX_FEATURES:3000)(MIN_DOCUMENTS:1)(TOKEN_TYPE:NORMAL)');
DBMS_DATA_MINING.DROP_MODEL(v_model_name, TRUE);
DBMS_DATA_MINING.CREATE_MODEL(
model_name => v_model_name,
mining_function => DBMS_DATA_MINING.FEATURE_EXTRACTION,
data_table_name => 'RETWSP_CUST_PROD_REVIEW_VW',
case_id_column_name => 'REVIEWID',
settings_table_name => 'RETWSP_ESA_SETTINGS',
xform_list => v_xlst);
END;
/
------------------
-- List top (largest) 3 features that represent are represented in each review.
-- Explain the attributes which most impact those features.
-- This can be used in UI to display all key features in each review.
select REPLACE(xt.attr_name,'"REVIEWTEXT".',''), xt.attr_value, xt.attr_weight, xt.attr_rank
from (SELECT S.feature_id fid, value val,
FEATURE_DETAILS(RETWSP_ESA_MODEL, S.feature_id, 5 using T.*) det
FROM
(SELECT v.*, FEATURE_SET(RETWSP_ESA_MODEL, 3 USING *) fset
FROM RETWSP_CUSTOMER_PRODUCT_REVIEW v
WHERE reviewid = 1) T,
TABLE(T.fset) S
order by val desc) X, XMLTABLE('/Details'
PASSING X.DET
COLUMNS
"ALGORITHM" VARCHAR2(30) PATH '@algorithm',
"FEATURE" VARCHAR2(30) PATH '@feature',
RECORDS XMLTYPE PATH '/Details') R,
XMLTABLE ('/Details/Attribute'
PASSING R.RECORDS
COLUMNS
"ATTR_NAME" VARCHAR2(30) PATH '@name',
"ATTR_VALUE" VARCHAR2(120) PATH '@actualValue',
"ATTR_WEIGHT" VARCHAR2(10) PATH '@weight',
"ATTR_RANK" VARCHAR2(10) PATH '@rank'
) XT
ORDER BY ATTR_RANK;
To display the prepared data in UI for end user to review, complete the following.
Select Application Builder to create the UI.
Click Create.
Select Create Page.
Select Plugin and click Next
Select Type.
Populate Page Name and click Next.
Select the Navigation Menu entries and click Next.
Click Next and populate SQL to build the chart.
select attribute_name as TAG,
count_val as TAG_COUNT
from retwsp_review_df
where count_val > 30
order by attribute_name;
You see the Page Designer. Click Save and run icon.
Log in with username/password.
The just designed screen is displayed, with the following content.
Oracle R graphic can be used to illustrate the correlation between customer attributes.
Navigate from SQLWorkshop ' SQL Scripts.
Review the content of the SQL and execute it. This creates a table that has a scatter plot matrix image blob stored as an output.
drop table ICA_CUST_CORR; create TABLE ICA_CUST_CORR as (SELECT * FROM TABLE ( rqTableEval( cursor((select AVG_INCOME, AVG_AGE, LOYALTY_SCORE from ICA_CUSTOMER_ATTRIBUTES_TBL where AVG_INCOME is not null and AVG_AGE is not null) ) , -- Input Cursor cursor(select 'Scatterplot Matrices' as MAIN from DUAL), -- Param Cursor 'PNG', -- Output Definition 'RQG$pairs' -- R Script ) ));
Create a new page in Churn Analysis.
Select Report from the Options and click Next.
Select Classic Report.
Input the page name and click Next.
Select Next to display the input query screen.
SELECT id, dbms_lob.getlength(image) as IMAGE FROM ica_cust_corr;
Click Create to display the Page Designer.
Change the title.
Select Column Image in the left panel, and in right panel change the values for the image column.
Select type - Display Image
Select Table Name ica_custom_corr
Blob Column as IMAGE
Primary Key Column as ID
Save and Run
The screen displays the Scatter Plot Matrix.
Innovate Science
Oracle R
Oracle Data Mining
Collaborate
Full stack development
R Graphics, Chart, Report
Share and Deliver
Gain Insight
Visualize and Share
Open Web Service
View SQL Workshop Object Browser.
Select Packages and Specification.
Select body that creates a classification model using the Decision Tree algorithm.
Key considerations in the code are
Note the default classification algorithm in ODM is Naive Bayes. In order to override, create and populate a settings table to be used as input to the model
Examples of other possible settings are:
(dbms_data_mining.tree_impurity_metric, 'TREE_IMPURITY_ENTROPY')
(dbms_data_mining.tree_term_max_depth, 5)
(dbms_data_mining.tree_term_minrec_split, 5)
(dbms_data_mining.tree_term_minpct_split, 2)
(dbms_data_mining.tree_term_minrec_node, 5)
(dbms_data_mining.tree_term_minpct_node, 0.05)
A cost matrix is used to influence the weighting of mis-classification during model creation and scoring.
Execute the procedure via SQL Command.
Execute Churn Model analysis and view the results.
Here are the results.
SELECT
dbms_data_mining.get_model_details_xml('SL_DT_CHURN_MODEL')
AS DT_DETAILSFROM dual;
Results of the XML is as follows, next step will be to parse the XML and
<PMML version="2.1">
<Header copyright="Copyright (c) 2004, Oracle Corporation. All rights reserved."/>
<DataDictionary numberOfFields="4">
<DataField name="AGE_RANGE" optype="categorical"/>
<DataField name="ANNL_INCOME_RANGE" optype="categorical"/>
<DataField name="CHURN_SCORE" optype="categorical"/>
<DataField name="PARTY_TYPE_CODE" optype="categorical"/>
</DataDictionary>
<TreeModel modelName="SL_DT_CHURN_MODEL" functionName="classification" splitCharacteristic="binarySplit">
<Extension name="buildSettings">
<Setting name="TREE_IMPURITY_METRIC" value="TREE_IMPURITY_GINI"/>
<Setting name="TREE_TERM_MAX_DEPTH" value="7"/>
<Setting name="TREE_TERM_MINPCT_NODE" value=".05"/>
<Setting name="TREE_TERM_MINPCT_SPLIT" value=".1"/>
<Setting name="TREE_TERM_MINREC_NODE" value="10"/>
<Setting name="TREE_TERM_MINREC_SPLIT" value="20"/>
<costMatrix>
<costElement>
<actualValue>0</actualValue>
<predictedValue>0</predictedValue>
<cost>0</cost>
</costElement>
<costElement>
<actualValue>0</actualValue>
<predictedValue>1</predictedValue>
<cost>1</cost>
</costElement>
<costElement>
<actualValue>0</actualValue>
<predictedValue>2</predictedValue>
<cost>2</cost>
</costElement>
<costElement>
<actualValue>0</actualValue>
<predictedValue>3</predictedValue>
<cost>3</cost>
</costElement>
<costElement>
<actualValue>1</actualValue>
<predictedValue>0</predictedValue>
<cost>1</cost>
</costElement>
<costElement>
<actualValue>1</actualValue>
<predictedValue>1</predictedValue>
<cost>0</cost>
</costElement>
<costElement>
<actualValue>1</actualValue>
<predictedValue>2</predictedValue>
<cost>2</cost>
</costElement>
<costElement>
<actualValue>1</actualValue>
<predictedValue>3</predictedValue>
<cost>3</cost>
</costElement>
<costElement>
<actualValue>2</actualValue>
<predictedValue>0</predictedValue>
<cost>3</cost>
</costElement>
<costElement>
<actualValue>2</actualValue>
<predictedValue>1</predictedValue>
<cost>2</cost>
</costElement>
<costElement>
<actualValue>2</actualValue>
<predictedValue>2</predictedValue>
<cost>0</cost>
</costElement>
<costElement>
<actualValue>2</actualValue>
<predictedValue>3</predictedValue>
<cost>1</cost>
</costElement>
<costElement>
<actualValue>3</actualValue>
<predictedValue>0</predictedValue>
<cost>3</cost>
</costElement>
<costElement>
<actualValue>3</actualValue>
<predictedValue>1</predictedValue>
<cost>2</cost>
</costElement>
<costElement>
<actualValue>3</actualValue>
<predictedValue>2</predictedValue>
<cost>1</cost>
</costElement>
<costElement>
<actualValue>3</actualValue>
<predictedValue>3</predictedValue>
<cost>0</cost>
</costElement>
</costMatrix>
</Extension>
<MiningSchema>
<MiningField name="AGE_RANGE" usageType="active"/>
<MiningField name="ANNL_INCOME_RANGE" usageType="active"/>
<MiningField name="CHURN_SCORE" usageType="predicted"/>
<MiningField name="PARTY_TYPE_CODE" usageType="active"/>
</MiningSchema>
<Node id="0" score="2" recordCount="100427">
<True/>
<ScoreDistribution value="2" recordCount="46479"/>
<ScoreDistribution value="1" recordCount="32131"/>
<ScoreDistribution value="3" recordCount="13794"/>
<ScoreDistribution value="0" recordCount="8023"/>
<Node id="1" score="2" recordCount="71604">
<CompoundPredicate booleanOperator="surrogate">
<SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
<Array type="string">"20-29" "40-49" "50-59" "70-79" </Array>
</SimpleSetPredicate>
<SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
<Array type="string">"0k-39k" "40k-59k" "80k-99k" </Array>
</SimpleSetPredicate>
</CompoundPredicate>
<ScoreDistribution value="2" recordCount="46479"/>
<ScoreDistribution value="3" recordCount="13794"/>
<ScoreDistribution value="1" recordCount="11331"/>
<Node id="2" score="2" recordCount="43586">
<CompoundPredicate booleanOperator="surrogate">
<SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
<Array type="string">"40-49" "70-79" </Array>
</SimpleSetPredicate>
<SimpleSetPredicate field="PARTY_TYPE_CODE" booleanOperator="isIn">
<Array type="string">"Cautious Spender" "Mainstream Shoppers" "Money and Brains" </Array>
</SimpleSetPredicate>
</CompoundPredicate>
<ScoreDistribution value="2" recordCount="29792"/>
<ScoreDistribution value="3" recordCount="13794"/>
<Node id="5" score="2" recordCount="41577">
<CompoundPredicate booleanOperator="surrogate">
<SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
<Array type="string">"40-49" </Array>
</SimpleSetPredicate>
<SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
<Array type="string">"0k-39k" "40k-59k" "60k-79k" "80k-99k" </Array>
</SimpleSetPredicate>
</CompoundPredicate>
<ScoreDistribution value="2" recordCount="27783"/>
<ScoreDistribution value="3" recordCount="13794"/>
</Node>
<Node id="6" score="2" recordCount="2009">
<CompoundPredicate booleanOperator="surrogate">
<SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
<Array type="string">"70-79" </Array>
</SimpleSetPredicate>
<SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
<Array type="string">"100k+" </Array>
</SimpleSetPredicate>
</CompoundPredicate>
<ScoreDistribution value="2" recordCount="2009"/>
</Node>
</Node>
<Node id="3" score="2" recordCount="28018">
<CompoundPredicate booleanOperator="surrogate">
<SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
<Array type="string">"20-29" "50-59" </Array>
</SimpleSetPredicate>
<SimpleSetPredicate field="PARTY_TYPE_CODE" booleanOperator="isIn">
<Array type="string">"Livin Large" "Value Seeker" "Young Professional" </Array>
</SimpleSetPredicate>
</CompoundPredicate>
<ScoreDistribution value="2" recordCount="16687"/>
<ScoreDistribution value="1" recordCount="11331"/>
<Node id="7" score="2" recordCount="15567">
<CompoundPredicate booleanOperator="surrogate">
<SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
<Array type="string">"20-29" </Array>
</SimpleSetPredicate>
<SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
<Array type="string">"0k-39k" "80k-99k" </Array>
</SimpleSetPredicate>
</CompoundPredicate>
<ScoreDistribution value="2" recordCount="10398"/>
<ScoreDistribution value="1" recordCount="5169"/>
</Node>
<Node id="8" score="2" recordCount="12451">
<CompoundPredicate booleanOperator="surrogate">
<SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
<Array type="string">"50-59" </Array>
</SimpleSetPredicate>
<SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
<Array type="string">"100k+" "40k-59k" </Array>
</SimpleSetPredicate>
</CompoundPredicate>
<ScoreDistribution value="2" recordCount="6289"/>
<ScoreDistribution value="1" recordCount="6162"/>
</Node>
</Node>
</Node>
<Node id="4" score="1" recordCount="28823">
<CompoundPredicate booleanOperator="surrogate">
<SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
<Array type="string">"30-39" "60-69" </Array>
</SimpleSetPredicate>
<SimpleSetPredicate field="ANNL_INCOME_RANGE" booleanOperator="isIn">
<Array type="string">"100k+" "60k-79k" </Array>
</SimpleSetPredicate>
</CompoundPredicate>
<ScoreDistribution value="1" recordCount="20800"/>
<ScoreDistribution value="0" recordCount="8023"/>
<Node id="9" score="1" recordCount="16772">
<CompoundPredicate booleanOperator="surrogate">
<SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
<Array type="string">"60-69" </Array>
</SimpleSetPredicate>
<SimpleSetPredicate field="PARTY_TYPE_CODE" booleanOperator="isIn">
<Array type="string">"Livin Large" "Value Seeker" </Array>
</SimpleSetPredicate>
</CompoundPredicate>
<ScoreDistribution value="1" recordCount="16772"/>
</Node>
<Node id="10" score="0" recordCount="12051">
<CompoundPredicate booleanOperator="surrogate">
<SimpleSetPredicate field="AGE_RANGE" booleanOperator="isIn">
<Array type="string">"30-39" </Array>
</SimpleSetPredicate>
<SimpleSetPredicate field="PARTY_TYPE_CODE" booleanOperator="isIn">
<Array type="string">"Mainstream Shoppers" "Young Professional" </Array>
</SimpleSetPredicate>
</CompoundPredicate>
<ScoreDistribution value="0" recordCount="8023"/>
<ScoreDistribution value="1" recordCount="4028"/>
</Node>
</Node>
</Node>
</TreeModel>
</PMML>
Parse XML and insert data into a table <RETWSP_TREE_CHURN_RULES> using SQL
Create table RETWSP_TREE_CHURN_RULES as
WITH X as
(SELECT * FROM
XMLTable('for $n in /PMML/TreeModel//Node
let $rf :=
if (count($n/CompoundPredicate) > 0) then
$n/CompoundPredicate/*[1]/@field
else
if (count($n/SimplePredicate) > 0) then
$n/SimplePredicate/@field
else
$n/SimpleSetPredicate/@field
let $ro :=
if (count($n/CompoundPredicate) > 0) then
if ($n/CompoundPredicate/*[1] instance of
element(SimplePredicate)) then
$n/CompoundPredicate/*[1]/@operator
else if ($n/CompoundPredicate/*[1] instance of
element(SimpleSetPredicate)) then
("in")
else ()
else
if (count($n/SimplePredicate) > 0) then
$n/SimplePredicate/@operator
else if (count($n/SimpleSetPredicate) > 0) then
("in")
else ()
let $rv :=
if (count($n/CompoundPredicate) > 0) then
if ($n/CompoundPredicate/*[1] instance of
element(SimplePredicate)) then
$n/CompoundPredicate/*[1]/@value
else
$n/CompoundPredicate/*[1]/Array/text()
else
if (count($n/SimplePredicate) > 0) then
$n/SimplePredicate/@value
else
$n/SimpleSetPredicate/Array/text()
let $sf :=
if (count($n/CompoundPredicate) > 0) then
$n/CompoundPredicate/*[2]/@field
else ()
let $so :=
if (count($n/CompoundPredicate) > 0) then
if ($n/CompoundPredicate/*[2] instance of
element(SimplePredicate)) then
$n/CompoundPredicate/*[2]/@operator
else if ($n/CompoundPredicate/*[2] instance of
element(SimpleSetPredicate)) then
("in")
else ()
else ()
let $sv :=
if (count($n/CompoundPredicate) > 0) then
if ($n/CompoundPredicate/*[2] instance of
element(SimplePredicate)) then
$n/CompoundPredicate/*[2]/@value
else
$n/CompoundPredicate/*[2]/Array/text()
else ()
return
<pred id="{$n/../@id}"
score="{$n/@score}"
rec="{$n/@recordCount}"
cid="{$n/@id}"
rf="{$rf}"
ro="{$ro}"
rv="{$rv}"
sf="{$sf}"
so="{$so}"
sv="{$sv}"
/>'
passing dbms_data_mining.get_model_details_xml('SL_DT_CHURN_MODEL')
COLUMNS
parent_node_id NUMBER PATH '/pred/@id',
child_node_id NUMBER PATH '/pred/@cid',
rec NUMBER PATH '/pred/@rec',
score VARCHAR2(4000) PATH '/pred/@score',
rule_field VARCHAR2(4000) PATH '/pred/@rf',
rule_op VARCHAR2(20) PATH '/pred/@ro',
rule_value VARCHAR2(4000) PATH '/pred/@rv',
surr_field VARCHAR2(4000) PATH '/pred/@sf',
surr_op VARCHAR2(20) PATH '/pred/@so',
surr_value VARCHAR2(4000) PATH '/pred/@sv'))
select pid parent_node, nid node, rec record_count,
score prediction, rule_pred local_rule, surr_pred local_surrogate,
rtrim(replace(full_rule,'$O$D$M$'),' AND') full_simple_rule from (
select row_number() over (partition by nid order by rn desc) rn,
pid, nid, rec, score, rule_pred, surr_pred, full_rule from (
select rn, pid, nid, rec, score, rule_pred, surr_pred,
sys_connect_by_path(pred, '$O$D$M$') full_rule from (
select row_number() over (partition by nid order by rid) rn,
pid, nid, rec, score, rule_pred, surr_pred,
nvl2(pred,pred || ' AND ',null) pred from(
select rid, pid, nid, rec, score, rule_pred, surr_pred,
decode(rn, 1, pred, null) pred from (
select rid, nid, rec, score, pid, rule_pred, surr_pred,
nvl2(root_op, '(' || root_field || ' ' || root_op || ' ' || root_value || ')', null) pred,
row_number() over (partition by nid, root_field, root_op order by rid desc) rn from (
SELECT
connect_by_root(parent_node_id) rid,
child_node_id nid,
rec, score,
connect_by_root(rule_field) root_field,
connect_by_root(rule_op) root_op,
connect_by_root(rule_value) root_value,
nvl2(rule_op, '(' || rule_field || ' ' || rule_op || ' ' || rule_value || ')', null) rule_pred,
nvl2(surr_op, '(' || surr_field || ' ' || surr_op || ' ' || surr_value || ')', null) surr_pred,
parent_node_id pid
FROM (
SELECT parent_node_id, child_node_id, rec, score, rule_field, surr_field, rule_op, surr_op,
replace(replace(rule_value,'" "', ''', '''),'"', '''') rule_value,
replace(replace(surr_value,'" "', ''', '''),'"', '''') surr_value
FROM (
SELECT parent_node_id, child_node_id, rec, score, rule_field, surr_field,
decode(rule_op,'lessOrEqual','<=','greaterThan','>',rule_op) rule_op,
decode(rule_op,'in','('||rule_value||')',rule_value) rule_value,
decode(surr_op,'lessOrEqual','<=','greaterThan','>',surr_op) surr_op,
decode(surr_op,'in','('||surr_value||')',surr_value) surr_value
FROM X)
)
CONNECT BY PRIOR child_node_id = parent_node_id
)
)
)
)
CONNECT BY PRIOR rn = rn - 1
AND PRIOR nid = nid
START WITH rn = 1
)
)
where rn = 1;
This sql is also listed in SQL Scripts.
Save the churn rules by creating table RETWSP_TREE_CHURN_RULES as (<sql above>).
Create an application to display these rules.
Select Churn Analysis from App Builder.
Create a page.
Select the Tree to display.
Populate Page Name as Churn Rules and click Next.
Select Create a new navigation menu entry.
Select RETWSP_TREE_CHURN_RULES from Table/View name list.
You see Page Designer.
Replace the following query to better format and execute the run.
select case when connect_by_isleaf = 1 then 0
when level = 1 then 1
else -1
end as status,
level,
nvl(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(LOCAL_RULE, ' - '), RECORD_COUNT), ' - '), LOCAL_SURROGATE), '===>'), PREDICTION), 'All Customers') as name,
'icon-tree-folder' as icon,
"NODE" as value,
nvl(FULL_SIMPLE_RULE, 'Not available') as tooltip,
null as link
from "#OWNER#"."RETWSP_TREE_CHURN_RULES"
start with "PARENT_NODE" is null
connect by prior "NODE" = "PARENT_NODE"
order siblings by "RECORD_COUNT"
Run a new window
View RESTful Services
Create ReST API, which you can use to load customer reviews in the above table.
Click Create.
Populate with Name, URI Prefix, and URI Template.
Select GET and populate the SQL as:
SELECT PARENT_NODE, NODE, RECORD_COUNT, PREDICTION, LOCAL_RULE, LOCAL_SURROGATE, FULL_SIMPLE_RULE FROM RETWSP_TREE_CHURN_MODEL
Select GET and then click Test.
Data is displayed in the window and any request to this end point http:/<url> will return the data shown in Figure A-79 in JSON format.
This exercise illustrates how to add link to the application Task Navigation.
As an INSIGHT_APPLICATION_ADMINISTATOR, you can add a new task to the Tasks.
Click Customization.
The Content Area displays Navigation XML. At the end of the XML, add the following content (see Customer Analytics) Update the highlighted contents.
<Item id="CUSTID" title="Customer Analytics" type="folder" visible="true"> <Items> <Item id="custana" title="Customer Churn Analysis" type="link" target="_blank"> <url>http://<url>/ords/f?p=100:1:8094585040758:::::</url> <Parameters/> </Item> </Items> </Item>
Click Save. Log out and then log back in. You should see:
Click the Customer Churn Analysis link system. A new tab is launched in the browser for the end user.
None of the tables in Retailer Workspace Schema are accessible in Application Schema.
Executing queries in Application Schema will result in an error. Until and unless access is provided, the user will not be able to access object/data in another schema.
describe retwsp_demo_1.p_churn_model; ERROR: ORA-04043: object retwsp_demo_1.p_churn_model does not exist select * from RETWSP_TREE_CHURN_RULES; ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause: *Action: Error at Line: 1 Column: 15
Long running jobs must be executed under RETAILER_WORKSPACE_JOBS. These are executed in resource groups dedicated to the retailer schema.
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'retwsp_churn_model', job_type => 'STORED_PROCEDURE', job_action => 'retwsp.pkg_customer_analytics.proc_churn_model', start_date => '01-JAN-17 07.00.00 PM US/Pacific', repeat_interval => 'FREQ=YEARLY; BYDATE=0331,0630,0930,1231; ', end_date => '31-DEC-17 07.00.00 PM US/Pacific', job_class => 'RETAILER_WORKSPACE_JOBS', comments => 'Retailer workspace churn model job'); END; /
