R script for starting.

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

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.