Kicking off users from cubes

barbe1lj — Wed, 29 Sep 2021 19:08:56 GMT

We have a job that runs every morning at 7:00am. Sometimes we have users in reports that are using the cube that the job runs therefore the job fails.

Any suggestion on how to resolve this issue? Is there a script or rule that can be run to kill all sessions? I know how to do this manually but was wondering if anyone has every created a rule/script to do this?

Thank you

Lori

Re: Kicking off users from cubes

DavidHD — Wed, 29 Sep 2021 19:18:26 GMT

Yes this can be done by utilizing the proc olapoperate - see the documentation: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/olapug/n0rh9rcu3ucqzon1o9cb47zbzytj.htm

Re: Kicking off users from cubes

AhmedAl_Attar — Wed, 29 Sep 2021 20:12:13 GMT

Hi @barbe1lj ,

You cab try the following macro

/**
*************************************************************************
* Lists and Closes All/Cube Specific Active Session on the specified OLAP
* Server.
* <br><br>Usage Example:<br>
*	%olap_closeSessions(p_host=
*	,p_userid=
*	,p_password=
*	,p_port=5451
*	,p_cube=_ALL_);<br>
*
* @param	p_host		SAS OLAP Server Host Name network IP address
* @param	p_userid	Registered SAS Metadata User ID to use when
*						logging into the OLAP Server
* @param	p_password	Registered SAS Metadata Password. Please use
*						Encrypted password.
* @param	p_port		TCP/IP Port used by the SAS OLAP Server Process
*						Default:5451
* @param	p_cube		SAS OLAP Cube Name being used
*
*************************************************************************
*/

%MACRO olap_closeSessions(
	 p_host
	,p_userid=
	,p_password=
	,p_port=5451
	,p_cube=_ALL_
	);

	%local l_closeStmt;

	%let l_closeStmt=;

	/* Assign a Filename and redirect the SAS log to it */
	FILENAME outlog TEMP;

	PROC PRINTTO LOG=outlog NEW; RUN;

	/* Get a list of all Active Sessions on the OLAP Server */
	PROC OLAPOPERATE;
		CONNECT HOST="&p_host" PORT=&p_port USERID="&p_userid"
		PASSWORD="&p_password";

 		%if (%superq(p_cube) = _ALL_) %then
 		%do;
			LIST SESSIONS;
		%end;
		%else
		%do;
			  LIST SESSIONS CUBE="&p_cube";
		%end;
	RUN;

	/* Redirect the log back to the original log file */
	PROC PRINTTO LOG=log; RUN;

	/* Get Active Sessions on the OLAP Server */
	DATA WORK.SESSIONS (KEEP=ID);

		INFILE outlog TRUNCOVER;

		LENGTH text $256 ID $71;
		INPUT text $ &;

	    IF SUBSTR(text,1,10) = "Session ID" THEN
	    DO;
			ID = SCAN(text,3,' ');
			output;
		END;
	RUN;

	PROC SQL NOPRINT;
		SELECT	"CLOSE SESSION '"||STRIP(ID)||"';"
		INTO	:l_closeStmt separated by ' '
		FROM	WORK.SESSIONS ;
	QUIT;

	%put l_closeStmt=%superq(l_closeStmt);
	%put sqlobs=&sqlobs;

	/* Close All Active Sessions on the OLAP Server if there were any */
	%if (&SQLOBS GT 0) %then
	%do;
		PROC OLAPOPERATE;
			CONNECT HOST="&p_host" PORT=&p_port USERID="&p_userid"
			PASSWORD="&p_password";
			&l_closeStmt
		RUN;

		%PUT "NOTE: &SQLOBS SAS OLAP Server sessions were closed.";
	%end;

	PROC DATASETS LIB=work NOLIST; DELETE sessions; RUN; QUIT;
%MEND olap_closeSessions;

You can use the sasadm@saspw user credentials to list and close all active sessions, or a specific session for a specific cube.

Hope this helps,

Ahmed

topic Re: Kicking off users from cubes in Administration and Deployment

Kicking off users from cubes

Re: Kicking off users from cubes

Re: Kicking off users from cubes