Migrating PowerBI Connector from MondoBD ODBC to MongoDB Atlas SQL

MongoDB Atlas
1

Hi all,
I have just started with the tasks prior to migrating my PowerBI dataflow that connects to my Mongo Atlas database through the MongoDB ODBC 1.4.5, with the new Atlas SQL Interface.
I have created the federated Mongo database using the Quick Start. I have created the new Atlas SQL ODBC connection in PowerBI, I have installed the Atlas SQL ODBC in the gateway and I have configured it pointing to the federated database, I have imported my PowerBI dataflow into another new workspace where I have changed all the connections of the flow tables, from
Odbc.DataSource(“dsn=MongoDB ODBC”, [HierarchicalNavigation = true])
to
Odbc.DataSource(“dsn=MongoDB Atlas SQL”, [HierarchicalNavigation = true]).
But the results are different. At this point (please, check the snapshots (1) pdf) begin the differences between the two connectors: while with the MongoDB ODBC I receive 4 databases
(INFORMATION_SCHEMA, mysql, bd1, bd2),
with the Atlas SQL ODBC I only receive 2 databases
(bd1,bd2).
But when I go to the next step(please, check the snapshots (2) pdf) (Source{[Name = “bd1”, Kind = “Database”]}[Data]) is when I find more differences, since with the Mongo ODBC I receive 270 rows, and with the Atlas SQL ODBC I receive 59.
I understand that it is because with the Mongo ODBC all collections appear included the “subcollections” that I have created (e.g. clients, clients_additionalQuestions, clients_aml_data_discarded_matches…), but with the Atlas SQL ODBC only grouped collections appear (e.g. clients).
This generates an error (please, check the snapshots (3) pdf) in the queries that I have configured in my dataflow because at some point, a column refers to a “subcollection” that I cannot expand because it does not appear in the prior step - the (2) step- to the database.

Please, see snapshots attached pdf of my Power Bi Dataflow

Atlas SQL troubleshooting indicates that the federated database schema must be updated to solve the error, but when I try to do so, I get this error:

db.runCommand({ sqlGenerateSchema: 1, sampleNamespaces: [“db1”], sampleSize: 1000, setSchemas: true })
MongoServerError: sqlGenerateSchema must be run against the admin database when sampleNamespaces are specified, correlationID = 1823c253e9cf4e2617c56895

There are only 2 databases in the federated instance, but neither of them are working
(same error) . I tried using a wildcard [“db1.*”] with same results.

Could you help me?

Thanks in advance
MongoSnapshots.pdf (146.6 KB)

2

In adding, I do a show databases, and show this:
AtlasDataFederation test> show databases
admin 0 B
config 0 B
local 0 B
nextportfolio 0 B
rosetta 0 B

3

Hi @Mario_Gonzalez Thanks for your inquiry. I believe there are 2 separate topics here: 1 there is an error with a particular column “client_additionalQuestions” and this could be because it isn’t in the sql schema, but from the screenshots it’s hard to tell if this is what is going on. Are you getting this error with a “Select * from table”? This part is unclear to me.

And 2, you are comparing the differences between the BI Connector relational schema representation and Atlas SQL, which doesn’t provide the same rendering of the data. This is by design and users can use SQL to transform (flatten, unwind, cast) the data.

Here is an example of SQL I pass in through the connector or you can put it in Mcode in Power Query:
Select Cast(_id as string) as _id,

couponUsed, customer_age, customer_gender, customer_satisfaction,

items_name, items_price as price, items_quantity,

(items_quantity * items_price) as totalPrice,

storeLocation, saleDate, purchaseMethod ,

cast(saleDate as Timestamp)as testDateTimestamp,

SUBSTRING (Cast(DATETRUNC(DAY,saleDate)as string),0,10) as DateOnly

from Flatten(Unwind(Supplies.Sales with Path=>Sales.items))

where items_quantity = 2

limit 100

If you can email me, we can figure out the error and I can guide you in a bit more targeted way. Also, I can point out some references on Atlas SQL that helps show the differences and how to adjust usage. Alexi.antonino@mongodb.com

4

Hi Alexi, thanks for your response. I’m going to send you an email right now.

all the best