Wednesday, June 15, 2016

Connecting to SQL Server 2016 from Power BI

 Most of the SQL Servers have an integrated Reporting Services, a stable work horse for not only turning out reports but also handling all aspects of enterprise reporting from getting data to distributing, monitoring, etc. It has a reporting services Report Server and an integrated Report Manager front end.

I have covered the Reporting Services in two of my more popular books, one for SQL Server 2008 and another for Reporting Services 2012. The latter covers both the Native Mode of the server but also the SharePoint Integrated mode.


PowerBI on the other hand is supposed to provide the ad-hoc authoring of reports even though the author of the report has the knowledge of neither the complete innards of the report data nor the programming capabilities of a full-blown report developer.

In this post, I will step through the process of connecting to SQL Server 2016 from one of the recent versions of Power BI. I have a number of posts for the interested reader related to Power BI at the conclusion of this post.

The details of this post relates to a Windows 10 laptop which has a local named instance of SQL Server 2016 (Developer edition) and Power BI.

Step 1:
Launch POWER BI from its desktop shortcut.


Step 2:
Make sure your SQL Server 2016 is up and running. Get to the main screen where you will be designing by dismissing the modal splash screen.


 Click on GetData. You should get a drop-down menu with various data sources listed. When you hover over SQL Server you can see the pop-up as shown.

 Click on SQL Server to display the following:

You need to enter the details. The present version is not showing any options to browse for a server. You need to enter (You can get the information from the SSMS 2016) the details, the one shown are for the present named Instance of SQL Server 2016.


Click OK and you will be displaying the following screen:


SQL Server 2016 was installed for Windows Authentication access and therefore you can accept the default and click Connect.

You get this message related to encryption. SQL Server 2016 supports encryption and I am not sure why this message generated.


Click OK and you are connected to the AdventureWorks2014 Database as shown.


You may want to read up on the following related stuff to get a head start with Power BI:

PowerBI and the Cloud

PowerBI on Gartner's Magic Quadrant

Report from SQL Anywhere 16 using Microsoft Power BI

Report from an EXCEL application

Rapid reporting with Microsoft Power BI

Microsoft Intelligent System Services

Power BI Unchained

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...