Forum Discussion
Do I need a database?? Excel datasource...
Hi all,
I get emailed daily web scrapes of our items with competitor pricing as an excel file. Each file is about 200K rows and I save them to a folder on a shared drive. I combine them using the power query from folder function for my PBI report.
I'm currently at 7 million rows and it's working fine but I'm worried that performance will suffer as the table keeps growing.
Also, I have a feeling that it's better to store the price history in some sort of database rather than in separate excel files.
- Should I make a database?
- What type? (Easy and free preferred)
- Bonus: Is there a way to get each new scrape to automatically add daily from my email?
If it matters I have PBI PPU, basic SQL skills, and access to SSMS but no idea if I'm allowed to add a new database.
Thanks in advance!
If it ain't broke don't fix it.
There are many good Databases that have a free tier like Google BigQuery or SQL Server. The question is creating the pipeline to get your files into a table in the DB. If you are the only one that needs access to the folder, you may not need a database. The Power Bi model could be set to refresh at night. So even if it grows too large it won't comprimise proformance and you could always delete old files more easily from a folder than a database. See attached article.
4 Replies
Hi b2wise ,
I can answer #3 above. In case you use outlook as your email client, you can add a folder as a source for your report and fetch attachments directly from your email. Please refer to this blog below :
https://radacad.com/import-email-attachments-directly-into-a-power-bi-report-using-power-query
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊Thanks rohit_singh , I use gmail but may have an option to use outlook as well and will keep that in mind.
I'm still looking for answer for questions 1 and 2.
If it ain't broke don't fix it.
There are many good Databases that have a free tier like Google BigQuery or SQL Server. The question is creating the pipeline to get your files into a table in the DB. If you are the only one that needs access to the folder, you may not need a database. The Power Bi model could be set to refresh at night. So even if it grows too large it won't comprimise proformance and you could always delete old files more easily from a folder than a database. See attached article.
