topic Re: SAS OLE DB connection in Power BI in Microsoft Integration with SAS

SAS OLE DB connection in Power BI

amar17 — Mon, 18 Dec 2017 11:50:50 GMT

Hi folks,

I need to connect Power BI to SAS using an OLE DB connection (can't use ODBC nor the native connection). Here is the string from the build:

provider=sas.IOMProvider.9.45;data source="iom-name://SASApp - Logical Workspace Server";mode="ReadWrite|Share Deny None";sas cell cache size=10000;sas port=0;sas protocol=0;sas server type=1;sas metadata user id=dxru984;sas metadata password=XXXXX;sas metadata location=iom-bridge://lhwappb1.xxx-xx.xxx:8561

I also tried with this one:

Provider=sas.IOMProvider.9.45;Data Source=iom-name://SASApp - Logical Workspace Server;SAS Cell Cache Size=10000;SAS Port=0;SAS Protocol=0;SAS Server Type=1;SAS Metadata User ID=dxru984;SAS Metadata Password=xxxxxxx;SAS Metadata Location=iom-bridge://lhwappb1.xxx-xx.xxx:8561

The first string works perfectly with Excel but not in PowerBI with that error message:

OLE DB : Format of the initialization string does not conform to the OLE DB specification

Any idea would be appreciated.

thanks

Re: SAS OLE DB connection in Power BI

JuanS_OCS — Mon, 18 Dec 2017 12:24:09 GMT

Hello @amar17,

I think this question would be best addressed to the Microsoft's Technical Support. Have you already drop this question at them?

Re: SAS OLE DB connection in Power BI

AlanC — Tue, 19 Dec 2017 21:44:35 GMT

Wild guess but try switching your double quotes to single.

Why can't you use ODBC?

Re: SAS OLE DB connection in Power BI

HugoFonseca — Tue, 10 Jul 2018 10:00:36 GMT

My connection work, by removing some default parameters: provider=sas.IOMProvider.9.45;data source=[APP_HOST];sas port=8591;sas protocol=2;sas metadata user id=[USERID];sas metadata password=[PASSWORD];sas metadata location=[META_HOST]:8561

I saw the solution in https://www.connectionstrings.com/sas-iom-provider/

Re: SAS OLE DB connection in Power BI

SASKiwi — Tue, 27 Nov 2018 03:18:03 GMT

@HugoFonseca - I'm giving this a go myself with Power BI Desktop 2.56. What SAS version did you get this going with? I'm trying it with SAS 9.4M2.

My connection string is:

provider=sas.IOMProvider;data source=MySASAppServer;sas port=8592;sas protocol=2;sas metadata location=MyMetadataServer:8561;

Then I'm relying on the Power BI user name and password prompt to supply the logon credentials.

I can see in the SAS metadata server log that connections are being successfully opened, but then straightaway they are closing. Then Power BI repeats trying to connect. In the Power BI connection window the "busy wheel" displays and never stops. No errors are shown. The Cancel button stops the continuous attempts.

Any help would be much appreciated! I'll open a Tech Support track as well.

Re: SAS OLE DB connection in Power BI

HugoFonseca — Tue, 27 Nov 2018 09:32:05 GMT

I'm using SAS 9.4M3, my Metaserver and AppServer are in Windows and the libnames in the AppServer use a FedServer driver (my FedServer is in Linux Suse).

Share my list of products in AppServer:

Base SAS Software 29JUN2019

SAS/STAT

SAS/GRAPH

SAS Integration Technologies

SAS/Secure 168-bit

SAS/Secure Windows

SAS Enterprise Guide

SAS/ACCESS Interface to PC Files

SAS/ACCESS Interface to ODBC

---SAS Workspace Server for Local Access

---High Performance Suite

---SAS Add-in for Microsoft Excel

---SAS Add-in for Microsoft Outlook

---SAS Add-in for Microsoft PowerPoint

---SAS Add-in for Microsoft Word

Re: SAS OLE DB connection in Power BI

dtravassos — Sat, 08 Jun 2019 23:00:44 GMT

Hi,

Do you guys know if it's possible for SAS EG to connect to a power bi server?

Thanks

Danielle

Re: SAS OLE DB connection in Power BI

Tom — Sat, 08 Jun 2019 23:42:46 GMT

SAS Enterprise Guide is a GUI tool that runs on Windows and helps you generate and run SAS code on a SAS server (possibly your same WIndows machine). I don't think there is any reason for EG itself to connect to Power BI. But since you can use SAS EG to run actual SAS code then just use the methods already suggested to allow your SAS session to connect.

Re: SAS OLE DB connection in Power BI

KoketsoM — Wed, 11 Sep 2019 07:44:19 GMT

A little late to the party but I used the following string to connect:

provider=sas.IOMProvider.9.45;data source=demo;sas port=8591;sas protocol=2;sas metadata user id=domain\userid;sas metadata password=PassWord;sas metadata location=demo:8561;

provider=sas.IOMProvider.9.45;

data source=server;

sas port=8591;

sas protocol=2;

sas metadata user id=domain\userid;

sas metadata password=PassWord;

sas metadata location=server:8561;

Hope it helps.