section access not working for admin users with reduction

[daveatkins]

I have tried to simplify the administration of section access by parsing an excel spreadsheet containing access rules into a section access table which avoids the manager of access having to input multiple rows for every user. It works for users, and it works for me because I hard code my own user in as the first row of section access, but it fails for all the other admin users.

First of all, what is the correct way to enter the admin users when there is a reduction column? I have tried this with the reduction column included and a * but the column does not contain all possible values, so I really need these users to be ADMIN role so they can see all data. In my latest attempt, I first add the admin users, without a reduction column, then concatenate on the other users later in the script.

I will just post the whole part of the load script here; maybe my whole idea of building up the access table is just not feasible. But it almost works, as I said, the user level access works and my own (da14) user works.

In the code, Region is a table previously loaded in the app containing the region and subregion for a list of facilities. file.xls is spreadsheet with users and columns what limitations by region, subregion, facility, or "All" override. 

when I comment out section access and view the generated tmpAccessList table, it appears as I intend and I can see no difference between my da14 entry and the list of additional all access admin users. I have copy/pasted the resulting table export into my load script and it works.

Section Access;

UserList:
LOAD
    upper("User id") as User,
    "Access Limits",
    Region,
    SubRegion,
    UPPER(Facility) AS Facility
FROM [file.xlsx]
(ooxml, embedded labels, table is Sheet1);

LET vNoOfRows = NoOfRows('UserList');


tmpAccessList:
LOAD * Inline [
ACCESS,	USERID,	
ADMIN, DOMAINNAME\DA14
ADMIN, INTERNAL\SA_SCHEDULER
];

//users without restrictions have ADMIN role, all others have USER role with reduction by facility

Concatenate(tmpAccessList)
LOAD 'ADMIN' as ACCESS,
User as USERID
Resident UserList
where match([Access Limits],'All')
;


//expand regions
FOR i=0 to $(vNoOfRows)-1
  SET vUserID=Peek('User',$(i),'UserList');
  SET vRegion=Peek('Region',$(i),'UserList');

Concatenate(tmpAccessList)

LOAD 'USER' as ACCESS,
$(vUserID) as USERID,
FACILITYID as FACILITYID
Resident Region
where Match(RegionCD,$(vRegion))
;
NEXT i;


//expand sub regions
FOR i=0 to $(vNoOfRows)-1
  SET vUserID=Peek('User',$(i),'UserList');
  SET vSubRegion=Peek('SubRegion',$(i),'UserList');

Concatenate(tmpAccessList)

LOAD 'USER' as ACCESS,
$(vUserID) as USERID,
FACILITYID as FACILITYID
Resident Region
where Match(SubRegionCD,$(vSubRegion))
;
NEXT i;

//load facilities
FOR i=0 to $(vNoOfRows)-1
  SET vUserID=Peek('User',$(i),'UserList');
  SET vFacility=Peek('Facility',$(i),'UserList');

Concatenate(tmpAccessList)

LOAD 'USER' as ACCESS,
$(vUserID) as USERID,
FACILITYID as FACILITYID
Resident Region
where Match(FACILITYID,$(vFacility))
;
NEXT i;

drop table UserList;

Section Application;

Hard coded, this excerpt works:

Section Access;
LOAD * Inline [
ACCESS, USERID, FACILITYID
ADMIN, OURDOMAIN\USER11, 
ADMIN, OURDOMAIN\USER34, 
ADMIN, OURDOMAIN\USER45, 
ADMIN, OURDOMAIN\DA14, 
ADMIN, INTERNAL\SA_SCHEDULER, 
USER, OURDOMAIN\USER19, AGM
USER, OURDOMAIN\USER19, AGMFSED
USER, OURDOMAIN\USER19, BLO
USER, OURDOMAIN\USER19, BLOH
USER, OURDOMAIN\USER19, ZIA
USER, OURDOMAIN\USER19, ZIAH
USER, OURDOMAIN\USER19, XEH
USER, OURDOMAIN\USER19, ROS
USER, OURDOMAIN\USER19, BGM
USER, OURDOMAIN\USER19, BGMH
USER, OURDOMAIN\USER19, QOH
USER, OURDOMAIN\USER19, QRM

[marcus_sommer]

A way to bypass the trouble with missing reduction-values is to list them to dummy-entries and afterwards the * wildcard will consider them. This may look like:

and the reduction-values for the dummy could be also derived dynamically from the appropriate fact- or dimension-tables.

[daveatkins · Accepted solution]

so here is the key learning...I got this to work. Instead of doing ANY manipulation/cleverness in the Section Access; part of the load script, I build up the access table first, then just apply it:

Section Access;
LOAD * resident tmpAccessList order by ACCESS desc;
Section Application;

this also leaves the access table accessible for review to ensure the spreadsheet is being parsed correctly.