topic Re: SAS Share and Power BI in Microsoft Integration with SAS

SAS Share and Power BI

wbaldwin — Wed, 17 Apr 2019 14:20:59 GMT

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!

Re: SAS Share and Power BI

ChrisHemedinger — Fri, 19 Apr 2019 13:35:01 GMT

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/

Re: SAS Share and Power BI

LinusH — Fri, 19 Apr 2019 16:37:37 GMT

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.

Re: SAS Share and Power BI

SASKiwi — Fri, 19 Apr 2019 23:00:16 GMT

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.

Re: SAS Share and Power BI

wbaldwin — Mon, 29 Apr 2019 13:19:53 GMT

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

Re: SAS Share and Power BI

akhozan — Tue, 30 Jun 2020 18:18:54 GMT

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.

Re: SAS Share and Power BI

ChrisHemedinger — Tue, 30 Jun 2020 18:37:29 GMT

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

Re: SAS Share and Power BI

akhozan — Tue, 30 Jun 2020 18:48:25 GMT

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

Re: SAS Share and Power BI

SASKiwi — Tue, 30 Jun 2020 21:12:09 GMT

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

Re: SAS Share and Power BI

akhozan — Tue, 30 Jun 2020 21:29:21 GMT

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