October 24, 2021
This is my first post so apologies for any errors.
I'm working with an SQL database and looking at using either Excel or Power BI to create some dashboards.
My question revolves around extracting the data from SQL. I believe I have two options and I'm looking for pros and cons of each one.
- Create the connection to the SQL database and then add the tables from the SQL DB I require and using PowerPivot I can join those tables and add formulas etc.
- Create an SQL query selecting only the fields that I require from the various tables using SQL joins and saving the query as a SQL View. Create the connection to the SQL database and then select the SQL View.
I appreciate any feedback that anyone is willing to give.
July 16, 2010
Welcome to our forum!
First, you should always use Power Query to get the data from your SQL database and then load it to Power Pivot/Data Model/Power BI. Power Query is built into both Excel and Power BI. Power Query will enable you to filter and remove columns you don't need in your final report, reducing the size of your file and improving performance. You can also add calculated columns here before loading.
That said, it is recommended that you connect to a view, rather than direct to the database tables and so you'd expect the view to already only contain the data you need in your final report.
Hope that helps.