Section Access & Access Point

[ryam]

I've scoured the community for help with this, but I can't seem to get it to work.

Here is my script below:

SECTION ACCESS;
LOAD 
	UPPER(ACCESS) AS ACCESS,
	UPPER(NTNAME) AS NTNAME, 
	UPPER(STORE_REGION_NUM) & '|' & UPPER(STORE_CNTRY_DESC) AS REDUCTION
FROM [..\Section Access\Section Access.xlsx]
(ooxml, embedded labels, table is SDD);
Section Application;

Section Access Table:

I have the following to options checked.

When I open up the application through the access point, I receive an error saying I have no access.

Anybody have any ideas?

Thank you.

[rwunderlich]

What user are you logging in as?  If you logged in as USER1, that would make a REDUCTION value of *|*.  Do you have a corresponding REDUCTION value of *|* in a Section Application table?

-Rob

[ryam]

*|* does not exist in any table, however I did create a table of all possible combinations for REDUCTION.

I can see why I'm getting an access denied since *|* does not exist on the section application tables.  How would I get this to work?  I'm I'm logging in as USER1, I would want to have zero data reduction.

If I'm logging in as USER5, I would only want data reduction for REGION 1 with the country unrestricted to REGION 1 countries.

If I'm logging in as USER7, I should only have access to ITALY which resides within REGION 2.

Section_Access_Table:
LOAD DISTINCT
	'ADMIN' AS ACCESS,
	'ADMIN' AS NTNAME,
	UPPER(STORE_REGION_NUM) & '|' & UPPER(STORE_CNTRY_DESC) AS REDUCTION
FROM [..\SDD_Vertica\Metadata\Store.QVD](qvd);

CONCATENATE
LOAD 
	UPPER(ACCESS) AS ACCESS,
	UPPER(NTNAME) AS NTNAME, 
	UPPER(STORE_REGION_NUM) & '|' & UPPER(STORE_CNTRY_DESC) AS REDUCTION
FROM [..\Section Access\Section Access.xlsx]
(ooxml, embedded labels, table is SDD);

SECTION ACCESS;
LOAD
	*
RESIDENT Section_Access_Table;
DROP TABLES Section_Access_Table;

SECTION APPLICATION;

[rwunderlich]

In this case, "*|*" is a literal value, it is not a wildcard.  You will need to add "*|*", linked to every row, in your section application REDUCTION field. 

There are others on this forum more experienced than me with Section Access. Perhaps they will chime in with a better solution.

-Rob

[marcus_sommer · Accepted solution]

Your reduction-fields seems to be hierarchically to each other. If so you need only the most granular field by avoiding the efforts to combine both - which like Rob mentioned couldn't be done with *|* because it's now a string.

Beside this using the * wildcard seems to be very convenient in defining the section access rules and will work within the most scenarios but it has some disadvantages, too - like:

• the wildcard respects only within the section access listed values
• NULL within the linked reduction-field is also never included
• the wildcard-records from the section access itself will be dropped and lead to a data-reduction

 Quite often this behaviour won't be noticed neither from the admin/development nor from the usage-side.

I'm not sure if it's really sensible to invest the needed efforts to avoid any data-reduction even for the users which should see all data because usually it relates to quite few records which are quite old or very new and not properly maintained yet or those with a low data-quality.

In the end it means you would need to check all reduction-values from the datamodel and the section access by replacing all invalid values or removing them and listing all directly within the section access without using the wildcards. Especially if the dataset shouldn't be controlled by a single field else multiple ones it could become quite expensive.

Therefore try to keep it as simple as possible. This could mean to separate the requirements into two or more applications instead of trying to implement multiple (and maybe even opposite) section access rules. Further no special concern about a possible data-reduction - you could check which records are removed and if they have any relevance - by using the wildcard on a single field (needs an appropriate combining) and if really needed a value-listing for the user: NONE.

With NONE is a non-existing user meant which only purpose is to get the list of all existing and valid values assigned - you may get it with a distinct load over the containing table or also per fieldvalue() from the system-table (by a multi-layer environment you may create it within a table in beforehand) - and then you could apply * as wildcard for those users which should see (nearly) everything. 

- Marcus

[ryam]

This worked perfectly.  I was over complicating it by trying to incorporate 2 reduction fields.