I’m working on importing data from a collection to Power BI. The new named connector is working great, but when the preview shows up, the schema is based on some old test records left in the collection. Here’s what I’ve tried so far:
- Removed the old documents from the collection.
- Set the Schema Sample Size Documents per database to 0 per this post.
- Tried to generate a new schema in mongosh using the instructions here from @Alexi_Antonino, but I’m getting an error “MongoServerError: no such command: ‘sqlGenerateSchema’”
Hi @Joel_Zehring I think I received this error when I didn’t have enough permissions to run this command. I am very confident that once we get this schema regenerated, all will be well for you. But your second bullet point, that is something to do with our older BI Connector, and not for the new Custom Power BI Connector (Atlas SQL)- so they are not related. I wanted to let you know this to alleviate some confusion.
For Atlas SQL, you should have a federated database (either one you created or one that was created through the SQL Quickstart). You could either create a new virtual collection in your Data Federation configuration or you could delete and recreate your Quick Start Federated DB - this should also give you an updated SQL Schema, reflecting any new schema changes in your source collection.
email me if you’d like to do a screen share session and we can make sure everything is set.
alexi.antonino@mongodb.com
2 Likes
Hi @Alexi_Antonino having same issue with connector to powerBI. First I have the issue similar to SQL Atlas Interface Error Connect Power BI - #2 by Alexi_Antonino and then I try to create a schema but get error "No such command: ‘sqlGenerateSchema’. I am sure I have admin access.
when i run sqlGetSchema i get the default answer: { “ok” : 1, “metadata” : { }, “schema” : { } }
has anyone managed to generate the schema successfully? i also can’t run the sqlGenerateSchema command
Hello @Matheus_Brito welcome to the community. A few things to help.
First, when you run the sqlGeneratSchema, you must do so from the admin db. Here are some instructions that might help. You may need permissions to do this, let me know if you get stuck.
Also, when using Shell, I needed to change my results output to show more verbose response. If you enter this command in: config.set(‘inspectDepth’, Infinity)
It will be able to show the whole schema back.
Hope this helps.
Alexi
@Alexi_Antonino thanks for you reply! I’m going to try these commands but first I want to clarify some points:
I have a collection with almost 300k documents with polymorphic data and I’m not sure how big should be the sampleSize. To sample between ALL documents should I set it to 0, right?
To be sure I have a schema containing all the fields I need, instead of running sqlGenerateSchema, can I run sqlSetSchema with an exported schema from MongoDB Compass? All I have to do is paste the generated JSON’s schema inside the sqlSetSchema command?
@Alexi_Antonino I try to run sqlGenerateSchema from the admin db and I’m getting “not authorized” response. The user who run these commands need any specific role or permission? I can’t find this info at documentation.
