A Appendix: Innovation Workbench Workshop
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.
Overview
Open Analytics: understanding 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 answer 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 in Figure A-4)
-
Retailer workspace
-
SQL Workshop
-
Application Builder
The Retailer Schema has read-only access to the Application Schema. All permissions are controlled using database permissions.
Roles for the analyst, scientist, developer. and integration are as follows:
-
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 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.
Lab Innovation Workbench
-
Login using http:// < url>
Login/Password
This takes you to the application.
-
Select Innovation Workbench.
-
Select Manage Innovation Workbench.
This takes you to Retailer Workspace where you can log in with same login and password credentials.
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-running transactions
-
-
SQL Workshop
-
Application Builder
-
Lab Analysis
Browse Existing Data
In this exercise you will browse the Application schema objects using SQL Workshop.
-
Access Read Only
-
Cleansed
-
Aggregated
-
Filtered
-
Sample
-
Inputs and Outputs
Figure A-18 SQL Workshop Object Browser Synonym
Description of "Figure A-18 SQL Workshop Object Browser Synonym"
Note that this dedicated schema is only associated with the Application Schema and cannot be extended to any other new schema.
Data
This SQL has been saved, so you can type sql in the command and execute and save it.
Figure A-20 SQL Workshop SQL Command Save SQL
Description of "Figure A-20 SQL Workshop SQL Command Save SQL"
Figure A-21 SQL Workshop SQL Command Saved SQL View Customer Attributes
Description of "Figure A-21 SQL Workshop SQL Command Saved SQL View Customer Attributes"
Using this you can do the following:
-
Upload Scripts
-
Create Scripts
-
Edit Scripts
-
Load data
-
Use ETL feeds
-
Use system feeds whereever possible. If the application feed is not available, then ReST objects can be used.
Using ReST to Load Data
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;
Other Methods for Loading Data
These methods must be used judiciously, with smaller datasets.
Prepare Data
Key components that can be used for refining data are Create Train and Test dataset.
The followings SQL shows how stratified split is used to 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 a 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));
Explore Data Analysis
Now we have customer reviews we will review, build text to find document frequencies, and display them in charts. We will further extract key text for each review.
Review Customer Behavior Analysis using Text Mining
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.
Innovate
Churn Analysis using ODM
-
View SQL Workshop Object Browser.
-
Select Packages and Specification.
-
Select the 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.
Decision Tree Model Details
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>).
Display Decision Tree Rules
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
Lab Share Insights - ReST API
-
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-67 in JSON format.
Lab Share Insights - Task Navigation
This exercise illustrates how to add a 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.
Database Tools
How to Allocate Privileges
None of the tables in Retailer Workspace Schema are accessible in the Application Schema.
Executing queries in the 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
How to Execute a Job using DBMS Scheduler
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; /