Kicking users out of Cubes while using SAS Web Report Studio

[barbe1lj]

Good Morning,
I was wondering if someone could help me with an issue we are having.

We have a job that runs every morning and there are days it fails because a user is in a SAS Web report studio report that uses a cube. Therefore the job fails cause the users is in the cube.

I am wanting to kick all users out of reports and cubes before this jobs runs but I cannot get successful with this.

We have gotten successful at kicking the user out if they are in SAS OLAP Cube Studio by using the below code. But Not in SAS Web report studio using the information map that uses the cube.

%MACRO CLOSE_SESSIONS(logfile=out.log, host=localhost, port=5451, userid=sasadm@saspw, password=sasadm1, cube=_ALL_);
  FILENAME outlog "&logfile";
  PROC PRINTTO LOG="&logfile" NEW;
  RUN;

PROC OLAPOPERATE;
    CONNECT HOST="&host" PORT=&port USERID="&userid" PASSWORD="&password"; 
    %if &CUBE = _ALL_ %then %do;
                LIST SESSIONS;
    %end;
    %else %do;
        LIST SESSIONS CUBE="&cube";   
        %end;
  RUN;

PROC PRINTTO LOG=log;
  RUN;

DATA SESSIONS (KEEP=ID);
    INFILE outlog TRUNCOVER;
    LENGTH text $256 ID $71;
    RETAIN count 0;
        CALL SYMPUT('n',count);
    INPUT text $ &;
    IF SUBSTR(text,1,10) = "Session ID" THEN DO;
       id = SCAN(text,3,' ');
           count + 1;
           CALL SYMPUT('n',count);
       OUTPUT;
    END;
  RUN;

%DO i = 1 %TO &n;
   DATA _NULL_;
      SET SESSIONS (FIRSTOBS=&i OBS=&i);
          CALL SYMPUT('id',id);
   RUN;
  
   PROC OLAPOPERATE;
     CONNECT HOST="&host" PORT=&port USERID="&userid" PASSWORD="&password";
     CLOSE SESSION "&id";
   RUN;
  %END;

DATA _NULL_;
    sessioncount = strip("&n");
    PUT "NOTE: " sessioncount " SAS OLAP Server sessions were closed.";
  RUN;
%MEND;

[gwootton]

I would expect your code to work for any session to the OLAP server, after you run it do you still see sessions in your list sessions output or in SAS Management Console's OLAP Server Monitor?

I would also mention you can use out= for "list sessions" to output to a dataset, which might be easier than parsing a file. For example:
proc olapoperate ...;
list sessions out=work.sessions;
...
quit;

Also, close sessions can have the _all_ or cube= options specified.

[Kurt_Bremser]

You close sessions which are present when you look for them, but that does not prevent new sessions from being started in the time between the lookup and the moment when cubes are updated.

The only safe way is to stop the OLAP server for the duration of the updates.

[Patrick]

If this is a Unix based environment then you could create the new cube somewhere else and then use a mv -f command to replace the old cube.

@Kurt_Bremser I remember you've explained such an approach already in other places in detail and with code. Can you think of any reason why this wouldn't work for a cube?

[Kurt_Bremser]

I've never tried the "remove first, then create" approach (which I presented at SASGF21) for cubes. We always created our cubes at non-office hours.

Someone else will have to try that (I no longer have access to a OLAP server environment); it may be that metadata will also play a role here, as the creation of a cube also writes to metadata.