SQL Server & Power BI Instead of Excel

I love data analysis and for the life of me, I can't understand why people use Excel as a database. I also wonder why people waste so much time using Excel for Data Visualizations. It's like "When the only tool you have is a hammer, everything looks like a nail" As an experiment, I used my SQL server to generate a listing of sales for the years 2010 through 2014 by country from the SQL Server AdventureWorks database. The raw data yields 60,398 rows. These are the types of things I like to do when I can't sleep at night.

You will soon see how you can produce the same results using SQL Server and Power BI in minutes, instead of hours using Excel. If you were doing this only in Excel, you would need to import the raw data into an Excel spreadsheet in order to create a Pivot table. This also increases the amount of mistakes that can be made .

We all know what happens when you cut and paste data into excel. The results are very unpredictable and depending how large the raw data file is, load time can really be an issue. Also, by the time you go through all the trouble in Excel, the data has probably changed already.

What we will do now is use Power BI and connect to a SQL Server data. Use an SQL query using the Pivot operator and load the data into Power BI.

Like magic, the SQL Query generates the same Pivot table in one step instead of formatting all the raw data in Excel and creating a pivot table.

Also in the same pass Power BI will create the same exact visualization that would require another step in Excel by creating a Pivot Chart.

Where you reap the biggest benefits using SQL Server and BI is that the data is real time so anytime you open the BI file, all you need to do is click refresh and your visualization automatically updates. If you are using Excel, every time you need to update your results, you will need to import new raw data in order to update your dashboard.

Granted, there is a learning curve when creating dashboards with Power BI, but it is well worth it in my opinion.

I hope this was a useful example and offers you another option when creating your dashboards.