Forum Discussion

rod_puente's avatar
rod_puente
Helper I
7 months ago
Solved

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"....
Need Help
  • v-dineshya's avatar
    v-dineshya
    6 months ago

    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 Learn

     

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

     

    Regards,

    Dinesh

     

rod_puente's avatar
rod_puente
Helper I
6 months ago

Dear v-dineshya 

 

Pls, could you please send me an example?

 

I continue with the problem

 

Regards

Rodrigo

v-dineshya's avatar
v-dineshya
Community Support
6 months ago

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 Learn

 

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

 

Regards,

Dinesh