cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kgmccann Regular Visitor
Regular Visitor

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

Accepted Solutions
kgmccann Regular Visitor
Regular Visitor

Re: Mapping Documents in Cosmos DB through ODBC connection

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
Community Support Team
Community Support Team

Re: Mapping Documents in Cosmos DB through ODBC connection

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

kgmccann Regular Visitor
Regular Visitor

Re: Mapping Documents in Cosmos DB through ODBC connection

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

 

 

kgmccann Regular Visitor
Regular Visitor

Re: Mapping Documents in Cosmos DB through ODBC connection

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

lianetoohey Regular Visitor
Regular Visitor

Re: Mapping Documents in Cosmos DB through ODBC connection

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

kgmccann Regular Visitor
Regular Visitor

Re: Mapping Documents in Cosmos DB through ODBC connection

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.

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors