Reading SAS Data in Microsoft Power BI (Help Please)

[Clark]

I am trying to configure either an ODBC or OLE DB connection between Power BI and SAS GRID 9.4 M6.

I have struggled to find content online to help. Therefore, I thought I'd share what steps I have done but also I'm a little bit stuck.

OLE DB

I have successfully connected to my environment after installing the OLE DB drivers from November 2018.

The connection string is the following: 

provider=sas.IOMProvider;data source=iom-name://SASApp - Logical Workspace Server;sas port=0;sas protocol=2;sas metadata user id=<userid>;sas metadata password=<password>;sas metadata location=iom://<SAS Metadata Server Address:8561

I guess the good thing is that I can connect and can see the default SASHELP and MAPS libraries.

Problems:

• I cannot see any other tables. How do I expose additional data?
• There are pre-assigned libraries in the metadata but none are exposed to the OLE DB. I can see these libraries in other SAS tools (SAS Studio etc).
• I have tried adding the following at the end of the connection string. None of the tables in that library were visible.

  SAS Workspace Init Script="LIBNAME libref 'path'";

Question: What am I missing to expose this data?

ODBC

I managed to configure the ODBC for SAS drivers on my laptop after installation to connect to the SAS/SHARE server running on the environment.

I had a similar results to the OLE DB where I was only getting SASHELP and MAPS libraries.

My system admin looked to add additional libraries within SAS/SHARE to expose them to PowerBI but now I am getting the following error:

Error Message:
Container exited unexpectedly with code 0xC0000374. PID: 7536.
Used features: Odbc.DataSource/SASDRV32.DLL/09.04.0000/SAS/9.04.01M6P110718.

My system admin can see that the connection is being created as before and there's no error messages in the logs.

I've tried to search for a resolution for this code without finding one.

Question: Does anyone have any suggestions?

[SASKiwi]

I'd suggest opening a Tech Support track on this. I'm guessing your use case is to expose all available SAS data to Power BI so it would be best to get your setup right.

We also use Power BI with SAS data, but since our use case involves just a small number of selected tables we found it a lot easier just to load these tables into SQL Server and get Power BI to read them from there. Some of these tables are refreshed regularly, even daily, so it is easy to run scheduled SAS jobs that reload the SQL Server copies of the SAS tables. 

[LinusH]

To be able to access data through SAS/SHARE, you ned to specifically assign the libraries you need.

One way of doing it is through metadata, see the documentation:

SAS Help Center: Establishing Shared Access to SAS Data Sets

[s0nniez]

Hi,

I am having the same problem as you and found out that most of the libraries that I can't see are created from teradata and the OLEDB can only see the SAS Libraries.