Step 3: Type Connection Information
You now want to tell Power BI where to find your SQL Server. This is pretty easy!
Server name: Here goes the name or address of your SQL Server. If the SQL Server is installed on your local machine, you can write "localhost" or your machine name. If it is hosted on a remote server, you can enter the IP address or name of the SQL Server instead.
Example: localhost\SQLEXPRESS or 192.168.1.100.
Optional Database Name: If you know the name of the database you want to use, enter it here. For example, leave it blank, and Power BI will show you a list of databases available.
Authentication: Select from these two options: If you want to access SQL Server with your Windows login, ensure Windows authentication is picked. Otherwise, select SQL Server Authentication and enter a username and password.
When you finish, click OK.
Step 4: Select Your Data
Now that you are connected to SQL Server, Power BI will retrieve and show a list of databases and tables available. This is where you select the data that they wish to bring into Power BI.
Select tables: Tick or mark those tables or views that you wish to bring in.
Preview: You will also get to preview the data to ensure you have made the correct choice.
Click on Load to enable Power BI to bring in the selected data when you are satisfied with your choice.
Step 5: Clean and Shape the Data (Optional)
You may want to clean or make some changes to the data before using it. Power BI would reasonably allow transforming your data.
In Power Query Editor, you can:
Remove extraneous columns
Remove unneeded rows
Add or alter existing columns
When done, click on Close & Apply to load the cleansed data into Power BI.
Step 6: Begin Report Creation
Now that your data link is complete, it is time to create your reports! Power BI allows easy dragging and dropping of data in bar charts, pie charts, or tables.
Use the fields pane on the far right to select the data that you want to present.
Select the charting method best suited for what you intend to show.
Change, style and alter data visuals until it fits your desire.
Try as many charts and visuals for the report that would communicate the best story for your data.
Step 7: Set Up Automatic Refresh (Optional)
If there are frequent updates to your data in SQL Server, you have the option to configure Power BI for automatic refresh.
Click on File > Options and settings > Options.
In Data Load, set your reports to automatically refresh and update with a defined schedule.
Step 8: Save and Share Your Report
In the end, when you are satisfied with your report, save it via clicking on the File > Save.
You can upload a report to Power BI Service in order to share it for viewing or collaborating with others.
Click Publish in Power BI Desktop, select a workspace in Power BI Service and you are done!
Tips for Troubleshooting
If any problems arise, here are some suggestions to investigate:
Firewall Issues: Ensure there are no settings on the firewall blocking the connection between Power BI and SQL Server.
Login Troubles: Double check for login troubles and your logins.
Handling Big Data: If you are working with very large datasets, Power BI may take its time in loading the data. Try limiting the dataloads or resort to the direct query option.
WooHoo! You have now connected Power BI with SQL Server and it's time to brush up your skills in creating kickass reports with your data. Enjoy visualizing and finding insights from your data!
