topic Re: Reading a spde file into power BI in SAS Enterprise Guide

Reading a spde file into power BI

alepage — Thu, 16 Jun 2022 14:13:18 GMT

Hello,

I have install the R software on my computer in order to be able to read / import a sas dataset in Power BI, for validation purpose. I have read a little bit the documentation on that, but it refers to sas7bdat.

How can we read a spde file in Power BI without reconverting it to the base format ?

Re: Reading a spde file into power BI

AlanC — Thu, 16 Jun 2022 14:24:09 GMT

I am out on a limb here since it has been years since I worked with SPDE. The sas7bdat is a binary proprietary format. SPDE files should be in that same vein. However, the sas7bdat and spde are both ODBC compliant. Download the ODBC drivers and use them vs using the binary readers.

For simple SAS data reading, use ODBC and OleDB where possible. They are not perfect but they should give you what you need. This also keeps you from needing R to read a SAS binary protocol.

Re: Reading a spde file into power BI

Kurt_Bremser — Thu, 16 Jun 2022 15:15:11 GMT

.spde files come from SAS libraries using the Scalable Performance Data Engine. You usually have more than one .spde file for a single dataset, as SPDE is designed to work with several discrete "buckets" on discrete hardware, so the I/O load is spread across devices. To successfully use them, you would also need to recreate the structure of the original SPDE library (where is the primary directory, which bucket goes to which secondary directory, ...)

So it might very well be that your file is useless outside of the original SAS installation.

Re: Reading a spde file into power BI

SASKiwi — Thu, 16 Jun 2022 20:21:38 GMT

There are many ways to load data into Power BI. One way we use a lot is to load our SAS tables into SQL Server from where Power BI can read the tables directly. That's a lot easier than using CSVs or similar. I concur with @Kurt_Bremser that you will have to convert SPDE to a normal SAS library if you want to continue with the R approach.

Re: Reading a spde file into power BI

alepage — Thu, 16 Jun 2022 21:22:11 GMT

I have made some test , I have able to read / import the sashelp.class into Power BI using the R script below

library(sas7bdat)

require(sas7bdat)

Class1 <-read.sas7bdat("C:/Temp/PowerBiTest/class.sas7bdat")

When the dataset is on our unix server (see the R script below, the three dots are for safety purpose), I receive an error unable to open the file.

library(sas7bdat)

require(sas7bdat)

Class1 <-read.sas7bdat("/finsys/.../datasets/class.sas7bdat")

Could you please provide me an example how to load a sas dataset using SQL server connection?

Regards,

Re: Reading a spde file into power BI

SASKiwi — Thu, 16 Jun 2022 23:33:49 GMT

PROC DATASETS is one of the easiest ways:

libname sqlsrvr odbc noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"
 qualifier = "MyDatabase" schema = "MySchema" insertbuff = 10000;

proc datasets library = sqlsrvr nolist;
  append base = MySQLTable data = MySASLib.MySASTable;
run;
quit;

This appends rows from the SAS dataset MYSASTABLE to the SQL Server table MYSQLTABLE. If it doesn't exist, it will be created, assuming you have the SQL permissions to do so.

Re: Reading a spde file into power BI

alepage — Fri, 17 Jun 2022 12:41:32 GMT

Do I need to put that script into Power BI or I convert my sas database as an oracle table on a specific server then I connect to that sql server in power bi to obtain my table ?

Re: Reading a spde file into power BI

SASKiwi — Fri, 17 Jun 2022 23:48:32 GMT

What SAS/ACCESS products do you have? I assumed you had ODBC which will work with any ODBC-compliant database including SQL Server. Run PROC SETINIT to confirm:

proc setinit;
run;

That script is a SAS program and you run it from SAS to load a SAS table into an SQL Server database.

Re: Reading a spde file into power BI

alepage — Mon, 20 Jun 2022 14:22:03 GMT

Here's the logfile content from the execution of proc setinit;run;

Original site validation data
Current version: 9.04.01M7P080520

