R script for starting.

[yoshihirok]

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 transform a csv by R script on Power BI Desktop.

Regards,

Yoshihiro Kawabata

[Anonymous · Accepted solution]

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

[tenaciousdata]

BarneyL,

Thanks for the hello world example.

Can you add how you would use the iris data in Power BI desktop and plot it.

Asking for a friend 🙂

[Anonymous]

---

@tenaciousdata wrote:

BarneyL,

Thanks for the hello world example.

Can you add how you would use the iris data in Power BI desktop and plot it.

Asking for a friend 🙂

---

Fortunately for your friend I was testing this out earlier in the week. Here are the examples I put together based on the Iris query I'd used before.

To run these turn on the R plots in options, add it to your report page and then drag Petal.Length, Petal.Width, Septal.Length and Septal.Width into the values section. Then add code to the R script editor that appears.

Note that you need the correct libraries installed first, I did this through RStudio.

This gives a simple scatter plot:

library(ggplot2)
qplot(Sepal.Width, Petal.Width, data = dataset)

And getting a bit more exotic this gives chernoff faces (my all time favourite silly chart option)

library("tcltk")
library("aplpack")
faces(dataset[1:20,1:4])

The charts work well although lag more than the native PowerBi stuff, they even change if you add a slicer on species and use it.

Given the chart source is just a script I suspect you can run calculations to the input dataset, I imagine this opens up some interesting possibilities.

[tenaciousdata]

Thank you @Anonymous 

You got me cooking!

There needs to be an I owe this person a beer button in the Power BI Community.

[Anonymous]

Glad to help out @tenaciousdata.

It will be interesting to see how far people can develop these things.

Speaking of which I found this blog today which is doing pretty much what I was pondering before (using forecasting algorithms in the R visual script). I suspect I'll be using this one a lot in the future.

http://www.datamic.net/blog/dynamic-forecasting-with-power-bi-and-r

[tenaciousdata]

Also,

Any ideas when generating the "Iris" dataset, one would get a

"Details: "ADO.NET: R script error.
Error: could not find function "Class"
Execution halted"

error.

Thanks.

[rpowerbi]

Hi Barneyl,

Thanks for the sample script

I was able to use your script as the R datasource for PowerBI, it works!

I need some help with..

I have a simple script to get data from SQL table using R

It works fine in the R console,but it doesnt work when I use it for Power BI as the datasource

#Here is the script

> library (RODBC)
> cn <- odbcDriverConnect(connection="Server Database Table;trusted_connection=yes;")
> mytable <- data.frame(sqlQuery(cn, "select * from [Database].[dbo].[Table]"))
> mytable

Please let me know how I can run my script as the R datasource for PowerBI

[Anonymous]

I'm not much of an R expert but I've been replicating this article and adapting it to connect directly to our database:

http://www.sqlservercentral.com/articles/Performance/119993/

The below code is the start of the process and works for me to give a table named data in Power BI desktop (based on the server being located at localhost).

library(RODBC)

cn <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=localhost;database=DW_Notts;trusted_connection=yes;")
query <- "select 
    object_name(ops.object_id)          as [Object Name]
                 , sum(CASE WHEN sysidx.indid < 2 THEN sysidx.rows ELSE 0 END) AS [Number of Rows]
                 , sum(ops.range_scan_count)         as [Range Scans]
                 , sum(ops.singleton_lookup_count)   as [Singleton Lookups]
                 , sum(ops.row_lock_count)           as [Row Locks]
                 , sum(ops.row_lock_wait_in_ms)      as [Row Lock Waits (ms)]
                 , sum(ops.page_lock_count)          as [Page Locks]
                 , sum(ops.page_lock_wait_in_ms)     as [Page Lock Waits (ms)]
                 , sum(ops.page_io_latch_wait_in_ms) as [Page IO Latch Wait (ms)]
                 from sys.dm_db_index_operational_stats(null,null,NULL,NULL) as ops
                 inner join sys.indexes as idx on idx.object_id = ops.object_id and idx.index_id = ops.index_id
                 inner join sys.sysindexes as sysidx on idx.object_id = sysidx.id
                 where ops.object_id > 100
                 group by ops.object_id
                 ORDER BY [Number of Rows] DESC"
data <- sqlQuery(cn,query)
tables <- data[, 1]
data <- data[, -1]

I think it might be an issue with object types, the items that appear in RStudio as Data get bicked up by Power BI the ones under Values don't.

[rpowerbi]

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]

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

[BhaveshPatel]

Yes. You can use olapR Package with the Microsoft R Server.

https://docs.microsoft.com/en-us/machine-learning-server/r-reference/olapr/olapr

[Anonymous]

---

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