Not all collumns showing in Power BI (Using Mongo DB Atlas beta connector)

Alexi_Antonino · 2023-09-24T23:03:45.233Z

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:

Screenshot 2023-08-25 at 9.15.00 AM1285×722 152 KB

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})

Srinivas_Jayaram · 2023-10-19T11:47:18.065Z

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.

Screenshot 2023-10-19 at 5.51.00 PM2248×1314 331 KB

Below is the list of columns that PowerBI has pulled ( around 15 columns )…rest is missing

Capture451×645 39.6 KB

Alexi_Antonino · 2023-10-19T13:40:44.748Z

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:

Screenshot 2023-08-25 at 9.26.18 AM761×744 16.1 KB

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:

Screenshot 2023-10-19 at 9.39.48 AM1276×685 152 KB

Let me know if this is helpful.

Srinivas_Jayaram · 2023-10-20T09:19:14.755Z

Alexi_Antonino:

db.runCommand({sqlGenerateSchema: 1, sampleNamespaces: [“datalake.*”], sampleSize: 1000, setSchemas: true})

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

Alexi_Antonino · 2023-10-20T11:59:56.204Z

I’m so glad this works for you! Best to you and your Power BI adventures:)

Jeremy_Morales · 2024-01-23T16:06:36.548Z

Hello,
I am brand-new to Atlas projects. What ‘Shell’ are you referring to for running the sqlGenerateSchema command?
Thank you!

Alexi_Antonino · 2024-01-23T16:22:22.411Z

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.

Screenshot 2023-03-29 at 12.58.57 PM1150×644 94.7 KB

Screenshot 2023-03-29 at 12.59.06 PM1157×649 131 KB

Let me know if you get stuck.
Best,
Alexi

Jeremy_Morales · 2024-01-23T17:20:37.753Z

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.

image1455×122 7.84 KB

Please let me know if you have any suggestions and thank you again for your help.

Best,
Jeremy

Grhamm_Pabst · 2024-02-21T14:20:45.363Z

Hi Alexi, I’m getting the same error as Jeremy.
Any suggestions?

Best,
Grhamm

Alexi_Antonino · 2024-02-21T15:00:47.975Z

@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.

Screenshot 2024-02-21 at 9.57.20 AM1068×957 73.6 KB

Screenshot 2024-02-21 at 9.57.41 AM941×946 67.2 KB

Screenshot 2024-02-21 at 9.57.52 AM826×838 63.9 KB

Grhamm_Pabst · 2024-02-26T16:55:25.782Z

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?

Alexi_Antonino · 2024-02-27T13:44:41.576Z

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

Arthur_Borges · 2024-03-04T17:51:43.075Z

Hello! I’m having the same issue of not authorized:

image1824×432 70.2 KB

Alexi_Antonino · 2024-03-04T18:48:34.237Z

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.

Screenshot 2024-02-28 at 7.29.26 AM796×619 47.9 KB

Screenshot 2024-02-28 at 7.29.49 AM802×619 49.5 KB

Screenshot 2024-02-28 at 7.37.41 AM949×907 72.3 KB

Arthur_Borges · 2024-03-04T19:35:48.163Z

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!

Alexi_Antonino · 2024-03-04T19:49:28.838Z

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!

Arthur_Borges · 2024-03-11T14:14:36.700Z

Hello Alexi!

Today I had this problem:

image1880×208 38 KB

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?

image2532×1294 205 KB

Alexi_Antonino · 2024-05-31T14:05:36.239Z

@Arthur_Borges - sorry just seeing this now. Please check out the new Sql Schema Mgmt UI in Atlas. You will see it on the Data Federation menu. If you don’t have a schema in place, you can easily generate one, if you do have one, you can review it for that column. Also, you can create a schedule to run Schema Generation to pickup new fields.

Donis_Rikardo · 2025-04-25T00:03:52.680Z

Please update your original answer. Now we can do same by UI

Alexi_Antonino · 2025-04-25T14:38:56.155Z

As mentioned, Now the Atlas SQL Interface SQL Schema Management is available within the Atlas UI. From the Data Federation menu, locate your federated database instance (whether is it a SQL Quickstart or a manually created instance), then select “manage sql schemas”.

Screenshot 2025-04-25 at 10.30.06 AM1480×457 33.3 KB

Screenshot 2025-04-25 at 10.29.51 AM1981×887 91.4 KB