topic Re: Connection string to use PowerBI with SAS in Administration and Deployment

Connection string to use PowerBI with SAS

davidmitrovic — Tue, 15 Feb 2022 16:45:29 GMT

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!

Re: Connection string to use PowerBI with SAS

SASKiwi — Tue, 15 Feb 2022 20:28:23 GMT

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.

Re: Connection string to use PowerBI with SAS

davidmitrovic — Tue, 15 Feb 2022 23:09:07 GMT

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!

Re: Connection string to use PowerBI with SAS

SASKiwi — Tue, 15 Feb 2022 23:31:17 GMT

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.

Re: Connection string to use PowerBI with SAS

davidmitrovic — Wed, 16 Feb 2022 08:28:54 GMT

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. 😞