How can i run APRIORIRULE algorithm for different markets

[Former Member]

Hi all,

According to my research i found the below code for basket analysis.

APRIORIRULE

SET SCHEMA TEST; -- cleanup DROP TYPE PAL_T_AP_DATA; DROP TYPE PAL_T_AP_PARAMS; DROP TYPE PAL_T_AP_RULES; DROP TYPE PAL_T_AP_PMML; DROP TABLE PAL_AP_SIGNATURE; CALL SYSTEM.AFL_WRAPPER_ERASER ('PAL_AP'); DROP TABLE AP_RULES; DROP TABLE AP_PMML; -- PAL setup CREATE TYPE PAL_T_AP_DATA AS TABLE (ORDERID INTEGER, PRODUCTID INTEGER); CREATE TYPE PAL_T_AP_PARAMS AS TABLE (NAME VARCHAR(60), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR (100)); CREATE TYPE PAL_T_AP_RULES AS TABLE (PRERULE VARCHAR(500), POSTRULE VARCHAR(500), SUPPORT DOUBLE, CONFIDENCE DOUBLE, LIFT DOUBLE); CREATE TYPE PAL_T_AP_PMML AS TABLE (ID INTEGER, PMMLMODEL VARCHAR(5000)); CREATE COLUMN TABLE PAL_AP_SIGNATURE (ID INTEGER, TYPENAME VARCHAR(1000), DIRECTION VARCHAR(100)); INSERT INTO PAL_AP_SIGNATURE VALUES (1, 'TEST.PAL_T_AP_DATA', 'in'); INSERT INTO PAL_AP_SIGNATURE VALUES (2, 'TEST.PAL_T_AP_PARAMS', 'in'); INSERT INTO PAL_AP_SIGNATURE VALUES (3, 'TEST.PAL_T_AP_RULES', 'out'); INSERT INTO PAL_AP_SIGNATURE VALUES (4, 'TEST.PAL_T_AP_PMML', 'out'); CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_AP', 'AFLPAL', 'APRIORIRULE', PAL_AP_SIGNATURE); DROP VIEW V_AP_DATA; -- app setup CREATE VIEW V_AP_DATA AS SELECT ORDERID,PRODUCTID FROM MARKETSALES; CREATE COLUMN TABLE AP_RULES LIKE PAL_T_AP_RULES; CREATE COLUMN TABLE AP_PMML LIKE PAL_T_AP_PMML; -- app runtime DROP TABLE #AP_PARAMS; CREATE LOCAL TEMPORARY COLUMN TABLE #AP_PARAMS LIKE PAL_T_AP_PARAMS; INSERT INTO #AP_PARAMS VALUES ('THREAD_NUMBER', 1, null, null); INSERT INTO #AP_PARAMS VALUES ('MIN_SUPPORT', null, 0.1, null); INSERT INTO #AP_PARAMS VALUES ('MIN_CONFIDENCE', null, 0.8, null); --INSERT INTO #AP_PARAMS VALUES ('MIN_LIFT', null, 1.0, null); INSERT INTO #AP_PARAMS VALUES ('MAX_ITEM_LENGTH', 5, null, null); --INSERT INTO #AP_PARAMS VALUES ('MAX_CONSEQUENT', 1, null, null); INSERT INTO #AP_PARAMS VALUES ('PMML_EXPORT', 0, null, null); -- NEW FROM SPS08 INSERT INTO #AP_PARAMS VALUES ('IS_USE_PREFIX_TREE', 0, null, null); -- 0: no, 1: yes --INSERT INTO #AP_PARAMS VALUES ('LHS_RESTRICT', null, null, '37'); --INSERT INTO #AP_PARAMS VALUES ('LHS_RESTRICT', null, null, '43'); --INSERT INTO #AP_PARAMS VALUES ('RHS_IS_COMPLEMENTARY_LHS', 0, null, null); -- 0: no, 1: yes --INSERT INTO #AP_PARAMS VALUES ('RHS_RESTRICT', null, null, '10'); --INSERT INTO #AP_PARAMS VALUES ('RHS_RESTRICT', null, null, '11'); --INSERT INTO #AP_PARAMS VALUES ('LHS_IS_COMPLEMENTARY_RHS', 0, null, null); -- 0: no, 1: yes TRUNCATE TABLE AP_RULES; TRUNCATE TABLE AP_PMML; CALL _SYS_AFL.PAL_AP (V_AP_DATA, #AP_PARAMS, AP_RULES, AP_PMML) WITH OVERVIEW; --SELECT * FROM V_AP_DATA; SELECT * FROM AP_RULES ORDER BY PRERULE, POSTRULE; --SELECT * FROM AP_PMML;

I was searching a way to do this for different markets. I mean in MARKETSALES table lets say i have, marketid, orderid and productid. If i run the above code the result should be for all markets.

So, 1 solution is using SELECT ORDERID,PRODUCTID FROM MARKETSALES WHERE marketid=?; But in this way, i have to run the code for all markets. And save their data manually. Is there any way to do it automatically for different markets?

And also after i finish the code i will publish it to lumira. So that for the below table i think that i should also add the parameter marketid for seperating the basket analysis. So that my user can filter by marketid and choose the post/pre rule for creating the charts using lumira.

PAL_T_AP_RULES AS TABLE (MARKETID INTEGER,PRERULE VARCHAR(500), POSTRULE VARCHAR(500), SUPPORT DOUBLE, CONFIDENCE DOUBLE, LIFT DOUBLE);

Any idea about this issue?

[achab]

Hi Kemal,

See Xingtian's answer - can you please confirm this answers your question?

Thanks & regards

Antoine

[Former Member]

Hi Kemal , For running in different markets, one solution is to create a stored procedure with MARKET_ID as input, in the procedure, select the data and pass it to the PAL procedure. Of course, this is not so automatic. If you want to run different markets in parallel, on solution might be to cal the procedure with selected data on the application layer (e.g., XS engine, ...) with parallel procedure call. If it is not required to run it in parallel, you can write a for loop in the stored procedure, of which the performance might not be very good.

Best regards,

Xingtian 

[achab]

Hi, I am adding for the PAL team as this is a PAL related question.

Thanks & regards,

Antoine