Hello im triying the new Power BI connector. All my collections are showing, but some collections have missing information/columns when opened in the Power Query editor. What can i do to fix this?
Hello @Santiago_De_la_Pena_Miranda and welcome to the MongoDB Community! I am pretty sure that the underlying SQL schema just didn’t sample enough documents to represent some of these “missing” fields/columns. If you regenerate the SQL Schema, you can make sure these fields get represented. Here are some instructions:
Here is the command that you would run, of course you would change the db instance name from “datalake” to th name of your virtual db name and you can also change the sample size.
db.runCommand({sqlGenerateSchema: 1, sampleNamespaces: [“datalake.*”], sampleSize: 1000, setSchemas: true})
Hello Alexi_Antonino - I successfully implemented the above steps as mentioned by you. Unfortunately, I still don’t get to see the entire columns on Power BI. It would be really helpful if you could help me resolve the issue as I’m struck on this issue from many days.
Appreciate your help !
Below is the actual tables on MongoDB - I have around 74 fields with few nested objects.
Below is the list of columns that PowerBI has pulled ( around 15 columns )…rest is missing
Hi there @Srinivas_Jayaram - a few questions. When you ran the sqlGenerateSchema command, did the output/results list all of the columns in your collection? Did you change the sample size to a larger number to make sure all of the fields were sampled?
Here is what the output of the sqlGenerateSchema looks like (this is just a portion of my collection- but this shows an array field called items and the fields within items) - you should see all fields present in this schema:
If you run the sqlGenerateSchema command and do not see all of the fields you expect, please run this again and increase the sample size (you can up this to 20K even).
And my last question, did you enable Atlas SQL with the Quickstart? or did you manually create a federated database? If you manually created a federated database, I would want to make sure that you have all of the cluster collections separated into virtual collections. Sometimes users miss this:
Let me know if this is helpful.
Hi Alexi Antonino,
Thank you so much for the reply. I’m now able to get the desired field on PowerBI.
Step1 : I have increased the sample size to 10k
Step 2 : I have enabled Atlas SQL with Quickstart.
It worked for me !!! thank you so much for all your help. Appreciate it !!!
Thanks,
Srini
I’m so glad this works for you! Best to you and your Power BI adventures:)
Hello,
I am brand-new to Atlas projects. What ‘Shell’ are you referring to for running the sqlGenerateSchema command?
Thank you!
Hi @Jeremy_Morales welcome to the MongoDB Community. I am the product manager for Atlas SQL. Based on your question, I feel like you have read and seen the solution to regenerate the SQL Schema. The sqlGenerateSchema command must be run in our Mongo SH (shell program). And this must be run against the admin db.
Let me know if you get stuck.
Best,
Alexi
1 Like
Hello,
Thank you for your quick response. I have run the command in the shell in admin mode, but it is not being recognized. I am using mongosh 2.11 on Windows with the free tier of MongoDB.
Please let me know if you have any suggestions and thank you again for your help.
Best,
Jeremy
Hi Alexi, I’m getting the same error as Jeremy.
Any suggestions?
Best,
Grhamm
@Jeremy_Morales and @Grhamm_Pabst - Greetings to both of you.
Graham - based on Jeremy’s screen shot, I can tell he is not connected to the correct end point. He is connecting to the cluster and the sql end point would first connect through the federated db. To get the correct end point, follow these steps:
- From Atlas, select the Data Federation menu from the left navigation
- Find your SQL Federated DB Instance (if you enabled SQL from the cluster using the Quickstart method, your Quick Start Federated DB will be towards the bottom of the page.
- From the Federated DB Instance card, select the “Connect” button
- Select the option for Shell
- Use this Connection string in Mongo Sh
**you’ll notice that the connection string for data federation (which is the query engine used by Atlas SQL) has a different format that those direct to the cluster. The data federation string contains a “…query.mongodb.net/” at the end.
Thank you, I managed to run the command but I’m getting an Unauthorized error, I’m using the user with all permissions what could it be?
Hi @Grhamm_Pabst I think it might be helpful to hop on a quick screen share call or if you would like to start with sharing with me screen shots of the process and then the error. I might be able to figure out what is going on with those. Here is my email address: alexi.antonino@mongodb.com
Best,
Alexi
Greetings @Arthur_Borges - And welcome to the community.
So glad you reached out. This is being caused by not enough permissions for your user id (this is a guess based on the error message). There are some custom permissions that give access to manage/generate the SQL Schema. You can even narrow this down by Data Federation Instance as I have done. Within custom roles->Global Actions and Roles->Actions->Atlas Data Federation Actions
There you will find sqlGetSchema and sqlSetSchema. I also selected the other permissions under Data Federation as well, but I am quite sure you can just select the sql ones for the Schema Generation/Update. Also, just wanted you to know that we have the sql schema management coming to the Atlas UI in April. That will make this function much easier. Let me know if you have any further questions or if you get stuck more/again.
Hello Alexi!
Everything worked extremely well! Thank you for your great support!
Waiting for the UI update, since things will become way easier 
Have an awesome day!
I am happy to hear that worked out for you @Arthur_Borges. I will work with our docs team to see about a troubleshooting section as I think this answer could help others. Best to you!
1 Like
Hello Alexi!
Today I had this problem:
We migrated from M0 to M2 on Saturday and today I created a data federation using quickstart for BI. Did the same process of running the command to generate the SQL Schema.
When I run the dev-db I got the mentioned error.
Despite that, I can see both federated instances in UI. What’s the error about then?
