Within an AW is possible to scope down the list of available dimension members using the PERMIT_READ command (another post on this subject later) and this process is quite well documented. However, removing measures has (at least for me) been a more difficult problem to resolve. When I developed the latest BI10g common schema sample analytic workspace I expanded the number of measures available to over a hundred measures. Compared to the original 9i schema the latest version has a lot of new features. However, the Query Builder measure panel now looks like this:
As the measures are split by functional area (customer analytics, sales analytics and brand analytics) it makes sense to have three different users and scope down the list of available measures accordingly.
To create the environment to manage the scoping process I added a new user called SH_SCOPE to my instance and created a table within this schema call SH_AW_SECURITY:
CREATE TABLE "SH_SCOPE"."SH_AW_SECURITY"
("USERID" VARCHAR2(50 BYTE)
,"MEASURE_NAME" VARCHAR2(250 BYTE));
The table contains the list of measures a user is able to access. To populate the table for the user SH_OLAP to only view he sales analytics cube I inserted the following rows into the SH_AW_SECURITY table viaSQL Plus.
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.REVENUE.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.COSTS.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.QUANTITY.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_MA_12M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_MA_6M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_PC_PP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_PC_PY.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_MA_12M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_MA_6M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_PC_PP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_PC_YA.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PROFIT.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.MARGIN.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_MA_12M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_MA_6M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_PC_PP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_PC_PY.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSPC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSPP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GST.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSTC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSTP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSPC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSPP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PST.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSTC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSTP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SCTREND.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SPTREND.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SRTREND.MEASURE');
Each user will next SELECT priviledges on this table as an OLAP DML program will be used to query the contents of the table as will be shown later.
The next step is to add a procedure to the D4OSYS schema that will be called each time a tool connects to a schema via OLAPI connection. The procedure is called D4O_AUTOGO and can be relativelty simple:
create or replace PROCEDURE D4O_AUTOGO (user_id IN VARCHAR) authid current_user
is
BEGIN dbms_aw.aw_attach(‘SH_OLAP.SH_AW’, false, false, 'FIRST');
END;
In this case as I know the only AW this user will access is called SH_AW, I can hard code the reference in to the procedure. Alternatively, the procedure could be made more generic by using a cursor to return a list of all available AWs and then attach each one in turn, with each AW being scoped according to its own PERMIT_READ command (more on this later):
The AW_ATTACH procedure attaches an existing analytic workspace to your SQL session so that you can access its contents. The analytic workspace remains attached until you explicitly detach it, or you end your session.
DBMS_AW.AW_ATTACH (
awname IN VARCHAR2,
forwrite IN BOOLEAN DEFAULT FALSE,
createaw IN BOOLEAN DEFAULT FALSE,
attargs IN VARCHAR2 DEFAULT NULL,
tablespace IN VARCHAR2 DEFAULT NULL);
For more information on this procedure please refer to OLAP 10g Reference manual.
Please note I am not a PL/SQL expert so I am sure this code could be optimised further. It is important to test this procedure to make sure it works as expected before moving on to the next series of steps. This procedure will get called every time a tool makes a connection to a schema via the OLAPI. This forces each AW to be attached in read-only mode.
The next step is to ensure each user has execute rights to D4O_AUTOGO, so from the D4OSYS account simply GRANT EXECUTE rights to SH_OLAP.
The last step is to create an OLAP DML program called PERMIT_READ that will scope down the list of available measures when the AW is attached in read-only mode. This is an important point – the program should not execute when the AW is attached in read-write mode as this will affect the metadata used by Analytic Worksapce Manager and Warehouse Builder. If the code is executed with the AW is attached in read-write mode it is likely that both AWM and OWB will fail to work correctly with the AW.
Using Analytic Workspace Manager you need to attach to the required workspace in read-write mode. Then open the OLAP Worksheet and define a new program called PERMIT_READ:
DEFINE PERMIT_READ PROGRAM BOOLEAN
Permit read needs to return a boolean answer of ‘Yes’ to allow the attach process of the AW to complete successfully. Personally, I prefer to call a program from PERMIT_READ rather than coding directly within this program. Therefore, create another program called SET_MEASURES to manage the code to hide the measures. The code can either directly list the measures to be excluded within a SWITCH/CASE or IF THEN/ELSE block or alternatively, in this case I stored the list of available measures in the relational table created earlier. The program can query this table directly as follows:
DEFINE SET_MEASURES PROGRAM BOOLEAN
argument T_USER text
variable T_LIST text
variable T_MEASURE text
variable B_RETURN boolean
trap on error noprint
B_RETURN = NO
" Check to make sure AW is attached in read-only mode
if aw(RO 'SH_OLAP.SH_AW')
then do
" Make sure AW is the first attached in the list
AW ATTACH SH_OLAP.SH_AW RO FIRST
" Find the current user
SQL DECLARE USER_cur CURSOR FOR SELECT USER FROM DUAL
SQL OPEN USER_CUR
sql fetch user_cur into :T_USER
" Find all the measures for the current user
SQL DECLARE measure_cur CURSOR FOR SELECT measure_name FROM sh_scope.sh_aw_security WHERE userid=:T_USER
if sqlcode ne 0
then signal dclerror 'SQLERRM'
SQL OPEN measure_cur
if sqlcode ne 0
then signal dclerror 'SQLERRM'
while SQLCODE eq 0
do
sql fetch measure_cur into :T_MEASURE
T_LIST = joinlines(T_LIST, T_MEASURE)
doend
SQL CLOSE measure_cur
SQL CLEANUP
" Limit to the measures the user is allowed to see
limit ALL_MEASURES to charlist(T_LIST)
" Swap the limit to set the measures to all those the user is not allowed to see
limit ALL_MEASURES complement
for ALL_MEASURES
do
CNS &remchars(CHANGECHARS(ALL_MEASURES, '.', '_'), findchars(ALL_MEASURES, 'MEASURE')-1, NUMCHARS(ALL_MEASURES))
" Remove the properties for specified measures to remove them the Query Builder
" measure panel
PROPERTY DELETE 'AW$MEASUREDEF'
PROPERTY 'AW$ROLE' NA
doend
doend
B_RETURN = YES
ALLDONE:
return B_RETURN
ERROR:
B_RETURN = NO
goto ALLDONE
END
Make sure the program compiles correctly before proceeding. Within the PERMIT_READ simply call SET_MEASURES to execute the code. You can test the procedure by simply attaching your AW in read-only mode, the program should execute automatically and without errors.
Now when the user SH_OLAP logs into Discoverer Plus the Query Builder panel is scoped down to just the measures for sales analysis.
and the same is true for Excel:
