Forum Discussion
MONGO DB API CONNECTION ERROR
Dear All
Pls I need your help with the following error. I currently have a PBI modeled with an API created in MongoDB, but I'm getting the following error "The connection has ended unexpectedly".
Is there any way to intervene in the query by giving it more time?
Regards
Rodrigo
Hi rod_puente ,
Please try below steps to fix the issue.
1. Create parameters, In Power BI Desktop, Manage Parameters --> New Parameter.
Parameter 1
Name: RangeStart
Type: Date/Time
Current Value: 01/01/2021 00:00:00Parameter 2
Name: RangeEnd
Type: Date/Time
Current Value: NowNote: Data type must be Date/Time, not Date.
2. Please refer below sample Base MongoDB API query and do changes your M code.
let
Source =
Json.Document(
Web.Contents(
"https://data.mongodb-api.com/app/<app-id>/endpoint/data/v1/action/find",
[
Headers = [
#"Content-Type" = "application/json",
#"api-key" = "<API KEY>"
],
Content = Text.ToBinary(
Json.FromValue([
dataSource = "Cluster0",
database = "SalesDB",
collection = "Orders"
])
),
Timeout = #duration(0, 0, 15, 0)
]
)
),Documents = Source[documents],
ToTable = Table.FromList(Documents, Splitter.SplitByNothing()),
Expanded = Table.ExpandRecordColumn(
ToTable,
"Column1",
{"orderDate","orderId","amount"}
FilteredByDate =
Table.SelectRows(
Expanded,
each
[orderDate] >= RangeStart
and [orderDate] < RangeEnd
)
in
Expanded3. Verify your query folding, In Power Query Editor, Right‑click FilteredByDate and Select View Native Query. If it is ENABLED, it is fine. If disabled, your API does not support server‑side filtering, you must push the filter inside the API body.
Please refer below sample MongoDB filter.
Content = Text.ToBinary(
Json.FromValue([
dataSource = "Cluster0",
database = "SalesDB",
collection = "Orders",
filter = [
orderDate = [
"$gte" = RangeStart,
"$lt" = RangeEnd
]
]
])
)4. Configure Incremental Refresh in Model, Go to Model view -->Select your table --> Open Incremental refresh.
Configure:
Enable Incremental refresh
Store rows from the last: 3 Years
Refresh rows from the last: 5 DaysClick OK and Publish to Power BI Service
5. FIRST refresh in Power BI Service, Power BI creates partitions for 3 years of data
6. MongoDB side optimization, On MongoDB, ensure below index exists:
db.Orders.createIndex({ orderDate: 1 })
Note: Without above index, timeouts continue even with incremental refresh.
Please refer below link.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
6 Replies
It might be due to the restrictions implemented at the source end. May be you can check with your source team and ask if there any such restirctions.
Since you are asking for a workaround to add some delay to each query, I would suggest you to check out this blog written by Chris webb, where he explained how you can add delay using M functions (Function.InvokeAfter)
https://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/
Connect on LinkedIn
Read my blogs here: techietips.co.in
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
Hi rod_puente ,
Thank you for reaching out to the Microsoft Community Forum.
Hi tharunkumarRTK , Thank you for your prompt response.
Hi rod_puente , In addition to tharunkumarRTK response, i have added one more point.
Use Incremental Refresh, Create RangeStart / RangeEnd DateTime parameters. Filter your query on a Date/DateTime column: DateColumn >= RangeStart and DateColumn < RangeEnd.
In Model --> Incremental refresh, choose like Store last 3 years / Refresh last 5 days. Publish, refresh once to materialize partitions, then subsequent refreshes will fast.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi rod_puente ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @rod_puente ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Dear v-dineshya
Pls, could you please send me an example?
I continue with the problem
Regards
Rodrigo
Hi rod_puente ,
Please try below steps to fix the issue.
1. Create parameters, In Power BI Desktop, Manage Parameters --> New Parameter.
Parameter 1
Name: RangeStart
Type: Date/Time
Current Value: 01/01/2021 00:00:00Parameter 2
Name: RangeEnd
Type: Date/Time
Current Value: NowNote: Data type must be Date/Time, not Date.
2. Please refer below sample Base MongoDB API query and do changes your M code.
let
Source =
Json.Document(
Web.Contents(
"https://data.mongodb-api.com/app/<app-id>/endpoint/data/v1/action/find",
[
Headers = [
#"Content-Type" = "application/json",
#"api-key" = "<API KEY>"
],
Content = Text.ToBinary(
Json.FromValue([
dataSource = "Cluster0",
database = "SalesDB",
collection = "Orders"
])
),
Timeout = #duration(0, 0, 15, 0)
]
)
),Documents = Source[documents],
ToTable = Table.FromList(Documents, Splitter.SplitByNothing()),
Expanded = Table.ExpandRecordColumn(
ToTable,
"Column1",
{"orderDate","orderId","amount"}
FilteredByDate =
Table.SelectRows(
Expanded,
each
[orderDate] >= RangeStart
and [orderDate] < RangeEnd
)
in
Expanded3. Verify your query folding, In Power Query Editor, Right‑click FilteredByDate and Select View Native Query. If it is ENABLED, it is fine. If disabled, your API does not support server‑side filtering, you must push the filter inside the API body.
Please refer below sample MongoDB filter.
Content = Text.ToBinary(
Json.FromValue([
dataSource = "Cluster0",
database = "SalesDB",
collection = "Orders",
filter = [
orderDate = [
"$gte" = RangeStart,
"$lt" = RangeEnd
]
]
])
)4. Configure Incremental Refresh in Model, Go to Model view -->Select your table --> Open Incremental refresh.
Configure:
Enable Incremental refresh
Store rows from the last: 3 Years
Refresh rows from the last: 5 DaysClick OK and Publish to Power BI Service
5. FIRST refresh in Power BI Service, Power BI creates partitions for 3 years of data
6. MongoDB side optimization, On MongoDB, ensure below index exists:
db.Orders.createIndex({ orderDate: 1 })
Note: Without above index, timeouts continue even with incremental refresh.
Please refer below link.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
