Mapping Documents in Cosmos DB through ODBC connection

[Anonymous]

I am trying to set up an ODBC connection via an On-premises data gateway so that we can access an Azure Cosmos DB and manipulate the data with R Scripting during the data load in Power BI.

I have managed to connect to the DB by following the tutorial here , but when the Schema Editor application samples the DB it fails to parse the Documents in the DB correctly, so that some fields come through in the connection and others do not.

I have successfully navigated this database on Power BI Desktop query editor by using the Cosmos DB connector, but the idea here is to be able to publish a report to the PBI Service while maintaining the ability to use R-scripting, which is not supported in the service, unless using a data gateway.

Has anyone had success with this type of scenario?

[Anonymous · Accepted solution]

I got around this by ignoring the recommended data connectors ( ODBC and Cosmos DB Connector ) and writing an R script to access the Cosmos DB API, so the "Source" in my power query was an Rscript, and this is all running through a personal gateway because R scripts do not work in Power BI service for some reason.  

This was an awful lot of hoops to jump through to get two Microsoft data products to talk to each other. Hopefully, they will get the Cosmos connector out of preview and working in the Service so that this is no an issue in the future.

[Anonymous]

Isn't it frustrating? 😞 

Would you be willing to share your R script? I'm STILL looking for a workable solution to this (I started that original thread you posted, many months ago...)

[Anonymous]

I am using this custom library: https://github.com/aaron2012r2/cosmosR which you will have to install on a machine hosting a personal gateway to use in power bi service. 

The library is not in the official CRAN repository so you'll have to pull it from Github. There are some instructions on how to get started with it here:

https://www.r-bloggers.com/query-azure-cosmos-db-in-r/ 

Unfortunately, that library does not have support for order-by, just for the fields, amount of items and where conditions.

[v-juanli-msft]

Hi @Anonymous 

Do you receive any error message in Power BI (Desktop /Service?)

Which fields doesn't come through the connection? R scripts?

Best Regards

Maggie

[Anonymous]

@v-juanli-msft No, the nested tables simply do not show up.  The ODBC Schema Editor has the option of saving the expected schema as a JSON file, and when I try to edit that to more accurately capture the structure of Cosmos DB I get some errors, but according to the discussion on this thread, this is a bug in the ODBC connection and MS is working on a fix.