cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Mapping Documents in Cosmos DB through ODBC connection

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?

1 ACCEPTED SOLUTION
Helper II
Helper II

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.

View solution in original post

5 REPLIES 5
Helper II
Helper II

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.

View solution in original post

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

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.

Community Support
Community Support

Hi @kgmccann 

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

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

 

Best Regards

Maggie

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

 

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors