Connection string to use PowerBI with SAS

[davidmitrovic]

Hi All,

I'm trying to use Power BI to connect with SAS data sets with SAS Providers to OLEDB. The main issue is that when we connect we see all the libraries and tables from the SAS Workspace Server. Anybody knows if it's possible to specify a single library in the connection string?

We are using this string:

provider=sas.IOMProvider.9.46;data source=SASApp;mode=Read;sas cell cache size=10000;sas machine dns name= hostname;sas port=8591;sas protocol=2;sas server type=1

Thanks!

[SASKiwi]

What is the issue with that? If the SAS data libraries are defined in SAS metadata then they are designed to be globally available to any SAS user whose metadata permissions allow it.

[davidmitrovic]

Hi,

The problem is not with the libraries, is with the information that returns the OLE DB connection from PowerBI. The connection returns for example, the following libraries (and all the tables that the libraries contains):

Lib1

Lib2

Lib3

But with the PowerBi we only wants to see the library "Lib3" with the tables of that library only.

Thanks!

[SASKiwi]

The only solution I can think of would be to set up a new instance of the SAS workspace server, called say SASApp2 and configure that instance to only assign the LIB3 library. You can do that in SMC in the data connection properties for LIB3 where you specify the SAS servers (SASApp2) it is assigned on.

Then you call SASApp2 in your OLEDB connection string.

[davidmitrovic]

It is a solution but in this server we have already defined 8 different SASApp. Our idea is to define the connection string so that we can specify a single library to connect to. 😞