Forum Discussion

yoshihirok's avatar
yoshihirok
Post Prodigy
10 years ago
Solved

R script for starting.

I want to a R script for starting R script in Power BI Desktop. Just like "Hello World" of some programming language.   I want to start the R language by Power BI Desktop.   Example:   How to t...
Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    I suspect querying the Iris data set is the closest thing to a hello world in R (it's a built in set of data on flower petal sizes, kind of the R equivalent to Adventureworks). Try this as a script.

     

    class(iris)
str(iris)

IrisInput <- iris
rpowerbi's avatar
rpowerbi
Advocate I
10 years ago

Thanks for your quick response BarneyL

 

Based on you example

Here's what I did:

 

library(RODBC)

cn <- odbcDriverConnect(connection="Server_Instance DatabaseName TableName;trusted_connection=yes;")
query <- "select * from [DatabaseName].[dbo].[TableName]"
data <- sqlQuery(cn,query)
data

 

Two things:

1.

cn <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=localhost;database=DW_Notts;trusted_connection=yes;")

Doesn't work for me, so I used

cn <- odbcDriverConnect(connection="Server_Instance DatabaseName;trusted_connection=yes;")

I tried to install the ODBCDriverConnect package but I get a msg saying:

 

package ‘odbcDriverConnect’ is not available (for R version 3.2.2)

 

2.I was able to execute the script on the R console but when I copy/paste the same script for power bi as the data source, it doesn't work

Here's the error msg:

 

Details: "ADO.NET: R script error.
Warning messages:
1: In odbcDriverConnect(connection = "server=ServerName_InstanceName;database=DatabaseName;trusted_connection=yes;") :
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect(connection = "server=ServerName_InstanceName;database=DatabaseName;trusted_connection=yes;") :
ODBC connection failed
Error in sqlQuery(cn, query) :
first argument is not an open RODBC channel
Execution halted
"

I'm I doing it right?

rpowerbi's avatar
rpowerbi
Advocate I
10 years ago

Is there a way to connect to the Sql Server Analysis Cube or Tabular Model from R

Please let me know if you have a sample script to connect to SSAS

 

 

Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago
    rpowerbi wrote:

    Is there a way to connect to the Sql Server Analysis Cube or Tabular Model from R

    Please let me know if you have a sample script to connect to SSAS

     

     

    Thanks

    I would assume so although I've never investigated how to do the query directly.

    If I wanted to do it and was feeling lazy about it I'd use something like the above code to run an SQL query and then use Openquery in the SQL to query the cube.