Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Prakash Ananthaiya]

Dear All,

      The requirement is to connect Power BI Desktop and Power BI service, have to establish successful connection in the direct query mode with MongoDB, for the live reports (no data refresh schedule).

Please suggest the standard way of architecture and the Power BI connector.

Trials done as of now:

1)   Connected Power BI Desktop to MongoDB through Presto – I have installed Simba Presto ODBC driver (used Qubolepresto driver in Power Bi) and the connection is successful but the challenge is the array object data type in Mongo DB (Key value with Pair) is transformed in presto as array of array for example it is visible the field value without key, only value is visible in the fields

Expected result, field value of array data type converted into string and displaying in the preview of the presto ODBC connector

[
{“Quiz”:”Children category”, “Q1″:”How many vertices for rectangle”, “Q2″:”What is the shape of the earth”},
{“Quiz”:”Adult category”, “Q1″:”Number of country”, “Q2″:”World’s richest country”}
]

Actual Result: But it is visible only the values, no keys are appearing |(I mean the keys are “Quiz”:  “Q1”: “Q2”:

[
{“Children category”, “How many vertices for rectangle”, “What is the shape of the earth”},
{“Adult category”, “Number of country”, “World’s richest country”}
]

2) Connected Power BI Desktop to MongoDB directly using Mongo ODBC driver but by default all the 145 Quiz category is appearing as columns, whereas it is expected to appear as column like Quiz, Q1, Q2. I wont be able to transpose because the rows and columns getting multiplied for each like N to the power N.

3) Used On-Premise Data Gateway to connect power Bi service with on premise Mongo DB, when refreshing data set received the below error

Something went wrong
There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
Processing error: The following system error occurred: Out of present range. Unable to convert a value to the data type requested for table ‘news_api_dummy’ column ‘index’. The data provider was unable to convert the value ‘<pii></pii>’ from the source data type ‘VT_R8’ to the expected data type ‘VT_I8’. Please check the column data types in the data model and data source and ensure that the data types are compatible.
Cluster URI: WABI-WEST-EUROPE-B-PRIMARY-redirect.analysis.windows.net
Activity ID: a0632d13-9c41-4491-b935-d2d2ad5861bc
Request ID: bcfa4ea5-43df-ede2-5d30-8f293751ea99
Time: 2020-10-12 16:07:45Z

You might think why did I tried connecting to Mongo DB through Presto, this is because of  enterprise architecture.

Please help me the proper way of connecting Power Bi with MongoDB and to solve the above explained issues.

Thanks & Regards

Prakash Ananthaiya

——————————
Prakash Ananthaiya
Power BI Specialist
——————————

[Lutz Bendlin]

have you tried Atlas? https://www.mongodb.com/products/bi-connector

——————————
Lutz
——————————
——————————————-