Basic Data Miner workflow
June 2019
Agenda
• What is Data Miner?
• How to install it?
• How to connect to ICA?
• Demo: Install and connect
• Workflows
• Template workflow
• Demo: Template workflow
• Exercises
What is Data Miner?
• A GUI to the Data Mining capabilities in the DB
• Data Mining is about discovering patterns in the data,
using statistics and machine learning
• More on this in the Advanced course
• Also a GUI to standard SQL functionality
• Read from and write to tables
• Filters
• Joins
• Aggregation
• etc
How to install it?
• Part of SQL*Developer
• Download from ICC Software Catalogue (version 17.3)
How to connect to ICA?
• Create a DB connection to your ORE_xxx user or the
ICAcc super user in ICA
• Add the DB connection in Data Miner
Install and connect
Data Miner to ICA
Demo
Workflows
• A workflow is a ”job” in Data Miner, that can also be
scheduled
• Consists of the functionality described earlier, shown as
connected building blocks
• You can build tables in your schema that contains
results of the workflow
• Will show this using a template workflow
Template workflow (1)
• Task: For all customers of a certain program (such as
FAMILY), the sales (amount and quantity) from a certain
date back in time until now is summed up per ZIP code
for the customer and till ID (purchase origin). The result
is placed in the TRANSACTION_PROFILE table. Do this for
Sweden data.
• Note: This might be of no use at all, a little use, or an
inspiration to other tasks
• It’s a way to get you started with Data Miner and the ICA data
Template workflow (2)
• This is a simplified version of Transaction_profile_data
template
• As shown in another session along with its documentation
• We will use MDB tables
• Input:
• Data Model
• Data Dictionary
Template workflow (3)
Template workflow (4)
• We could read directly from the MDB tables, but then
every country would have its own workflow
• Instead: Create views for the MDB tables, place them in
the ORE_xxx schema
Template workflow (5)
CREATE OR REPLACE VIEW indiv_profl AS SELECT * FROM MDBSE.indiv_profl;
CREATE OR REPLACE VIEW loyalty_progs AS SELECT * FROM MDBSE.loyalty_progs;
CREATE OR REPLACE VIEW loyalty_prog_profl AS SELECT * FROM
MDBSE.loyalty_prog_profl;
CREATE OR REPLACE VIEW cust_xref AS SELECT * FROM MDBSE.cust_xref;
CREATE OR REPLACE VIEW pos_trx_dtl AS SELECT * FROM MDBSE.pos_trx_dtl;
CREATE OR REPLACE VIEW retl_prod_profl AS SELECT * FROM MDBSE.retl_prod_profl;
CREATE OR REPLACE VIEW retl_dept_sub_cls_profl AS SELECT * FROM
MDBSE.retl_dept_sub_cls_profl;
CREATE OR REPLACE VIEW retl_dept_cls_profl AS SELECT * FROM
MDBSE.retl_dept_cls_profl;
CREATE OR REPLACE VIEW retl_dept_profl AS SELECT * FROM MDBSE.retl_dept_profl;
Template workflow (6)
Create the output table as
CREATE TABLE transaction_profile
( zip_cd_base VARCHAR2(40 BYTE),
till_id VARCHAR2(120 BYTE),
purch_amt_sum NUMBER,
purch_qty_sum NUMBER,
insert_date DATE DEFAULT SYSDATE
);
Template workflow
Demo
Questions
Exercise 1
• Create the TRANSACTION_PROFILE table and the
MDB table views, for your market.
• Run the workflow to get the results in
TRANSACTION_PROFILE.
16
Exercise 2 (1)
• Task: Modify the TRANSACTION_PROFILE_DATA
workflow like this:
• Make sure that the enrolment date for the card
used in each purchase is not older than two years
from now.
• Hint: Use a filter. The function ADD_MONTHS(a,b)
returns the date a+b, where a is a date and b is the
number of months to add (can also be negative). The
function SYSDATE returns the current date.
• Rather than grouping the result on ZIP code and
till ID, group on ZIP code and BU code (store
number).
17
Exercise 2 (2)
1. Create the table TRANSACTION_PROFILE_NEW
as:
CREATE TABLE transaction_profile_new
(zip_cd_base VARCHAR2(40 BYTE),
bu_code VARCHAR2(5 BYTE),
purch_amt_sum NUMBER,
purch_qty_sum NUMBER,
insert_date DATE DEFAULT SYSDATE
);
2. Import the Transaction_profile_simple workflow
as Transaction_profile_simple_ex.
18
Exercise 2 (3)
3. Do the work!
4. Run the workflow to get the results in
TRANSACTION_PROFILE_NEW. When looking in
the table, order by descending amount.
19
