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

    • 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