How to input data from MongoDB into ADF pipeline to refresh PBI dataset

Question

How to input data from MongoDB into ADF pipeline to refresh PBI dataset

Alan Pitthan 40

Hello,

I have seen that is possible to refresh powerbi datasets using an ADF pipeline. However, I have the task to refresh said datasets using updated data from MongoDB. So, how setup this connection and make it automated? In a short manner, I would like to be able to parse the JSON
string from mongo into the request´s body on the API that connects to powerbi dataset on the ADF pipeline.
It would be much appreciated if I found a tutorial video about that.

Thank you,

VasimTamboli 5,215 Reputation points

2023-05-31T15:09:40.9933333+00:00
To input data from MongoDB into an Azure Data Factory (ADF) pipeline and refresh a Power BI dataset, you can follow these steps:

Set up a MongoDB Linked Service in ADF:

In your ADF instance, go to the Author & Monitor page.

Click on "Connections" in the left-hand menu.

Create a new Linked Service for MongoDB and provide the necessary connection details, such as server name, database name, authentication method, and credentials.

Create a Data Pipeline in ADF:

Go to the Author & Monitor page and click on "Author" to create a new pipeline.

Drag and drop the MongoDB source connector from the toolbox onto the pipeline canvas.

Configure the MongoDB source connector by selecting the previously created MongoDB Linked Service and specifying the collection or query you want to fetch the data from.

Add a Data Transformation Activity (if needed):

If you need to transform or manipulate the MongoDB data before passing it to Power BI, you can add appropriate data transformation activities (e.g., Data Flow, Mapping Data Flow, or Wrangling Data Flow) to the pipeline.

Add a Power BI Dataset Refresh Activity:

Drag and drop the Power BI Dataset Refresh activity from the toolbox onto the pipeline canvas.

Configure the Power BI Dataset Refresh activity by selecting the appropriate Power BI Linked Service and specifying the dataset you want to refresh.

Connect the MongoDB source to the Power BI Dataset Refresh activity:

Connect the output of the MongoDB source connector to the input of the Power BI Dataset Refresh activity by dragging and dropping the arrow between them.

Publish and Trigger the Pipeline:

Save and publish the pipeline.

Set up a trigger for the pipeline to automate the data refresh process. You can configure triggers based on a schedule, event, or manual execution.

By following these steps, you will be able to extract data from MongoDB, transform it if needed, and then refresh the Power BI dataset using an ADF pipeline.

For tutorial videos and detailed guidance, you can refer to the official Microsoft documentation for Azure Data Factory, which provides step-by-step instructions and examples for working with MongoDB and Power BI integration in ADF.
QuantumCache 20,366 Reputation points Moderator

2023-06-07T00:37:07.1733333+00:00

Hello @Alan Pitthan
Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!
Sedat SALMAN 14,180 Reputation points MVP

2023-06-07T01:18:34.8966667+00:00

Hi Alan I could not find a video but maybe the following guides can help you

Connecting MongoDB to Azure Data Factory

https://docs.microsoft.com/en-us/azure/data-factory/connector-mongodb

Creating an ADF Pipeline

https://docs.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-dot-net#create-a-pipeline

Transforming MongoDB Data

https://docs.microsoft.com/en-us/azure/data-factory/data-flow-transform

Connecting ADF to Power BI

https://docs.microsoft.com/en-us/rest/api/power-bi/

Automating the ADF Pipeline

https://docs.microsoft.com/en-us/azure/data-factory/how-to-create-schedule-trigger

Parsing the JSON String

https://docs.microsoft.com/en-us/azure/data-factory/control-flow-parse-json-activity

Hope this helps you
Alan Pitthan 40 Reputation points

2023-06-13T12:19:50.05+00:00

Thank you all for the assistance, it looks exactly what I'm needing. I'll let you know the outcome of the setup.
Alan Pitthan 40 Reputation points

2023-06-13T20:22:37.3333333+00:00

Hello,

Today I tried the setup at work. We initially tested to update a published dataset with corresponding data from mongoDb, (originally the dataset was conceived in pbi-desktop through ODBC connection).

The linked service worked flawlessly as the data was being pulled from mongoDb without trouble, however we got an issue with the POST api where it would either return error 403 - forbidden, or error 2108 - user configuration issue. Those errors varied while we were trying to find a solution by changing the Authentication type.

On powerbi online we did the needed configuration on the admin portal and even added the azure ADF user as admin or contributor of the workspace.
One thing that caught my attention is that we have this warning on the configuration tab of the targeted dataset:

I was wondering if some datasets are "blocked" to be updated from the ADF method depending if they were created with the ODBC connection.

I hope that our issue is related with a wrong config in the ADF pipeline rather than a possible locked state on the dataset itself.

1 answer

Your answer

QuantumCache 20,366 Reputation points Moderator

2023-06-07T00:37:07.1733333+00:00

