ORACLE Data Mining

1. ORACLE Data Mining • Included with ORACLE 10g • Freely available for UVic and for personal unlimited evaluation use. Choose the enterprise version!

2. Creating a data mining user • A data mining user is a canonical user, but with a few more privileges than usual. • As ‘system’ create a canonical user: CREATE USER dmuser1 IDENTIFIED BY dmpsw DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED on users; • Then, grant privileges to this user: • GRANT create procedure to DMUSER1; • GRANT create session to DMUSER1; • GRANT create table to DMUSER1; • GRANT create sequence to DMUSER1; • GRANT create view to DMUSER1; • GRANT create job to DMUSER1; • GRANT create type to DMUSER1; • GRANT create synonym to DMUSER1; • GRANT execute on ctxsys.ctx_ddl to DMUSER1; Now, DMUSER1 is ready to perform data mining tasks.

3. ‘sh’ user • To use the examples you have to activate the sh user. • There are the example data • Created during ORACLE installation. • However, it’s not active. • To activate: Open Oracle Enterprise Manager. Login as ‘system’. Go to ‘administration’ tab, then click on users, find the ‘sh’ user and activate it.

4. Using the data of ‘sh’ • Connect (login) as sh and grant SELECT privilege to dmuser1 for these tables: • COUNTRIES • CUSTOMERS • PRODUCTS • SUPPLEMENTARY_DEMOGRAPHICS • SALES • In other words execute: • GRANT SELECT ON customers TO dmuser1; • GRANT SELECT ON sales TO dmuser1; • GRANT SELECT ON products TO dmuser1; • GRANT SELECT ON supplementary_demographics TO dmuser1; • GRANT SELECT ON countries TO dmuser1; • Now, dmuser1 is able to execute SELECT on these tables, and create views on them.

5. DMUSER1: Creating views on ‘sh’ data CREATE VIEW mining_data_build_v AS SELECT a.CUST_ID, a.CUST_GENDER, 2003-a.CUST_YEAR_OF_BIRTH AGE, a.CUST_MARITAL_STATUS, c.COUNTRY_NAME, a.CUST_INCOME_LEVEL, b.EDUCATION, b.OCCUPATION, b.HOUSEHOLD_SIZE, b.YRS_RESIDENCE, b.AFFINITY_CARD, b.BULK_PACK_DISKETTES, b.FLAT_PANEL_MONITOR, b.HOME_THEATER_PACKAGE, b.BOOKKEEPING_APPLICATION, b.PRINTER_SUPPLIES, b.Y_BOX_GAMES, b.OS_DOC_SET_KANJI FROM sh.customers a, sh.supplementary_demographics b, sh.countries c WHERE a.CUST_ID = b.CUST_ID AND a.country_id = c.country_id AND a.cust_id between 101501 and 103000; • You can find other view creation statements in dmsh.sql that will be in the web.

6. Oracle Data Miner GUI • It’s a Java application. Connects through JDBC to the ORACLE database. • Installed in SENG Windows lab. • Can also be freely downloaded from Oracle.

7. Connecting

8. The Problem • An electronics store chain wants to distribute a discount card to its customers, but only to those customers who are expected to increase their buying (and thus the company’s revenue) because of this card. • A test campaign was run on a sample of customers and the results were compiled into a table containing the customer demographics, purchasing patterns, and a measure of revenue produced by each customer.

9. Structure

10. A fragment of data

11. Getting a feeling about the data

12. Build a classifier I

13. Build a classifier II

14. Build a classifier III

15. Build a classifier IV I am selecting this

16. Build a classifier V

17. Build a classifier VI

18. Build a classifier VII

19. Build a classifier VIII P(A=1|…) will be used for sorting tuples, as opposed to P(A=0|…).

20. Build a classifier IX

21. Build a classifier X