In this article we will see how connect Power BI to SQL Server.
Following are the steps to connect Power BI desktop to SQL Server.
> Open Power BI Desktop, Click on GET DATA then onย the Left side you will get the list of different different data source Just click on SQL Server database
Now a SQL Server Database dialog box opens with following options.
Server: Provide your SQL server instance name.
Database: Provide your database name, It is an optional. If you want to use a custom SQL query then it is mandatory.
Data Connectivity mode: Here you have two option to choose either Import mode or Direct Query mode.
Import mode: In import mode, selected tables and columns are imported into Power BI desktop.
DirectQuery mode: In DirectQuery mode, No data is imported or copied into Power BI desktop. All theย ย data remains in the data source.
Advance options: You can provide command time (in minutes), It is an optional.
SQL Statement: You can write SQL statement to extract the data, It is an optional. If you write a SQL statement, you must specify a database name.
Include Relationship column: You can include and exclude the Relationship columns.
This option is checked if a table has any relationships with other tables and includes expandable relationship columns in Power Query Editor. By default it is included.
Navigate using full hierarchy: You can enable or disable navigation with a full hierarchy. By default it is ย disable.
If this option is enabled then you can navigate from the server down to databases, then schemas, and finally objects within schemas.
If this option is disabled then you navigate from the server to the databases, and then all objects from all schemas.
Enable SQL Server Failover support: You can enable or disable SQL Server Failover support.
If you enable this option, you can benefit from local high availability through redundancy at the server-instance level by leveraging Windows Server Failover Clustering. By default it is disabled.
After that , Click on OK button.
Once you click on OK Button, a credentials window opens for authentication: Here you can select either Windows or Database.
If you choose Windows, you can either select to use the current user credentials or specify alternate credentials then click on Connect button.
You might get a prompt on Encryption Support, Just click OK to connect without encryption.
After that , You get a preview of your data that is extracted through your SQL statement.
If you want to modify any column name, or change data type or exclude any column then you can click on transform data button, it will redirect you to the Power Query Editor window.
If you click on load button then power bi create a table in power bi desktop as you can see below.
If you click on Cancel button , then the dialog box will be closed with out any action.
Now you can drag the table columns into table visual to see the data.