CPU A: Model name='' model number='' serial='+4'.
Expiration: 29JUN2023.
Grace Period: 15 days (ending 14JUL2023).
Warning Period: 18 days (ending 01AUG2023).
System birthday: 12MAY2022.
Operating System: LIN X64 .
Product expiration dates:
---Base SAS Software 29JUN2023 (CPU A)
---SAS/STAT 29JUN2023 (CPU A)
---SAS/GRAPH 29JUN2023 (CPU A)
---SAS Integration Technologies 29JUN2023 (CPU A)
---SAS/Secure 168-bit 29JUN2023 (CPU A)
---SAS Enterprise Guide 29JUN2023 (CPU A)
---SAS/ACCESS Interface to Oracle 29JUN2023 (CPU A)
---SAS/ACCESS Interface to PC Files 29JUN2023 (CPU A)
---SAS/ACCESS Interface to ODBC 29JUN2023 (CPU A)
---SAS Workspace Server for Local Access 29JUN2023 (CPU A)
---High Performance Suite 29JUN2023 (CPU A)
---SAS Add-in for Microsoft Excel 29JUN2023 (CPU A)
2 The SAS System 10:18 Monday, June 20, 2022

---SAS Add-in for Microsoft Outlook 29JUN2023 (CPU A)
---SAS Add-in for Microsoft PowerPoint 29JUN2023 (CPU A)
---SAS Add-in for Microsoft Word

Re: Reading a spde file into power BI

SASKiwi — Tue, 21 Jun 2022 01:39:02 GMT

SAS/ACCESS Interface to ODBC is licensed so you can use that to load SAS tables into SQL Server as I have already explained. You will obviously need to change the LIBNAME statement to point to your required SQL Server database. If you haven't done this before, I suggest consulting your SAS administrator.

Re: Reading a spde file into power BI

AlanC — Tue, 21 Jun 2022 05:59:07 GMT

One thing to keep in mind, SAS/Access to ODBC is a push operation from SAS and requires SAS to run. You can also use a pull operation using the SAS ODBC driver, which is free, and pull the data direct into PowerBI.

Re: Reading a spde file into power BI

LinusH — Tue, 21 Jun 2022 10:03:22 GMT

Even though the ODBC driver is free, you still need a SAS server to respond, either a local SAS installation, or a SAS/SHARE server (with the additional SAS/SHARE*NET license).

Re: Reading a spde file into power BI

AlanC — Tue, 21 Jun 2022 14:30:23 GMT

[Edited to removed ODBC-AC] SAS datasets can be read 100% using ~~ODBC~~/OleDb without a SAS server using the drivers from SAS. There are 3 providers. Use local provider not IOM or Share. Use it all of the time at client sites.

Re: Reading a spde file into power BI

LinusH — Tue, 21 Jun 2022 11:07:09 GMT

I was aware you could do it with OLEDB, but not ODBC. The documentation still states you need additional licensing, but if you have a link or similar to share it would be great!
https://documentation.sas.com/doc/en/odbcdref/9.4/p1esd8t7nrdvmfn1guh905g1dnvk.htm

Re: Reading a spde file into power BI

alepage — Tue, 21 Jun 2022 12:25:38 GMT

Could you please provide a link to the web page related to OLEDB sas documentation

Re: Reading a spde file into power BI

LinusH — Tue, 21 Jun 2022 12:29:03 GMT

https://documentation.sas.com/doc/en/oledbpr/9.4/n0heigqkqll8u9n1d9beeqkh0n2f.htm

Re: Reading a spde file into power BI

AlanC — Tue, 21 Jun 2022 14:29:14 GMT

I stand corrected. My examples are using OleDb.

Re: Reading a spde file into power BI

AlanC — Tue, 21 Jun 2022 14:44:22 GMT

I just did a quick test and connected to PowerBI: Instructions:

Go here: https://support.sas.com/downloads/package.htm?pid=2472#
Download the 1st driver (64 bit) and install it
Open PowerBI
Add OleDB datasource. Connection string: Provider=SAS.LocalProvider.1; Data Source=c:\temp\MySasData
See sample in attached picture

Re: Reading a spde file into power BI

alepage — Thu, 23 Jun 2022 14:44:37 GMT

Hello,

When I click on build, I see a selection of OLE DB connector.

Which one did you use?

How do you pass the server name in the username? host@username, 10.111.44.111@username ?

Does you file is hosted on a unix server ?

Up to now, all my connection tests failed

Re: Reading a spde file into power BI

AlanC — Thu, 23 Jun 2022 14:57:42 GMT

Use this technique for testing: http://www.polyweb.com/blog/index.php/archives/17

Go to connectionstrings.com for info on the parts of the connection string. SAS will also have documentation on their allowed parameters.

No, I didnt test with Unix. It shouldnt matter: you are just reading a file.