Forum Discussion

programmerdavid's avatar
programmerdavid
Regular Visitor
1 year ago
Solved

Dynamic Power BI parameters into MongoDB Atlas SQL

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...
Need Help
  • programmerdavid's avatar
    programmerdavid
    1 year ago

    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

     

v-menakakota's avatar
v-menakakota
Community Support
1 year ago

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's avatar
v-menakakota
Community Support
1 year ago

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's avatar
    programmerdavid
    Regular Visitor
    1 year ago

    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