MONGO DB API CONNECTION ERROR

[rod_puente]

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 

[v-dineshya]

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

[rod_puente]

Dear @v-dineshya 

Pls, could you please send me an example?

I continue with the problem

Regards

Rodrigo

[v-dineshya · Accepted solution]

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:00

Parameter 2
Name: RangeEnd
Type: Date/Time
Current Value: Now

Note: 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
Expanded

3. 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 Days

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

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

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

[v-dineshya]

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

[tharunkumarRTK]

@rod_puente 

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!