Hello @Alan Pitthan
Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!
Sedat SALMAN 14,180 Reputation points MVP

2023-06-07T01:18:34.8966667+00:00

Hi Alan I could not find a video but maybe the following guides can help you

Connecting MongoDB to Azure Data Factory

https://docs.microsoft.com/en-us/azure/data-factory/connector-mongodb

Creating an ADF Pipeline

https://docs.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-dot-net#create-a-pipeline

Transforming MongoDB Data

https://docs.microsoft.com/en-us/azure/data-factory/data-flow-transform

Connecting ADF to Power BI

https://docs.microsoft.com/en-us/rest/api/power-bi/

Automating the ADF Pipeline

https://docs.microsoft.com/en-us/azure/data-factory/how-to-create-schedule-trigger

Parsing the JSON String

https://docs.microsoft.com/en-us/azure/data-factory/control-flow-parse-json-activity

Hope this helps you
Alan Pitthan 40 Reputation points

2023-06-13T12:19:50.05+00:00

Thank you all for the assistance, it looks exactly what I'm needing. I'll let you know the outcome of the setup.
Alan Pitthan 40 Reputation points

2023-06-13T20:22:37.3333333+00:00

Hello,

Today I tried the setup at work. We initially tested to update a published dataset with corresponding data from mongoDb, (originally the dataset was conceived in pbi-desktop through ODBC connection).

The linked service worked flawlessly as the data was being pulled from mongoDb without trouble, however we got an issue with the POST api where it would either return error 403 - forbidden, or error 2108 - user configuration issue. Those errors varied while we were trying to find a solution by changing the Authentication type.

On powerbi online we did the needed configuration on the admin portal and even added the azure ADF user as admin or contributor of the workspace.
One thing that caught my attention is that we have this warning on the configuration tab of the targeted dataset:

I was wondering if some datasets are "blocked" to be updated from the ADF method depending if they were created with the ODBC connection.

I hope that our issue is related with a wrong config in the ADF pipeline rather than a possible locked state on the dataset itself.

Answer 1

QuantumCache 20,366 Moderator

Hello @Alan Pitthan Thanks for reaching out on this forum.
Few more clarifications could help better understand the scenario requirements!!!

Did you try connecting to the Mongo DB from ADF pipeline? and how does the data coming from MongoDB look like? any sample JSON format?

2 What is the actual DataSource for your PowerBI? is it any Database or any other sources?

And the requirement is to update the DataSource/DataSet of the PowerBi from ADF, right?

I see that you already know about the Dataset Refresh activity from ADF!!!

I have tried the PowerBi REST API to refresh the Dataset directly.
If your PowerBi is using any Database , then it can be updated and the PowerBi automatically gets refreshed as the backend dataset is updated!!! Please let us know your scenario and we would be happy to help!.
User's image

User's image

Alan Pitthan 40 Reputation points

2023-06-13T20:49:25.3066667+00:00

Hello @SatishBoddu-MSFT ,

Thanks for the repply,

Answering your questions:
1 - Yes, the connection works well! we can see the JSON from the collection contents on ADF. I just can't show it now because I cant access it on my current machine, but is pretty standard stuff (id, name, company, ....).

2 - Sorry I may not understand exactly your question. The published dataset on powerbi online was previously conceived in powerbi desktop, where the data was obtained from mongoDb through ODBC connector. The whole ADF setup is to now find a way to refresh this dataset from the original updated collection from mongoDb.

3 - That is correct.

A little more context: my task is related to the usage of powerbi-embedded inside an application. We have a maser user defined on azure which is used for authentication procedures when embedding reports. The embedding works fine, but the datasets need to be updated so the reports can keep up with recent data.

We tried the setup today, but we were having errors 403 (forbidden) and error code 2108 (user configuration issue) while testing the pipeline. THose errors varied while we change the authentication method of the POST request. I think it might be some misplaced detail on the ADF side, however there are a few uncertainty related to powerbi. In this sense, I would like to ask a couple questions:

a) In your setup do you need to also pass any sort of authentication token or credentials so the API request doesn't get blocked on powerbi?

b) The azure user that builds the ADF need to be included in the pbi workspace access as admin/contributor so the refresh action can happen?

c) I have this warning in the config tab for the pbi dataset that I was testing to update:

I cannot change anything in the datasource credentials for this dataset. This is what might be blocking the refresh from ADF?

d) Do you need to specify the data from mongoDb that you want to send to pbi in the POST request body?

e) Silly question, but you have to specifically have those 3 boxes on the pipeline?

Thank you kindly.

Today I tried the setup at work. We initially tested to update a published dataset with corresponding data from mongoDb, (originally the dataset was conceived in pbi-desktop through ODBC connection).

Share via

How to input data from MongoDB into ADF pipeline to refresh PBI dataset

1 answer

Your answer