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]

JUST a help to generate all key combinations to create auth key.anyone help?

[cesaraccardi]

Hi,

Can't you just do a resident load from the fact table to get all possible values for each field?

Cesar

[cesaraccardi]

A load 'distinct' should work

[Chanty4u]

what ever the section access script  hve u can  load and  do resident load.

u will get those as per requirmnt

and  all shud be in Upper case.

[gauthamchilled]

But HIC is doing multiple times

in generic keys pdf attached page no 13

[gauthamchilled]

since i am using 4 fields reduction. i cannot use section access script table directly. I am creating authorization table and auth bridge table. so confusion to get all possible key combinations

[cesaraccardi]

Hi,

If you have 4 reduction fields then you have to load 4 times:

LOAD DISTINCT

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

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

RESIDENT FACT;

LOAD DISTINCT

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

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

RESIDENT FACT;

LOAD DISTINCT

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

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

RESIDENT FACT;

LOAD DISTINCT

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

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

RESIDENT FACT;

And you create %FactKey on the fact table as well to do the linkage.

[cesaraccardi]

You can add a WHERE clause to each loading as well to reduce the size of the Authorisation bridge table:

LOAD DISTINCT

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

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

RESIDENT FACT WHERE NOT ISNULL(COMPA);

LOAD DISTINCT

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

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

RESIDENT FACT WHERE NOT ISNULL(COMPB);

LOAD DISTINCT

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

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

RESIDENT FACT WHERE NOT ISNULL(COMPC);

LOAD DISTINCT

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

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

RESIDENT FACT WHERE NOT ISNULL(COMPD);

[gauthamchilled]

OK.

so only 4 combinations?

'<ANY>|<ANY>|<ANY>|<ANY>' is not there?