Connect Power BI / data federation Mongo DB and MongoDB Atlas SQL connector beta)

[adrianVE278]

I am trying to connect my power bi desktop to a Mongo DB federated database instance.

I followed the previous steps for it, in summary:

* I have a user in mongo atlas with readAnyDatabases permission.

* start my federated cluster, in this case read from another mongoDb cluster (not from AWS S3 service)

* install the mongod db atlas ODBC connector ( atlas sql odbc 0.1.4)

* I have my connection URL with the name of the federated database (everything is fine with it)

It can even connect my deferred cluster with Monngo Compass (visualizing the data successfully) so I assume that all the configuration, mentioned above is correct.

however when I connect via Power BI (using the URI of the mongoDB federated database and the database name) the data I get in data transform has a "strange" format where I only see the ids and some other data , but not as such the data that I need to read from the collection added to the federated database.

Am I missing something in the configuration, or has someone else been able to perform this task without any problem.

Here is an image of what the loaded data looks like, however it is not the data contained in the database at all

[marzwrob]

Is there a way to transform this data in PowerBI to a table-like data?

[adrianVE278 · Accepted solution]

The problem has been solved since I wrote, what happens is that the preview of the collection looks like the photo in the original post, however, when loading the data to transform it (or just loading it), it looks like the attached photo . But, it loads the subdocuments as objects literally as opposed to tables linked by _id as it does or to the MongoBI connector when it loads successfully.

[v-shex-msft]

Hi @adrianVE278,

The data connector should design based on data source api to getting data. So these result fields may be different from the common table that you visited in the data source. (they may include some logic and flag field that be used in backend processing or other type of operations)

Regards,
Xiaoxin Sheng

[marzwrob]

Is there a way to transform this data in PowerBI to a table-like data rather an object? Getting the same issue loading from MongoDB via MongoDB Atlas connector