Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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 🙂
@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.
Thank you @Anonymous
You got me cooking!
There needs to be an I owe this person a beer button in the Power BI Community.
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
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.
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
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.
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?
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
Yes. You can use olapR Package with the Microsoft R Server.
https://docs.microsoft.com/en-us/machine-learning-server/r-reference/olapr/olapr
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |