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

 

Regards,

Yoshihiro Kawabata

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

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      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's avatar
        Anonymous
        Not applicable
        10 years ago

        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.

    • tenaciousdata's avatar
      tenaciousdata
      Advocate I
      10 years ago

      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's avatar
        Anonymous
        Not applicable
        10 years ago
        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.