Monday, December 12, 2016

Reading SAS files from Power BI using R Script

Recently I have been working to read SAS files (sas7bdat file format) in Power BI. SAS technical support and the Power BI community team have both worked with me to attempt to use the SAS Local Provider for OLE DB which is the only provider that does not require a SAS server.

We were able to use the SAS Local Data Provider 9.44 in Excel by using the Data Connection Wizard to access Data Link Properties and to read the sas7bdat data into Excel as a data source (see details on this at the bottom of this post).

However, following similar steps in Power BI lead to an error message because "the SAS Local Provider for OLE DB does not 'like' the GetOleDbSchemaTable method. (quote from SAS Technical support):
(Text of error: "The 'SAS Local Data Provider 9.44' provider is not registered on the local machine.")
Error Message
Text of error: "The 'SAS Local Data Provider 9.44' provider is not registered on the local machine."
Now, this error seems actionable until you remember that we just used the provider to connect from Excel. This is due to the use of the GetOleDbSchemaTable method and we could not find a way to work around this problem in Power BI.

I have submitted this as a support ticket to Microsof and I look forward to their response on how to get a supported connection to SAS files.

The next step is to find another way:
Loading Data into the Dashboard file using R Script:

The best method I have found so far to read SAS files into Power BI is using R script:
https://businessintelligist.com/2016/09/09/reading-sas-sas7bdat-files-in-power-bi/

1) Locate your SAS Data set files (you can download a sample set in ZIP format from: http://www.principlesofeconometrics.com/sas.htm)

2) Install R locally (I have been using R 3.3.2 from CRAN (https://cran.r-project.org/bin/windows/base/)

3) Install Power BI Desktop locally:
Download Power BI Desktop from http://app.powerbi.com

4) Open Power BI, and first, set your version of R in Options and Settings:
a. Click the “x” in the upper right corner of the splash dialog box to close it:
Close the Power BI Desktop dialog box
b.       Select File -> Options and Settings ->Options -> R Scripting
c.       You should be able to select your installed R version from the Detected R home directories list and Detected R IDEs
5) Now, setup the data source for your Power BI solution:
a.       Drop down Get Data and select More… -> Other –> R Script, then the Connect button
b.       The Execute R Script dialog box provides an input location for the following script which will set the working directory, access the library and read the file into Power BI:



c.       Click OK to see the content of the SAS file, select the table(s) to load  (multiple table files haven’t been tested by me using R Script)
d.       Select to Load the data directly, or Edit to use the Query Editor to filter, clean, reformat or otherwise transform the data for this and all future data loads.

6)      Click Close & Apply when the data set is complete and you are ready to use it in Power BI
 
Graph using Airline data from SAS file











Using a faster library:
I have found some information that haven is a faster library than sas7bdat, the sample files for SAS that I have access to are not sufficient to test this, but the haven library works very well:

