Views on data federation

[Adolfo_Adrian]

Hello,

indeed I was able to connect using the sitaxis

MongoDBAtlasODBC.Query("mongodb://federateddatabaseinstance0-z7f7m.a.query.mongodb.net/data_base?ssl=true&authSource=admin",
"Flexiweb_Production",
"Select *
from Flexiweb_Production.Batch
WHERE CAST(EXTRACT(YEAR FROM date )as integer)>=2023")

my question now is, in the configuration of my federated database there is an option to add a view

In this view, I can add a pipeline that is something like

db.Batch.aggregate([
   {
     $match: {
       CreationDate: {
         $gte: ISODate("2023-01-01T00:00:00.000Z"),
         $lt: ISODate("2024-01-01T00:00:00.000Z")
       }
     }
   }
])

The idea is to create an already filtered view so that power bi already connects to that collection. Is that possible?

[Jason_Tran]

Hi @Adolfo_Adrian,

I’ve not yet tested this on Power BI but I believe it seems to work on DBeaver assuming you just want to see the output from the view. For example from my test environment:

Initial data:

AtlasDataFederation testdb> show collections
collection
AtlasDataFederation testdb> db.collection.find()
[
  { _id: ObjectId("64accacd1c9d59c7264bbfdf"), a: 3 },
  { _id: ObjectId("64accacd1c9d59c7264bbfdd"), a: 1 },
  { _id: ObjectId("64accacd1c9d59c7264bbfde"), a: 2 },
  { _id: ObjectId("64accacd1c9d59c7264bbfe0"), a: 4 },
  { _id: ObjectId("64accacd1c9d59c7264bbfe1"), a: 5 }
]

View being created to match documents with {'a': 2}:

AtlasDataFederation testdb> db.createCollection( "testViewColl", { viewOn: "collection", pipeline: [{ $match: { 'a':2 } }] } )
{ ok: 1 }

Verifying the view:

AtlasDataFederation testdb> db.runCommand({"listCollections":1})
{
  ok: 1,
  cursor: {
    firstBatch: [
      {
        name: 'collection',
        type: 'collection',
        options: {},
        info: { readOnly: true }
      },
      {
        name: 'testViewColl',
        type: 'view',
        options: {
          viewOn: 'collection',
          pipeline: [ { '$match': { a: { '$eq': 2 } } } ]
        },
        info: { readOnly: true }
      }
    ],
    id: Long("0"),
    ns: 'testdb.$cmd.listCollections'
  }
}
AtlasDataFederation testdb> db.testViewColl.find()
[ { _id: ObjectId("64accacd1c9d59c7264bbfde"), a: 2 } ]

DBeaver table for the initial data:

DBeaver showing data for the view:

I’ll try test this out with Power BI connector when possible and update here with any information. But if you’re running into any issues so far let me know what the scenario / error messages are.

Regards,
Jason

[Adolfo_Adrian]

Hi @Jason_Tran thanks for your response.

I try this>>>>>

I create the view follow your example, tanking random _id form the colection

But, I go to Dbeaver and Power Bi and… I dont see the view

Connecting directly from power BI, I don’t see it either.

You don’t know if it is possible to create the view from here in the federated database configuration

I don’t know if what I want in that way is possible, I’m not an expert in mongo, but what you do is exactly what I want, to have a view to be able to consult it and be able to take filtered data.

Thanks,

[Jason_Tran]

@Adolfo_Adrian

but what you do is exactly what I want,

Thank you for confirming Adolfo!

@Adolfo_Adrian

I create the view follow your example, tanking random _id form the colection

What database did you create this on? Is it the same one where the collection belongs which the View is being created?

To make sure, can you perform the following in mongosh and provide the output when possible:

1. use <dbname> (where <dbname> is the name of the database that contains the collection where you would like to create the view against)
2. show collections (to verify the collection is there - I assume the collection is called Lote based off your screenshot.
3. From within the same db, run db.runCommand({ "create" : "<view-name>", "viewOn" :" <collection-name>", "pipeline" : ["<stage1>","<stage2>",...] }) (as you have in your screenshot). If this is already done, go to step 4.
4. Provide the output of db.runCommand({"listCollections":1})

Additionally, from the DBeaver instance, can you advise database value you used when connecting? It should be in the Driver Properties setting. Additionally, please provide the JDBC URL format you’ve used (Redact all credentials and sensitive information before posting here).

@Adolfo_Adrian

You don’t know if it is possible to create the view from here in the federated database configuration

I’ll need to test this later today as I’ve not yet done so but the View that I created from mongosh does appear in the Atlas UI at the same screen where you display “Create View”. I’ll let you know once I finish attempting to create this via the UI. In the meantime, were you having any particular issues creating the view from there?

Regards,
Jason

[Jason_Tran]

@Adolfo_Adrian

In this view, I can add a pipeline that is something like

db.Batch.aggregate([
   {
     $match: {
       CreationDate: {
         $gte: ISODate("2023-01-01T00:00:00.000Z"),
         $lt: ISODate("2024-01-01T00:00:00.000Z")
       }
     }
   }
])

@Adolfo_Adrian

You don’t know if it is possible to create the view from here in the federated database configuration

@Adolfo_Adrian - I created a view via the UI based off the $match example you made. Check out the below example. I don’t have any data which matches this view so I recommend testing it on your own environment to verify that it works:

1. Initial data federation configuration page with no view (yet):

2. Clicked “Add View” and used the following pipeline:

For your reference / to make it easier to copy and paste, here is the text snippet of the above pipeline:

[
  {
    "$match": {
      "CreationDate": {
        "$gte": { "$date": "2023-01-01T00:00:00.000Z" },
        "$lt": { "$date": "2024-01-01T00:00:00.000Z" }
      }
    }
  }
]

3. Hit Save for the Edit View screen.

4. Hit Save for the Data Federation instance configuration screen (highlighted in the red box):

5. (Optional since this is for your question regarding the creation of the View via the UI) - Connect to the instance and test the view out.

Regards,
Jason

[Adolfo_Adrian]

Helllo my friend …

I followed your steps as is, I was able to create the view successfully and connect via Power Bi. But unfortunately I have this error in the load. Do you know what it refers to?

ERROR

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] ERROR [HY000] [MongoDB][API] Caught panic: called Option::unwrap() on a None value Ok(“in file ‘C:\Users\mci-exec\.cargo\registry\src\index.crates.io-6f17d22bba15001f\mongodb-2.5.0\src\cursor\mod.rs’ at line 229”). '.

[Jason_Tran]

Hi @Adolfo_Adrian,

I’ve not encountered the error before but did you follow the Connect from Power BI documentation for setting up the connection?

Regards,
Jason

[Adolfo_Adrian]

Hello again.,

Yes, of course, I have followed all the steps but it throws me that error, I have not been able to find the documentation of said error either.

[Jason_Tran]

Hi Adolfo,

What’s the role associated with the database user trying to connect?

I was able to load up the following view in Power BI for Desktop:

You’ll also need to sqlGenerateSchema first before connecting. For example:

AtlasDataFederation admin> db.runCommand({ sqlGenerateSchema: 1, sampleNamespaces: ['VirtualDatabase.newView'], sampleSize: 100, setSchemas: true })
{
  ok: 1,
  schemas: [
    {
      databaseName: 'VirtualDatabase',
      namespaces: [
        {
          name: 'newView',
          schema: {
            version: Long("1"),
            jsonSchema: {
              bsonType: [ 'object' ],
              properties: {
                _id: {
                  bsonType: [ 'objectId' ],
                  additionalProperties: false
                },
                a: { bsonType: [ 'int' ], additionalProperties: false }
              },
              additionalProperties: false,
              required: [ '_id', 'a' ]
            }
          }
        }
      ]
    }
  ]
}

Note: 'VirtualDatabase.newView' is the namespace that includes reference to the view.

Hope this helps.

Regards,
Jason

[Jason_Tran]

For what it’s worth, please see my test environment details used for Power BI and the data federation view:

• Data federation instance using an M0 Atlas cluster namespace as a data source
• Power BI for Desktop
• atlasAdmin role associated with the database user connecting

Regards,
Jason

[Adolfo_Adrian]

indeed I can load the views, where segment tables, the error seems to be between the connectivity with PB