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]

Is it that complex to have section access for multiple fields?

[luciancotea]

Well, if you can combine your fields under a single hierarchy, you can specify a single node for them.

For example, if your 4 fields are:

CONTINENT - COUNTRY - REGION - PRODUCT CATEGORY

you can group first 3 fields into one hierarchy and then your security model would only have to specify

HIERARCHY NODE - PRODUCT CATEGORY

[RedSky001]

I ran into the same issue.  Once you get past 2 fields you need to start creating a lot of load statements and it's easy to screw it up.  so I wrote a script to create all the permutations, (for my dashboard I have 8 fields so 256 load statements are  required).

Anyway I know this post was some time ago but I've attached my modified script, hope you or someone finds this useful.

Mark

// Made up fact data

  FACT:

  load * Inline [ AuthIDTemp ,COMPA ,COMPB ,COMPC ,COMPD

  1 ,A ,B ,C ,D

  2 ,A ,B ,C ,D

  3 ,A ,B ,C ,D

  4 ,A ,B ,C ,D

  5 ,A ,B ,C ,D

  6 ,A ,B ,C ,D

  7 ,A ,B ,C ,D

  8 ,A ,B ,C ,D

  9 ,A ,B ,C ,D

  10 ,A ,B ,C ,D

  11 ,A ,B ,C ,D

  12 ,A ,B ,C ,D

  13 ,A ,B ,C ,D

  14 ,A ,B ,C ,D

  15 ,A ,B ,C ,D

  16 ,A ,B ,C ,D

  17 ,A ,B ,C ,D

  18 ,A ,B ,C ,D

  ];

  NoConcatenate

  Temp:

  load * Inline [ Field1

  X

  ALL

  ];

  NoConcatenate

  TblCombinaitons:

  load Field1 as Field1 Resident Temp;

  join load Field1 as Field2 Resident Temp;

  join load Field1 as Field3 Resident Temp;

  join load Field1 as Field4 Resident Temp;

  drop Table Temp;

  NoConcatenate

  SQL_TABLE:

  load

  if(Field1='X','COMPA',chr(39)&Field1&chr(39)) as Field1

  ,if(Field2='X','COMPB',chr(39)&Field2&chr(39)) as Field2

  ,if(Field3='X','COMPC',chr(39)&Field3&chr(39)) as Field3

  ,if(Field4='X','COMPD',chr(39)&Field4&chr(39)) as Field4

  Resident TblCombinaitons;

  drop Table TblCombinaitons;

  CountTable:

  load count(Field1) as FieldNameCount  Resident SQL_TABLE;

  let vRowCount = peek('FieldNameCount',0,'CountTable');

  drop Table CountTable;

  Temp:

  NoConcatenate

  load Distinct AuthIDTemp

  ,COMPA

  ,COMPB

  ,COMPC

  ,COMPD

  Resident FACT;

  AuthorizationBridgeTemp:

  Load distinct AuthIDTemp as %AuthIDTemp

  ,AuthIDTemp as AuthIDTemp

  resident Temp ;

  FOR i = 0 to vRowCount -1

  vFieldValue1 = peek('Field1',$(i),'SQL_TABLE');

  vFieldValue2 = peek('Field2',$(i),'SQL_TABLE');

  vFieldValue3 = peek('Field3',$(i),'SQL_TABLE');

  vFieldValue4 = peek('Field4',$(i),'SQL_TABLE');

    Concatenate(AuthorizationBridgeTemp)

    load distinct $(vFieldValue1) & '|' & $(vFieldValue2) & '|' & $(vFieldValue3) & '|' & $(vFieldValue4)  as %AuthIDTemp

    ,AuthIDTemp Resident Temp;

  Next i;

  drop Tables SQL_TABLE, Temp