Using haven the script changes to reference the haven library (https://cran.r-project.org/package=haven) and to use the read_sas function to read the SAS file from that library as you can see in this sample to read another data file, USA:
I am very interested to know what the results are using production size and complex datasets, and am willing to help in anyway that I can if you have questions about using this method.

Publishing and Refresh of data:
The Power BI solution can be published to http://app.Powerbi.com using the Publish button. The R Script has imported the dataset, so that will be published to the Power BI Service along with the reports.

R Script as a data source does not yet support refresh. There is a setting for refreshing data from Files, but that does not work for R Script as a source, it is for CSV, etc…


Rachael

25 comments:

Mohana M said...

Thanks for sharing this informative blog to our vision. Keep update your blog. If anyone interested to know more details about SAS then learn SAS Training in Bangalore will definitely helps you.Data Science Training in Bangalore

sai venkat said...

Hats off to your presence of mind..I really enjoyed reading your blog. I really appreciate your information which you shared with us.
SAS Online Training
R Programming Online Training|
Tableau Online Training|

Ishu Sathya said...

Excellent article on the importance of R programming in tableau tool. I am working in the tableau related project. I gain some new updated regarding the tableau tool R Programming. Keep updating the recent updates of R. Thank you admin.

Regards:

R Programming Course |
R Training in Chennai

Mary Adlena said...

You truly did more than visitors’ expectations. Thank you for rendering these helpful, trusted, edifying and also cool thoughts on the topic.
Android Project Center in Chennai | Android Project Center in Velachery

soumya Teja said...

This is an excellent blog thanks for sharing valuable information with us please check it once at MSBI Online Training

Apsara G said...

Interesting post! This is really helpful for me. I like it! Thanks for sharing! Java Training in Chennai

Gandhi I said...

Thanks for the informational and unique blog.
Digital Marketing Training Institute in Chennai | SEO Training in Chennai

viswanath said...

AWS Training in Bangalore - Live Online & Classroom
myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.


IOT Training in Bangalore - Live Online & Classroom
IOT Training course observes iot as the platform for networking of different devices on the internet and their inter related communication. Reading data through the sensors and processing it with applications sitting in the cloud and thereafter passing the processed data to generate different kind of output is the motive of the complete curricula. Students are made to understand the type of input devices and communications among the devices in a wireless media.

Anonymous said...

Hadoop concepts, Applying modelling through R programming using Machine learning algorithms and illustrate impeccable Data Visualization by leveraging on 'R' capabilities.With companies across industries striving to bring their research and analysis (R&A) departments up to speed, the demand for qualified data scientists is rising.
data science training in bangalore

Python is a high-level, interpreted, interactive and object-oriented scripting language. Python is designed to be highly readable. It uses English keywords frequently where as other languages use punctuation, and it h
as fewer syntactical constructions than other languages.python interview questions and answers

vijaya kumar said...

Thanks for sharing wonderful information which helps all auidance

devOps Training | devOps supports

Nila shri said...

Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts, have a nice weekend!

Data Science Training in Chennai

Hemapriya said...

This blog is very much helpful to us. Thanks for your information.
SAS Training Chennai
SAS Training Institute in Chennai
SAS Courses in Chennai
SAS Training Center in Chennai
SAS Analytics Training in Chennai

mounika r said...
This comment has been removed by the author.
Dharani M said...

Nice blog
sas training in Marathahalli

sas training institutes in Marathahalli

Pawel Co Faddey said...

Great Article
R Project Topics for Computer Science
FInal Year Project Centers in Chennai

JavaScript Training in Chennai
JavaScript Training in Chennai

Mirnalini Sathya said...


It is a wonderful data you offered to us I really enjoy by reading your article.
German Classes in Chennai
German Language Classes in Chennai
Big Data Training in Chennai
Hadoop Training in Chennai
Android Training in Chennai
Selenium Training in Chennai
Digital Marketing Training in Chennai
JAVA Training in Chennai
JAVA Course in Chennai

sathyaramesh said...

Brilliant blog!! I just love the content which you wrote in this post. It explained the stuff very well in a neat manner.
Keep posting!!
Hadoop Training in Chennai
Hadoop Course in Chennai
Web Designing Course in Chennai
PHP Training in Chennai
Big Data Training in Chennai
Hadoop Training in Velachery
Hadoop Training in Annanagar

cynthia williams said...

Truly a very good article on future technology. Thanks for discussing this, I love to learn more about this topic.
DevOps Training in Chennai
DevOps course in Chennai
AWS Training in Chennai
AWS Training in Velachery
Cloud Computing Courses in Chennai
RPA course in Chennai
DevOps Training in OMR
DevOps Training in Chennai

Kayal m said...

I recently saw your post! I was very happy to visit your blog. I like so much and I hope more valuable post from your post...

Unix Training in Chennai
Unix shell scripting Training in Chennai
Excel Training in Chennai
Corporate Training in Chennai
Embedded System Course Chennai
Linux Training in Chennai
Tableau Training in Chennai
Unix Training in Chennai
Unix shell scripting Training in Chennai

yuvanthi said...

I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision.keep blogging.
Regards,
PHP Training in Chennai
PHP Course in Chennai
Tally Course in Chennai
ReactJS Training in Chennai
microsoft dynamics crm training in chennai
PHP Training in Chennai
PHP Course in Chennai

lekha mathan said...

Very interesting post! Thanks for sharing your experience suggestions.
Aviation Courses in Chennai
air hostess academy in Chennai
Airport Management Training in Chennai
airport ground staff training in Chennai
Aviation Academy in Chennai
air hostess training in Chennai
airport management courses in Chennai
ground staff training in Chennai

Vicky Ram said...

Enjoyed your approach to explaining how it works, hope to see more blog posts from you. thank you!

yellowlocaldirectory
Education

priya rajesh said...

Excellent blog with unique content, thanks a lot for sharing this. I love to learn more about this topic.
Data Science Training in Chennai
Data Science Certification in Chennai
R Programming Training in Chennai
R analytics Training in Chennai
Machine Learning Training in Chennai
Machine Learning course
Data Science Training in Velachery
Data Science Course in Chennai

kavithasathish said...

Thanks for this innovative blog. Keep posting the updates.
pearson vue
German Language Classes in Chennai
IELTS Training in Chennai
Japanese Language Course in Chennai
spanish classes in chennai
Best Spoken English Classes in Chennai
Spoken English Classes in Tambaram
Spoken English Classes in Anna Nagar

for ict 99 said...

Great Article
final year projects on machine learning

Final Year Project Centers in Chennai


JavaScript Training in Chennai
JavaScript Training in Chennai