What is the Better Power BI Data Source For Enterprise Reporting - Excel or SQL Server?

[hiphopanonymous]

A team in my organization is connecting multiple Excel workbooks to a single Power BI report that will be used for a global report. The Excel workbooks are stored on a Windows network drive. I prefer to connect my Power BI reports to a SQL Server Database that is hosted on a company server, which I believe is a more appropriate data source for this enterprise-level Power BI application.

Can anyone explain the advantages of using a SQL Server Database as a source for a Power BI Report instead of using multiple Excel workbooks that are stored in a Windows network drive (e.g. performance, data refresh speeds, security, etc.)? This will help me determine if I should help them transition from Excel to SQL Server or let them be with Excel.

[v-diye-msft · Accepted solution]

Hi @hiphopanonymous 

My personal opinion is to put this into SQL. Not only will it be faster, you will have the ability to backup and move the data around so much easier.

Also, if you upload Excel file data to SQL Server database, you can connect to SQL Server database using “DirectQuery” option from Power BI Desktop, then create reports in Desktop and publish them to Power BI Service. The “DirectQuery” feature lets you connect live to your data source, which should make the whole process faster as you don’t need to import data from SQL Server database to Power BI.

And after you publish the reports to Power BI Service,  Power BI automatically refreshes data in your dataset and reports hourly.

[Mariusz]

Hi @hiphopanonymous 

If you are working with existing excel files and you looking to create an ETL process just to move the data to SQL for Power BI use only, I would say it's not worth the hassle.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn