Optimizing data warehousing/ ETL

[ruesaint_denis]

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

[TomMartens · Accepted solution]

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

[ruesaint_denis]

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

[v-easonf-msft]

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. 

MFA Authentication

Best Regards,
Community Support Team _ Eason