Dynamic Power BI parameters into MongoDB Atlas SQL

[programmerdavid]

I am attempting to put together a replacement report in Power BI pulling data from a MongoDB Atlas Cloud instance. The collection I am querying is large dataset with hundreds of millions of documents in the collection.

What I am attempting to do is effectively the following in SQL against the MongoDB collection instead of processing once all the results are returned:

select

column1,

column2,

column3

where column1 = <User Parameter>

I have attempted several different avenues but I am still unable to have the filtering occur from the query. Looking at the diagnostics of the query, I do not see any of the routes I have attempted passing in the parameter to the call to MongoDB using the ODBC connection. 

Thanks in advance for any help that can be offered!

[programmerdavid]

I am specifically looking to pass in a parameter to filter the query. I have not seen any of the examples showing filtering at the source. The filtering at the source is the key component I am still looking for. I have been connected to Mongo but as mentioned the table set is millions of rows so it must be filtered at the source instead of brought in for processing.

Please see the component I am looking for:

What I am attempting to do is effectively the following in SQL against the MongoDB collection instead of processing once all the results are returned:

select

column1,

column2,

column3

where column1 = <User Parameter>

 Using my data source with the attempt to pass in a parameter in the query responds with "This query doesn't support parameters."

Simplified data source with identifying information removed. Additionally, as mentioned above, the number field that is being used for filtering is set up as an index. However, performance is not the concern at the moment as it cannot be run to bring back the data.

let
    Source = Value.NativeQuery(
        MongoDBAtlasODBC.Contents("mongodb://MYDataString", "DatabaseName", []){[Name="DatabaseName",Kind="Database"]}[Data],
        "SELECT
            `_id` AS `Id`, 
             `Number` AS `Number`, 
            COALESCE(`Property`, '') AS `Property`
        FROM `Changes`
        WHERE `Number` = ?",
        {NumberParameter},
        [EnableFolding=true]
    )
in
    Source

Additional documentation I used for the connector directly on MongoDB's site. https://www.mongodb.com/docs/atlas/data-federation/query/sql/powerbi/connect/

[v-menakakota]

Hi @programmerdavid,

Thank you for following up with the reply. Dynamic parameters won't work in this case. However, you can create a parameter and pass it to your SQL query. But consider these points: 

* Instead of "?", replace it with the actual parameter name.

* If you're working with a number parameter, make sure to concatenate it properly after "=".

* First, create a variable for your query and then pass that variable to the query parameter. When entering source make sure that after "Kind = Database", there should be a "query = parameter".

You can use the connector and pass the query, then generate the m-code and update your m-code from the auto generated code.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[v-menakakota]

Hi @programmerdavid ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @programmerdavid ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @programmerdavid ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[programmerdavid · Accepted solution]

I was able to get this resolved after returning from unexpected leave. We made several changes to it in order to resolve it. I have an example query below of how we were able to set it up and run it. Hopefully the additional information below helps others else in the future.

<insert connection string here> should be replaced with the information provided from mongo db, it will start with mongodb://. only one mongodb:// is needed.

<DatabaseName> should be the database you want to connect to on MongoDB.

InputNumberParameter is set up as a parameter to be provided.

let
    NumberParameter = InputNumberParameter,
    Query = "SELECT
            `_id` AS `Id`,
            COALESCE(`Identifier`, '') AS `Identifier`
        FROM `Collection`
        WHERE `Number`='" & NumberParameter & "'",
    Source = Value.NativeQuery(MongoDBAtlasODBC.Contents("mongodb://<insert connection string here>", "<DatabaseName>", []){[Name="<DatabaseName>",Kind="Database"]}[Data], Query, null, [EnableFolding=true])
in
	Source

[Akash_Varuna]

Hi @programmerdavid did you check for Query Folding whether it has been enabled or not and for faster indexing create your column as indexed one and pass the parameter through power bi by creating a for user input

[programmerdavid]

The column is set up as indexed on the source. The problem is the parameter is not being passed through as a folding parameter if I set it up as filtering and processes on the full set when it is returned. And none of the syntax on the advanced query building has allowed me to pass in the parameter to build it.

If there's a specific setting I should be trying to work with MongoDB, I would appreciate that detail.

I have tried to use & to concatenate and that is not working. Along with passing in a parameter referenced as @parameterName. 

[v-menakakota]

Hi @programmerdavid ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.
 
Upon reviewing the information provided please go through below documentation which may assist you in addressing the query.
MongoDB Atlas SQL interface connector - Power Query | Microsoft Learn

Also refer the below solved links for more understanding.
Solved: How to connect to Mongo DB via ODBC connection on ... - Microsoft Fabric Community
Solved: Connect Power BI service to Mongo Atlas - Microsoft Fabric Community

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.