SQL Atlas Interface Error Connect Power BI

[Adolfo_Adrian]

OS: Windows 11 Pro
PBI: Version: 2.117.984.0 64-bit (May 2023)
ODBC SQL Atlas Interface: V.0.1.4
Power Bi Connector: V.0.1.4

Currently I have configured the SQL atlas Interface beta connector, I read and connect data from a federated database, in all the collections of the database, the load from PBI works fine, however I have 2 collections that give me this error,

No se han podido guardar las modificaciones en el servidor. Error devuelto: 'OLE DB or ODBC error: [Expression.Error] Data source error occurred. SQLSTATE: HY000 NativeError: 96 Error message: ODBC: ERROR [HY000] [MongoDB][Core] Trying to execute query failed with error: Kind: Command failed: Error code 96 (OperationFailed): failed getting result set schema: translator error: project fields may not be empty, contain dots, or start with dollars, correlationID = 176a2801865a657aba0d5641, labels: {}. '.

I don’t know if it has something to do with the flexibility of the collection, for example, some documents have a string that comes with integers, and other documents this array does not exist… Does that influence the load? I was also unable to load this collection using the Mongo BI connector.

[Alexi_Antonino]

Hi @Adolfo_Adrian Thanks so much for your question and welcome to the community! Sometimes when a collection(s) can’t be loaded or previewed within Power Query it is because of a SQL schema issue. We do a very quick scan and sample 1 document to build the SQL schema so this could be problematic when you have polymorphic data throughout your source collection. You can fix this by going into MongoDB Shell and running a command to generate the SQL Schema, with a larger sample size. You can do this for each collection or for all collections within your federated database. Here is our docs page for this info as well: https://www.mongodb.com/docs/atlas/data-federation/query/sql/schema-management/

Here are some instructions to help guide you:

• Connect to your Federated DB within MongoDB Shell.
• Type: use admin
• Hit: enter
• Type: db.runCommand({sqlGenerateSchema: 1, sampleNamespaces: [“datalake.testDL”], sampleSize: 1000, setSchemas: true})
• Hit: enter

In the below command, datalake = the name of my virtual database and testDL is the viratul collection. You could also generate a SQL Schema for all collections within a Federated DB instance virtual database with a wildcard:

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

But this error has me thinking that your collection name(s) may contain a period or dollar sign? Can you see if that is the case? If that is the case, we are looking into a fix that may correct this soon, but for now a work around would be to use a manually created Federated DB (not a SQL Quickstart) and when creating the virtual database and collection names within the Federated DB instance make sure to avoid periods/dots and dollar signs.

Let me know if you need any assistance.

Best,
Alexi

[Adolfo_Adrian]

Thanks for the answer, I’ll try and I’ll be leaving the results this way. On the other hand, the collections that do load successfully, how can I segment their load, in the case of SQL Atlas Interface (via federated data bases) does not allow me to write a query to segment by date, for example, as is the case with mongo BI connector.

Could you help me with this while I verify the issue.

[Adolfo_Adrian]

I am happy to tell you that it was not necessary to carry out your instructions, although it is good to know.

The error certainly came from naming the virtual collections in the federated database with a point in between. When renaming it gave no error.

On the other hand, you could help me with the question of segmentation in the load.

[Alexi_Antonino]

Hello Adolfo - I am happy to hear that these 2 collections/tables loaded for you within Power BI now. Per your other question, when you say “segmentation” I believe you are asking how to filter by a piece of data to narrow down results? There are multiple ways to narrow the data. First, if you always wanted the data set to be narrowed, you could created a view within Data Federation using something like $match to filter. But if you want your data set to be larger and used for many different purposes once in Power BI there are 2 methods I am aware of. Within Power Query, you can use the column menu to filter or you can use SQL within the Power Query formula bar.

Let me know if this is the functionality you were looking for, or if I have misunderstood.

Best to you!

[Adolfo_Adrian]

It works wonderfully, I just couldn’t figure out how to apply various unwins combined with flatten

I tried this one, it worked perfect for me

SELECT *
FROM FLATTEN(
UNWIND(Flexiweb_Production.POSVersion
WITH PATH => downloadHistory))

However, there are several objectives that I want to treat something like this,

SELECT *
FROM FLATTEN(

UNWIND(Flexiweb_Production.POSVersion
WITH PATH => downloadHistory),

UNWIND(Flexiweb_Production.POSVersion
WITH PATH => Statistics)

)

but it gives me intanxis errors in the documentation there is no example with several unwind https://www.mongodb.com/docs/atlas/data-federation/query/sql/reference/#std-label-sql-limitations

[Adolfo_Adrian]

Another question, such as the sistansis to add a where on a date field, intnetn with cast, concvert, but they all give me an error. I want to do something like

SELECT *
FROM Flexiweb_Production.Batch
WHERE (date as date)=“2023-01-03 12:35:55.000”

[Alexi_Antonino]

Yay - I am so happy you got things working. Here is some SQL syntax/examples that may help you.

This example uses dot notation to unwind an array
Select CAST(_id as String),purchaseMethod, customer.age, items.quantity, items.price from UNWIND(Sales WITH PATH=> Sales.items) Where items.quantity = 2

Here is where I use a cast to extract the year and filter on year extraction:
= MongoDBAtlasODBC.Query(“mongodb://asql-rotpc.a.query.mongodb.net/Supplies?ssl=true&authSource=admin”,“Supplies”,“Select CAST(_id as String),purchaseMethod, customer.age, CAST(EXTRACT(YEAR FROM saleDate)as integer) as SalesDate, items.quantity, Cast(items.price as varchar(20)) as price from UNWIND(Sales WITH PATH=> Sales.items) where CAST(EXTRACT(YEAR FROM saleDate)as integer)>2015”)

If you would be so kind to send me your email address, I will provide you with a pdf that has all kinds of mongosql examples like these 2 above.

Here is my email: alexi.antonino@mongodb.com