Publishing R visual - ODBC connection string

[zukkster]

I have manged to create an R visual in the Desktop that runs a  stored procedure and charts the results. With a lot of trial and error I managed to work out the connection string required in the desktop.

con <- odbcDriverConnect( "Driver={SQL Server Native Client 11.0}; Server= ###########; Database=#######; UID=#######; PWD=########")
sql_str <- paste("EXEC contact_density_sent_band ", first_period, ", ", last_period )

strd_prod <- sqlQuery(con, sql_str, rows_at_time=1)

If I publish the report, then I get an ODBC connection error. I'm assuming I need to use the Gateway instead of my personal account? Any idea of what the syntax is likely to be to use the Gateway?

[v-yuta-msft · Accepted solution]

Hi zukkster,

Based on your description, you call R ODBC package to access your database in power bi R visual, right? The data you achieved through R ODBC package will not be saved as a dataset in your PBIX file so when you pulish your report there will occur an erro. So I would recommend you to import database data using the built-in power bi ODBC connector and then implement R script in R visual with the dataset in power bi.

Regards,

Jimmy Tao

[zukkster]

The data table has hundreds of millions of records. Is importing this volume of data into Power BI going to be feasable? I've already tried to do this using DAX and while I can make it work, it takes 50 seconds to update the data when you change the date slices - compared to 2 seconds when you use R to send a strored procedure call to the table using ODBC.