09-11-2017 10:09 AM
My question has two part.
I'm a little confused by OLAP and OLE DB provider. in menu File->Open -> OLAP Cube,Information map, OLE DB,...
What is the difference between them to connect to OLAP server?
I'm using Base SAS and have OLE DB provider in list of products when I install SAS on my PC and trying to connect to OLAP server via SAS code. I followed the instruction in http://support.sas.com/kb/41/722.html and can connect to OLAP server and see the cubes, but when I try to connect to OLAP server via code in SAS EG I'm getting error.
/* The machine name or IP address for the SAS OLAP server */ %let host=abcd.com; /* The port number for the SAS OLAP server */ %let port=5451; /* A User ID with access to the cube */ %let user=user1; /* The User's password */ %let pass=******; /* The OLAP cube name */ %let cube=Checkin; proc sql; connect to olap (host="&host" port=&port user="&user" pass="&pass"); select * from connection to olap ( select [Measures].defaultmember on columns from [&cube] ); disconnect from olap; quit;
54 /* The machine name or IP address for the SAS OLAP server */ 55 %let host=abcd.com; 56 57 /* The port number for the SAS OLAP server */ 58 %let port=5451; 59 60 /* A User ID with access to the cube */ 61 %let user=user1; 62 63 /* The User's password */ 64 %let pass=******; 65 66 /* The OLAP cube name 67 %let cube=Checkin;*/ 68 69 proc sql; 70 connect to olap (host="&host" port=&port 71 user="&user" pass="&pass"); ERROR: The Bridge Protocol Engine Socket Access Method was unable to connect to abcd.com, port 5451. ERROR: The TCP/IP tcpSockConnect() support routine failed with error 110 (The connection has timed out.). NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 72 select * from connection to olap 73 ( 74 select [Measures].defaultmember on columns from [&cube] 75 ); ERROR: Connection to the olap DBMS does not exist. ERROR: IOM call failed. 76 disconnect from olap; NOTE: Statement not executed due to NOEXEC option. 77 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 1:03.03 cpu time 0.04 seconds 83 %_eg_hidenotesandsource; 95 96 97 %_eg_hidenotesandsource;
What could be the issue?
09-11-2017 06:45 PM
Firstly you say you're using Base SAS but you've posted in the Enterprise Guide forum - if you're using EG you don't connect to the server that way.
If you're using SAS Display Manager it looks like either the OLAP Server isn't running or you're trying to connect via the wrong Port.
09-12-2017 06:41 AM
I'm running the code in SAS EG and not Base SAS and I've been told that I should have Base license to be able to connect to OLAP via OLE DB provider.
I can connect to OLAP server by creating connection and see cubes but I'm getting error when I run the SAS code?
According to SAS documents the port number should be 5451 http://support.sas.com/kb/18/411.html. Is there any other port that could be used for this connection?
09-15-2017 07:06 AM
Yes. I can open them and view the tables.
I tried to run the code in Base SAS and getting this error:
ERROR: The Bridge Protocol Engine Socket Access Method was unable to connect to host abcd.com, port 5451. ERROR: The TCP/IP tcpSockConnect() support routine failed with error 10060 (The connection has timed out.).
09-15-2017 09:42 AM
The only thing then is to check that the Port ACTUALLY is 5451 in Management Console. It doesn't have to be and I've worked at sites where the defaults haven't been used for perfectly valid reasons.
If that Port is correct then I'm sorry but I'm stumped....
09-18-2017 08:19 AM
If I understand there is no way to connect to OLAP server by SAS code in SAS EG and it could be done only in Base SAS with OLE DB provider.
@ChrisBrooks , you wrote: "...if you're using EG you don't connect to the server that way."
What do you mean by that? How can I connect to OLAP server via SAS EG?
09-18-2017 08:51 AM
There's a little bit of confusion in your posts that needs to be cleared up. You mention using EG, and using Base SAS.
For any EG user, it's important to understand that almost all of the underlying work is done by exactly the same software as when you use "base SAS". The only difference is the interface; what you call "base SAS" is termed Display Manager, and is the oldest interface to SAS. The presentation part always runs on the same machine that the SAS system is running on to execute the code.
EG is a newer interface, but almost always it generates SAS code, which is executed by the SAS system (in most cases, you can ask EG to show you the underlying code that is run). But there's a very big difference. The EG presentation part always runs on your local PC, but EG is designed so that the underlying code might be run on your local PC, or it might be run on a different SAS server.
When you write SAS code in a code node in EG, that code is ALWAYS executed by the SAS system.
So, given this, we can do some troubleshooting.
Since you can explore the cube using EG, you do have a SAS OLAP server up and running somewhere. (Just to make this confusing, the OLAP explorer in EG is one of the pieces that DOESN'T use the SAS system to retrieve the data.)
Using EG, can you successfully execute any old simple piece of SAS code in a code node? If yes, that means that you have a good connection to a SAS server, whether on your machine or on a remote SAS server.
If these two pieces are successful, it just remains to troubleshoot the connection problems that you're having.
Based on this, I hope you can see that exactly the same code should connect to your OLAP server either from Base SAS (Display Manager) or from a code node in EG.
You should start by talking to your SAS administrator (the person who set up the SAS OLAP server), and find out if anybody else has succeeded in doing this. If not, requesting help from SAS Tech Support would be perfectly reasonable.