Your SAS programs, embedded in web apps and elsewhere

Prompts in STP in WRS: WORK.MYVIEW.DATA does not exists.

Reply
Regular Contributor
Posts: 207

Prompts in STP in WRS: WORK.MYVIEW.DATA does not exists.

Hi Guys,
I have created a view based on some dynamically created view in the WORK library for individual users. It all happens with an STP.

1. proc sql to get user specific data

create view WORK.DIM_SALES_SALESTEAM as select * from connection to olap
(
SELECT NON EMPTY { [MEASURES].[FSUM] } ON COLUMNS,
NON EMPTY { [DIM_SALES].[SALESTAM].AllMembers } ON ROWS
FROM [mycube]
);

2.
/*MAKE VIEWS AVAILABLE FOR PROMPTS (WORK LIBRARY IS NOT A VALID SOURCE) */
create VIEW allshared.WORK_DIM_SALES as select * from WORK.DIM_SALES_SALESTEAM;

This STP also has a prompt that neatly selects from allshared.WORK_DIM_SALES which is now only shows user specific data. The view allshared.WORK_DIM_SALES is also registered in the meta data and its all fine as a source until...

I reach WRS. When I open this WRS I get the
Error: File WORK.MYVIEW.DATA does not exists.

Its seems the STP when executed in WRS does not know anything about the views it just created. Why is that?
If I use CREATE TABLE instead of create view it works fine but that defeats the objects since I dont want a table for each user or those users overwriting each others data.

The error dissapears when I remove the prompt from the STP. Executing a second STP with the prompts but no view creation causes the same error again.

Aim: dynamic cascading prompts that show user specific data retrieved from an OLAP cube member permissions
Regular Contributor
Posts: 207

Re: Prompts in STP in WRS: WORK.MYVIEW.DATA does not exists.

I just noticed that the STP, executed as STP, does not create the views in the WORK library in the first place. It does, however, do it when I execute the code as a program in EG4.2. I wonder why that is...
In WRS the error reads "unable to find physical table"

A short STP that I and you can use for testing works fine i.e. it shows the output put here as well, the WORK library on the sas application server\libraries is empty. Is there a second work library on somewhere? If so, how do I use it to reference to in sas app server views?

--Executed on the workspace server
--shows html results in EG4.2 albeit error
--ERROR: No logical assign for filename _WEBOUT.
--WARNING: No body file. HTML output will not be created.

%STPBEGIN
*ProcessBody;

proc sql;
create view work.viewthatdoesnotappear as select * from sashelp.class;
quit;

/**/
*ods html file="\myfile.html";
proc print data=WORK.viewthatdoesnotappear;
var Sex;
run;


%STPEND

A view, registered in the metadata that now selects from
work.viewthatdoesnotappear will fail.
Regular Contributor
Posts: 207

Re: Prompts in STP in WRS: WORK.MYVIEW.DATA does not exists.

I included the STP in an Information Map. Defined Information Map specific Prompts
that have the master views as a source and that was a progress.
It seems the work library the STP (Workspace Server) creates the views in and the work library the prompts use are the same now (maybe because they are together wrapped in an Informatio Map)?.
Now, since I have it all in an Information Map the STP needs to get the executors login details to access the OLAP cube. A dynamic user id and a dynamic password.
The Information Map will ultimately be executed in Web Report Studio so which user id and password combination is the right on _Metauser (whatever the password variable will be), &sysuserid (whatever the password variable will be) or some InformationMap user, or even the portal/web report user?
Regular Contributor
Posts: 207

Re: Prompts in STP in WRS: WORK.MYVIEW.DATA does not exists.

OK. I figured out the following can remain empty. It just works like that.

http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003076738.htm
Ask a Question
Discussion stats
  • 3 replies
  • 378 views
  • 0 likes
  • 1 in conversation