section access more than one field

[gauthamchilled]

Hi,

I have gone thru HIC's blog on section access reduction in more than one field. Not sure how to build the following auth keys for all possible combinations.

how to get all possible combinations for the below like

COMPA	COMPB	COMPC	COMPD
1	1	1	ANY
1	1	ANY	ANY

-                 -                -                   -

i know the script for this to concatnate and get the auth key for the bridge table. but how to generate the combinations easily..

not sure how many combinations it will have also. please help.

[gauthamchilled]

hic‌ can you please help? Is it only 4 combinations we get if we have reduce for 4 fields?

Is it not require to load all possible key combinations for section using multiple fields?

[cesaraccardi]

That is a good point, you should add that key as well:

LOAD DISTINCT

     '<ANY>|<ANY>|<ANY>|<ANY>' as %AuthKey,

     COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD    as %FactKey

RESIDENT FACT;

[gauthamchilled]

COMPA & '|<ANY>|<COMPC>|<ANY>' as %AuthKey? THERE ARE many key combinations right?

[cesaraccardi]

When you concatenate COMPA with <ANY>|<ANY>|<ANY> it means the list of values for COMPA plus any given value for the other fields, so that already includes COMPA & '|<ANY>|<COMPC>|<ANY>'.

[cesaraccardi]

Sorry, in your access table when it says COMPA = 1, COMPB = 1, etc... Does that mean:

1. COMPA = 1 AND COMPB = 1?
2. COMPA = 1 OR COMPB = 1?

Because the load script I provided is for #2.

[gauthamchilled]

COMPA = 1 AND COMPB = 1

[gauthamchilled]

OH..what is for case 1 then?

[gauthamchilled]

anyone help?

[luciancotea]

If you have '_' for 'ANY'

and

X for each COMP value

then

Load XXXX as Key from Transactions

Load _XXX as Key from Transactions

Load X_XX as Key from Transactions

Load XX_X as Key from Transactions

Load XXX_ as Key from Transactions

Load __XX as Key from Transactions

Load XX__ as Key from Transactions

Load _XX_ as Key from Transactions

Load X_X_ as Key from Transactions

Load _X_X as Key from Transactions

Load X__X as Key from Transactions

Load ___X as Key from Transactions

Load __X_ as Key from Transactions

Load _X__ as Key from Transactions

Load X___ as Key from Transactions

Load ____ as Key from Transactions

After that, to exclude all extra combinations, you reload again the result using a method to reduce the rows (KEEP, EXISTS, INNER JOIN,...)

[cesaraccardi]

In that case you can load the concatenation of keys from Fact table and do a Cartesian join with the keys from the section access table (this can generate a very large table depending on the number of values you have in your data set but will have all possible combinations against each key):

TMP_KEYS:

LOAD DISTINCT

     COMPA     as FACT_COMPA,

     COMPB     as FACT_COMPB,

     COMPC     as FACT_COMPC,

     COMPD     as FACT_COMPD,

     COMPA & '|' & COMPB & '|' & COMPC & '|' & COMPD    as %FactKey

RESIDENT FACT;

JOIN(TMP_KEYS)

LOAD

     COMPA     as ACCESS_COMPA,

     COMPB     as ACCESS_COMPB,

     COMPC     as ACCESS_COMPC,

     COMPD     as ACCESS_COMPD,

     %AuthKey

RESIDENT SECTION_ACCESS;

Now you do a resident load and get rid of the keys you are not interested (ANY values):

BRIDGE:

LOAD

     %FactKey,

     %AuthKey

RESIDENT

     TMP_KEYS

WHERE

     (FACT_COMPA = ACCESS_COMPA OR ACCESS_COMPA = 'ANY') AND

     (FACT_COMPB = ACCESS_COMPB OR ACCESS_COMPB = 'ANY') AND

     (FACT_COMPC = ACCESS_COMPC OR ACCESS_COMPC = 'ANY') AND

     (FACT_COMPD = ACCESS_COMPD OR ACCESS_COMPD = 'ANY');

DROP TABLE TMP_KEYS;