Forum Discussion
Optimizing data warehousing/ ETL
Hi everyone,
I'm looking for advice on how to solve my data workflow. The IT lords prefer that I do not have access to the MySQL database, and they only provide seperate excels that I've structured like a database (Users, Orders, etc.)
The excels download via an http link, but Power BI does not allow me to add it as a web link because the website/CRM requires multi-factor authentication. So, every day, I go in, download the excels, and replace them in our OneDrive. So inefficient. So painful.
Does anyone have any tips on how to work around this? Is there a way to automate the downloading of these files, or a suggestion I can go back to the IT lords with regarding a data safe way for me to obtain the data.
Please let me know, also willing to hire temporarily as consultant to guide this process.
Thank you,
Denisse
Hey ruesaint_denis ,
basically there are 2 issues, you are facing
- MFA
- No direct access to the underlying database
Until you can't use any authentication that does not require human intervention, you can't automate anything. For this reason I would ask the lorde if they can provide some kind of placement of the files, that does not use MFA. Then you might consider creating some kind of Power Automate that automatically moves the files to onedrive.
The other aspect is that you can access the database directly, this often happens to avoid overload on the database by large analytical queries. This can be a complex path, as you have to convince the lords that your requirement is important and that they will also benefit if they are more supportive.
Hopefully, this provides some ideas on how to tackle your challenge.
Regards,
Tom
3 Replies
Hey ruesaint_denis ,
basically there are 2 issues, you are facing
- MFA
- No direct access to the underlying database
Until you can't use any authentication that does not require human intervention, you can't automate anything. For this reason I would ask the lorde if they can provide some kind of placement of the files, that does not use MFA. Then you might consider creating some kind of Power Automate that automatically moves the files to onedrive.
The other aspect is that you can access the database directly, this often happens to avoid overload on the database by large analytical queries. This can be a complex path, as you have to convince the lords that your requirement is important and that they will also benefit if they are more supportive.
Hopefully, this provides some ideas on how to tackle your challenge.
Regards,
Tom
Hi TomMartens ,
Thank you for your help and reply. Would you have any "secruity safe" suggestions on where/how the IT dept can transfer the files in order for me to automate the workflow? In my previous company they were looking to do an FTP site, but I'm not too familiar with the requirements there.
Thank you,
Denisse
Hi, ruesaint_denis
As mentioned by TomMartens , unless MFA is turned off, you cannot achieve any automation(Include automatic file transfer）
You can vote below idea which has the similar request.If there are enough votes, the Product Team will evaluate and take good consideration about it.
Best Regards,
Community Support Team _ Eason
