Forum Discussion
R script for starting.
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'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
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.
Yes. You can use olapR Package with the Microsoft R Server.
https://docs.microsoft.com/en-us/machine-learning-server/r-reference/olapr/olapr
