We are working on trying to have a datamart read/query a second datamart to get specific data. We are running into a couple of issues:
Firstly, when attempting to set up the connection I typically get this error, both if I set the Database name and if I don't, what is annoying is we were able to successful connec to this datamart on a different test datamart:
Steps to achieve:
- Copy datamart connection string:
- Go to second datamart that I wish to pull data into
- Click get data
- Select Azure SQL Database
- Insert connection string
- Set the timeout to high number, in this case we did 600 but will adjust. (This was due to an error we were getting during testing was that the connection would time out since we are getting around 400,000 rows of data
- no data gateway since its cloud to cloud
- Sign in using MFA
- hit next and recieve error below
So in an attempt to remedy this, i went into the datamart settings into the datasource credential settings and everytime I load into it the first time I get an error saying that the datasources could not be loaded, so I have to refresh page. When I do, i get tons of connections, most of which are not connected or what i am guessing is the blank ones are where I have attempted to connect to the datamart unsuccessfully. Either way, if i try to click edit credentials on any of them, nothing happens. I can go to lineage view and most of them, such as the datamart connection shows successful connection. Below is what I see
I understand datamarts are in preview, i just am wondering if this is a known issue and if there is a current work around.
Any input would be greatly appreciated!
2 Comments
v-cazheng-msftCommunity Support
Status changed:NewtoInvestigatingHi Wilsonjr,
I’ve tested the connection from one Datamarts to another Datamarts and there is no issue on connection. But in my scenario, I don’t configure MFA and the data amount is not so large.
Consider the usage of MFA and the large data amount, I would suggest you try these steps.
1 Make sure the connection info is correct
Server name is the Server connection String you copy from the upstream Datamarts Settings page
Database is the name of the upstream Datamarts
Authentication account is who creates the upstream Datamarts
2 Prolong the timeout as long as it is less than 2 hours which is the maximum
3 connect with the help of Gateway
Please register the data source to your gateway firstly and then recreate your new Datamarts with this configured gateway
Best Regards,
Community Support Team _ Caiyun
This simi worked by creating the Gateway, once you get the table added however, any existing table looses it's credentials and you are unable to edit the credentials. The work around was to instead re add a table from the same data source, which auto fills the credentials for the broken tables. You then can delete the extra tables you added. I appreciate the speedy response. MFA is dirty when it comes to DB's trying to talk to each other. Hoping I can work with my team on a better solution that doesnt utilize MFA but for now this will work thank you!
