SAS Share and Power BI

[wbaldwin]

Running SAS GRID 9.4 M4... we have SAS Share (have not used it)  and we have many SAS libs/datasets... Users want to access specific datasets via Microsoft Power BI.  We don't want to move/copy the data to Power BI but was hoping we could use SAS Share and make sas datasets available in Power BI.   We will put the needed datasets into a SAS Share library.  Now my question, I assume the Power BI admin would need to load a SAS ODBC to access/connect to this SAS Share Lib.  Does this seem correct/doable?  Or is there another/better approach to make SAS datasets available in Power BI?

Thank you!

[ChrisHemedinger]

That sounds correct.  To use the SAS ODBC driver, you need a SAS/SHARE server or a special running SAS ODBC server.

Or, as this is a Microsoft tool, an OLE DB connection might work.  SAS has a the Local OLE DB Provider (for sas7bdat files on disk) and the IOM OLE DB Provider (for SAS server connections -- same as used by EG).  You might be able to piggy-back on existing infrastructure for that.  

I have a few blog posts that might help:

https://blogs.sas.com/content/sasdummy/tag/ole-db/

https://blogs.sas.com/content/sasdummy/tag/sas-integration-technologies/

[wbaldwin]

Chrs.. your blog is Very good/useful information that I think will help me solve my issues.. Thank You!

[LinusH]

How much data do you have? If remember correctly SAS/SHARE is a single threaded (per user) application, and might not cope with queries on large data sets.

[SASKiwi · Accepted solution]

We use MS PowerBI as well as SAS. Our current approach is to load the required SAS data into an SQL Server database, refreshing it every time it gets updated. Fortunately this happens no more than once a day. SAS/ACCESS to ODBC is used for this. PowerBI then reads the data from SQL Server - a simple report refresh does this. 

[akhozan]

Can you connect directly from Power BI to SAS using SAS/share via JDBC? We currently use this method with Spotfire, but Power BI will be the preferred visualization tool in the future once more of our data moves to the cloud.

[ChrisHemedinger]

@akhozan According to the Power BI doc, you can use the generic interfaces (ODBC or OLE DB) to connect to a variety of providers.  SAS Workspace supports connections via OLE DB.  SAS/SHARE has an ODBC connection method.  Some of this might require configuration in your Power BI desktop environment.  I don't know how/if these methods work in the cloud.

[akhozan]

Thanks @ChrisHemedinger. I will work with my SAS Admin to get an ODBC port enabled for SAS/Share.

[SASKiwi]

@akhozan  - FYI, I believe there will be other ways SAS and Power BI can work together when SAS Viya 4.0 is released later this year. There are some details of this in the SAS Global Forum 2020 presentations regarding the strategic partnership between SAS and Microsoft, which you can link to from the SAS Community.

[akhozan]

Thanks @SASKiwi, we are not yet using SAS Viya, but that is still being planned/talked about.