I hope that my inquiry is relatively simple, but I have searched thoroughly for a solution, with no luck.
I am evaluating MongoDB Atlas using the M0 Shared Tier Cluster. I have successfully loaded my data to into three collections. I am able to connect to my cluster and view my data in MongoDB Compass. Everything looks fine.
The goal of this project is to view the data using Tableau and Power BI.
While I was loading my data, I connected to my cluster using Tableau and the MongoDB Atlas connection driver. I was able to authenticate and view my data.
The problem is that I cannot see any field changes that were made after the moment I connected with Tableau. I cannot see a new field that I added to a collection, and I cannot see any data at all in a collection that I populated after connecting with Tableau.
From my reading I gather that I need to refresh the schema that MongoDB Atlas is presenting to the client (e.g. Tableau, Power BI). I am perplexed at how to accomplish this. I can find no option to do this, nor can I find an option to restart my cluster.
Before tackling the cloud-based Atlas, I installed MongoDB on my local computer. I faced the same issue. To fix it, I restarted the mongosqld process that’s part of the MongoDB BI Connector package. However, as I’m not using mongosqld to connect to Atlas, that isn’t an option.
The command you need is FLUSH SAMPLE executed from the SQL Client. When running a local BI-Connector the schema can also be periodically refreshed with the startup option --schemaRefreshIntervalSecs <number> or the corresponding configuration file option.
We have upgraded to the M10 tier, but I’m still having the problem.
I have successfully uploaded my data to our cluster, and I can see the data using MongoDB Compass.
I have installed the connector for Microsoft Power BI. I’m able to connect to the database, but I don’t see the fields I should be seeing. I put FLUSH SAMPLE in the field for the initial query, and I get the error below.
I have located the option to enable the Business Intelligence Connector. I notice that there’s an additional cost for this, and MongoDB suggests I use the Atlas SQL Interface instead. Correct me if I’m wrong, but using the Business Intelligence Connector involves configuring an ODBC entry on each report user’s computer, whereas with the Atlas SQL Interface, I only have to install, but not configure, the ODBC connector.
I would be happy to elaborate if anything I’ve said is unclear.
@Rick_Byrne Ok - It was unclear if you were using Atlas SQL or the BI Connector. Ideally, you are using Atlas SQL since this is newer and BI Connector will be replaced.
Flush is only recognized with BI Connector. I have reviewed your initial post and I can suggest a few things.
First, you are using Tableau Desktop (I believe), and to use Atlas SQL an M0 will work just fine. You will have needed to enable Atlas SQL (either with the Quickstart or the Advanced) and this enablement will provide you with the SQL endpoint (it is actually pointing to a federated data base). It sounds like you passed in this SQL endpoint, a database name (this is necessary), and your login credentials. You see your data (represented as tables and rows), but some fields (maybe they are new) are not being represented?
To fix this, we need to regenerated you SQL Schema. You can do this today within MongoSH. We have some Atlas UI for SQL Schemas coming out in March/April - so this will be much easier then. Until the UI is available, you can generate a new SQL Schema by running this command. I would use a sample size that is large enough to find the missing fields. You run this command by namespace, so it doesn’t take long to run. Now, if you run this and still those missing fields do not show up, there is a command to force or set the schema, but let’s start with the generate command.
Thank you for this information. I’m pleased to learn that MongoDB will be favoring the Atlas SQL going forward, because the Atlas SQL is easier to deploy on the machines of the users who will be using my reports.
Thank you for the clarification that flush is only recognized with the BI Connector.
Exactly. This is a very eloquent explanation of my situation.
Running this command solved my problem. Thank you. For the benefit of others who may find this post in the future, replace the word “datalake” with the name of your database. Replace testDL with the name of your collection, or, as in the last code example, replace testDL is an asterisk to rebuild the schemas for all your collections.
Thank you again for your patient assistance, Alexi.
