connecting to a database in R using an Office Data Connection (.odc) file from Power BI

Question

I have been asked to make a bunch of charts for a large organization and have been given access to their Power BI dashboard. I want to go around Power BI's interface so I can make the charts in R. Power BI offers an 'analyse in excel' option which lets you download an .odc file to open in excel. The .odc file doesn't contain the data it just specifies a database connection for excel. However, this only works on a windows computer and I have a mac. If need be I will try to figure out how to install a Windows virtual machine but I thought I would first try here:

The .odc file specifies a connection type and string, e.g.

<odc:Connection odc:Type="OLEDB">   
<odc:ConnectionString>Provider=MSOLAP;Integrated Security=ClaimsToken;
Identity Provider=https://login.microsoftonline.com/common, 
https://analysis.windows.net/powerbi/api, 929d0ec0-7a41-4b1e-bc7c-b754a28bddcc;
Data Source=pbiazure://api.powerbi.com;
Initial Catalog=2df8e9ba-9ee5-43fb-bbb0-ba622b9cdc2b;
MDX Compatibility= 1; MDX Missing Member Mode= Error; 
Safety Options= 2; Update Isolation Level= 2; </odc:ConnectionString>

I was hoping it would be possible to connect to the underlying database in R using dbplyr or some other package but I am uncertain where to start. This answer for how to do it in python looks promising Reading a dataframe from an odc file created through excel using pandas but I got an error relating to my python installation. I'd rather get it to work in R.

Is there enough information in the .odc file to connect via dbplyr?

Sporran

Answer 1

What you posted in your question appears to be a OLE DB connection string for an Azure database. Connections strings are particular to the source (e.g. Azure or SQL) you are connecting to, not the program that is connecting (e.g. R or Power BI).

However R and dbplyr appear to work primarily with ODBC connections not OLE DB (reference 1, and reference 2).

You might be able to use this link to translate the OLE DB connection string to an ODBC connection string. However this does require that the source accepts/has the drivers for ODBC connections.

I would recommend first checking whether there is an ODBC connection available to you as this would be much simpler.

Once you have a connection string, connecting to the source should run something like this:

connection_string <- "DRIVER=...; DATABASE=...; SERVER=...;"
db_connection <- DBI::dbConnect(odbc::odbc(),. connection_string = connection_string)
remote_table <- dplyr::tbl(db_connection, from = "name_of_remote_table")

Correlative

Thanks @Simon.S.A this is a useful place for me to start trying to figure out a solution – Sporran

@Sporran did you figure this out? I'm stuck on the very same problem. – Jinny

If you could post your answer here or there: https://mcmap.net/q/1914933/-open-odc-connection-in-r that'd be appreciated – Jinny

Anyone figure this out? – Sugar