Connecting to SAS Library via Ole DB connection in Power BI

[HerrVeraeus]

Hi!
Novis user but on my way to become a very big fan of SAS Studio 🙂

I am facing an issue I dont really know how to tackle. My organisation is at the moment very Microsoft based, which means that if we can we should provide reports and data in Microsoft products. So for this Im looking for away to connect to my libraries in SAS Studio via a OLE DB Provider in Power BI. 

But I cant find any good walkthroughs on how to exactly do this. 

I dont have my SAS data locally, its on a server. I have a couple of options in Power BI and dont know which to choose and I dont really know how to find the correct paths to my libraries. In short I kinda lack everything except for my desire to make this connection :D.

Is there anyone out there that has done this before or have the knowledge to help me with this? 
Thanks in advance!

// Fredrik

[MrSantos]

Hi,

I found a video explaining how create the connection between PowerBI and SAS but its in Portuguese (https://www.youtube.com/watch?v=lFVSSOYQ2X0). I'll try to translate and summarize:

- on the 'From OLE DB' window type: source=iom-bridge://<SAS Metadata Server name>:8591

The port may change depending on your config.

- create

- on Provider, select SAS IOM Data Provider

- click next

- on Data Source type: source=iom-bridge://<SAS Metadata Server name>:8591

- add the user name and password, try with an internal account like sasdemo

- test connection

- on the last tab, scroll and find 'SAS Protocol' and change it to 2

- click ok

- on the 'From OLE DB' window type, find and remove: mode="Read Write Share Deny None" 

- click  Advanced Options and write the sql query to select your data

- click ok

Hope this help and works for you.

Regards,

[HerrVeraeus]

Best video I have seen so far. But I forgot to mention that I am in SAS Viya, not 9.4. Is there a video or similar on SAS VIYA to Power BI?

[MrSantos]

I haven't found anything related to SAS Viya unfortunately. Also, I don't think that SAS Viya has any equivalent  options for OLE DB and ODBC connections.

You can investigate the use of APIs or use MS SQL Server as intermediary. Or, you can contact SAS Customer Support Site | SAS Support to check if they have other alternatives for you.

Please see below some extra information:

Solved: SAS Share and Power BI - SAS Support Communities

https://developer.sas.com/home.html

https://developer.sas.com/guides/rest.html

Regards,

[HerrVeraeus]

Right! So I might have been looking at this all wrong maybe. Thank you so much for your help!

Very appreciated!

[SASKiwi]

We provide a lot of SAS datasets to Power BI by loading them into SQL Server tables using SAS/ACCESS database connectors and that works very efficiently. While we use SAS 9.4, I'm sure this approach would work equally as well in Viya.  

[HerrVeraeus]

Ok! Sounds like something to investigate further. You dont happen to know any videos or guides?

[LinusH]

First contact the PBI team and agree upon a SQL Server database where you can export your data to, existing or potentailly anew one.

Secondly, set up a connection to the database.

Then, you can write to that destination as if it would be a SAS dataset.

https://documentation.sas.com/doc/en/itopscdc/v_069/itopssr/p03lkog7sche30n16nz48rhqe2ka.htm#n1enb4c...

"To configure and deploy your SAS/ACCESS products, see the “Configuring SAS/ACCESS and Data Connectors for SAS Viya 4” README file at $deploy/sas-bases/examples/data-access/README.md (for Markdown) or $deploy/sas-ases/docs/configuring_sasaccess_and_data_connectors_for_sas_viya_4.htm (for HTML)."

https://documentation.sas.com/doc/en/pgmsascdc/v_068/acreldb/p0378ek9nfsu5bn1cpsc8woi6qjf.htm