Hello Linus, Thanks for you input to clarify the storage. I looked through a lot of SAS documentation but could not find explained what you just said. I did test the scenario and it did not work Now, since you say it work. I might need to look over it again. ... View more

Hello Linus Hello Patrick, Thanks for your input. The screen print is now attached. Patrick, I dont know why this feature only exists in SAS OLAP CUBE Studio (seems to be one of those great messed up UI designs and duplicated functionality) but regardless if I want to build a cube or not what does this functionality actually do, phyiscally ? Lets not talk about how to create cubes. "I would assume that if your source data lives in Oracle and you choose "create as view" then your source is defined as SAS SQL view." Interesting! So what is the difference if I tick this box, physically? None? I dont want to get into aggregation either. Lets focus on SAS DataSets (Tables) and what this feature "CREATE VIEW" does to them. My data is pre-aggregated and needs therefore tons (100GB) of Oracle space. "But metadata will affect what code will be generated and this of course will affect if your data gets replicated or not." Thats could be the right path, but I would like to have that in some kind of document not just say oh well, if I tick "Create view" less data well be replicated by some code generated. I am having huge amounts of data here. I need to be sure. ... View more

"You're obviously talking about SAS Metadata which is only a Metadata description of an object. If you choses "create as view" or "as table" makes on the metadata level not a big difference." Patrick, thanks for your input. I dont mind what it is called in the Metadata I care about PHYSICAL storage. As I said in my answer to Tom's post. I dont want to replyicate 100GB of Oracle data in SAS. Its all about the storage space and performance. "create as view" or "as table" can be selected in SAS OLAP CUBE STUDIO 4.2 (somehow this does not seem to exist in the SAS Management Console) in the table properties tab called...."PHYSICAL STORAGE" I am not using SAS DI Studio. ... View more

Hello Tom, Thanks for your help. You wrote "No data will be moved or accessed until you actually use one of the tables in SAS code." So what happens when I "access" the data. I guess it gets cached? What happens if I have a 100 GB Oracle Table? I know the difference between a SAS DataSet and a SAS View (a view on a SAS DataSet). You also say "No data will be moved or accessed until you actually use one of the tables in SAS code." what happens when I actually want to have the data stored? I was hoping the functionality offered by the SAS OLAP CUBE Studio 4.2 to change the phyiscal storage from table to view by clicking "Create as View" would exactly mean that, I either pull the data from Oracle and store it or it is indeed just a reference. Thanks a lot for the feedback. ... View more

Hello Patrick, thanks for the reply. Yes, the connection to the database works fine. The Oracle connection is not a problem. The setup is completed. "Once this is done you can use library definitions in EG" I know that, and that works fine too. But I wont to have a SQL PASS THROUGH code independent of libraries registered already. --Shiva, thanks again but we are well aware how to set up Oracle connections and SAS ACCESS libraries. That all works fine. But I dont want to use a already registered library in my case. --Patrick "And with newer SAS EG versions you can even convert the code to explicit pass-through (it will read metadata information and use it to generate the connection string)." That sounds interesting. I hope it does not involve SAS ACCESS because I dont want to go via libraries. --Paul, thanks for that. That was what I was looking for. You can also find the logins when you use EG  > Tools -> SAS EG Explorer - > File - > Manage Logins ... View more

Hi Patrick, Sorry, but I dont think you can. There is hardly any information on metauser/password information passed to the Oracle server via PASS THROUGH. PASS THROUGH is only talked about in respect to Composite Information Server. Note, if I substitute the METAUSER and METERPASS with with the static values, the connection works. I think its a problem with METAUSER (which only is a single time mentionded in the Admin Guide regarding PROC METALIB) Thanks, metalray ... View more

Hi DF, works great. thanks a lot. ... View more

Hi DF, the connecting user is always the same as specified in proc sql ; connect to oracle as oradb (user='....' password='.....' path='.....'); How can it be that Oracle knows the SAS executing user? - because the executing user (sysuserid) is the one that will have to go in the WHERE clause to only get the records for that specific user) ... View more

@Tom, good idea but with your SQL you are merging all the tables or even creating some kind of cross/full join, something I cant do with a million rows. @DF, the idea with the OS User seems to work fine when opening the View in EG but when using the view in an information map the data returned is always nothing/empty. Please explain me, why does OS_User work if the sas user does not exist on the oracle database as a user? ... View more

Hello Guys, Many thanks for your suggestions. I think I never got so many responses here. First, yes, the Oracle User ID is very different from the user ID of the view user. There is and will never be a match (@DF). We have a thousand users for SAS but not a thousand Oracle USERIDs. @Tom, you pointed it out rightly, I cant use a SAS macro variable in the code that is running on the database, hence my describe view statement always showed the sasuserid of the creator of the view. I was hopeing this can be substituted at runtime but obviously not. Now, we try to do a SAS DATA VIEW to allow putting the sysuserid of the individual executer in the view and create a truly user-dependent-dynamic-view of data. The data is quite large and I wonder how to efficiently link it using a SAS DATA VIEW. I cant merge the tables, it will create millions of rows. Regards, Bob ... View more